之前同事发过一个语句，实现的功能比较简单，类似group by的分组计数功能，因为where条件有like，又无法用group by来实现。 SELECT a.N0,b.N1,c.N2,d.N3,e.N4,f.N5,g.N6,h.N7,i.N8,j.N9 from (select count(*) N0 from tbl_loginfo_20141110 where keyrecord like '0%' or keyrecord like 'GJ_0%') a, (select count(*) N1 from tbl_loginfo_20141110 where keyrecord like '1%' or keyrecord like 'GJ_1%') b, (select count(*) N2 from tbl_loginfo_20141110 where keyrecord like '2%' or keyrecord like 'GJ_2%') c, (select count(*) N3 from tbl_loginfo_20141110 where keyrecord like '3%' or keyrecord like 'GJ_3%') d, (select count(*) N4 from tbl_loginfo_20141110 where keyrecord like '4%' or keyrecord like 'GJ_4%') e, (select count(*) N5 from tbl_loginfo_20141110 where keyrecord like '5%' or keyrecord like 'GJ_5%') f, (select count(*) N6 from tbl_loginfo_20141110 where keyrecord like '6%' or keyrecord like 'GJ_6%') g, (select count(*) N7 from tbl_loginfo_20141110 where keyrecord like '7%' or keyrecord like 'GJ_7%') h, (select count(*) N8 from tbl_loginfo_20141110 where keyrecord like '8%' or keyrecord like 'GJ_8%') i, (select count(*) N9 from tbl_loginfo_20141110 where keyrecord like '9%' or keyrecord like 'GJ_9%') j; 为了了解语句的性能，我做了如下类似的测试： select * from v$version; --Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production drop table a; create table a as select * from dba_objects where rownum<=50000; begin for x in 1..6 loop insert into a select * from a; end loop; commit; end; select count(*) from a; --3200000 select bytes/1024/1024 from user_segments where segment_name='A'; --357M alter system flush shared_pool; alter system flush buffer_cache; SELECT a.N0,b.N1,c.N2,d.N3,e.N4,f.N5,g.N6,h.N7,i.N8,j.N9 from (select count(*) N0 from a where object_name like 'A%' or object_name like 'V%') a, (select count(*) N1 from a where object_name like 'B%' or object_name like 'V%') b, (select count(*) N2 from a where object_name like 'C%' or object_name like 'V%') c, (select count(*) N3 from a where object_name like 'D%' or object_name like 'V%') d, (select count(*) N4 from a where object_name like 'E%' or object_name like 'V%') e, (select count(*) N5 from a where object_name like 'F%' or object_name like 'V%') f, (select count(*) N6 from a where object_name like 'G%' or object_name like 'V%') g, (select count(*) N7 from a where object_name like 'H%' or object_name like 'V%') h, (select count(*) N8 from a where object_name like 'I%' or object_name like 'V%') i, (select count(*) N9 from a where object_name like 'J%' or object_name like 'V%') j; --58s alter system flush shared_pool; alter system flush buffer_cache; --改写后 select sum(case when object_name like 'A%' or object_name like 'V%' then 1 else 0 end) N0, sum(case when object_name like 'B%' or object_name like 'V%' then 1 else 0 end) N1, sum(case when object_name like 'C%' or object_name like 'V%' then 1 else 0 end) N2, sum(case when object_name like 'D%' or object_name like 'V%' then 1 else 0 end) N3, sum(case when object_name like 'E%' or object_name like 'V%' then 1 else 0 end) N4, sum(case when object_name like 'F%' or object_name like 'V%' then 1 else 0 end) N5, sum(case when object_name like 'G%' or object_name like 'V%' then 1 else 0 end) N6, sum(case when object_name like 'H%' or object_name like 'V%' then 1 else 0 end) N7, sum(case when object_name like 'I%' or object_name like 'V%' then 1 else 0 end) N8, sum(case when object_name like 'J%' or object_name like 'V%' then 1 else 0 end) N9 from a; --19s --对比执行计划： --前者执行计划： SQL>explain plan for 2 SELECT a.N0,b.N1,c.N2,d.N3,e.N4,f.N5,g.N6,h.N7,i.N8,j.N9 from 3 (select count(*) N0 from a where object_name like 'A%' or object_name like 'V%') a, 4 (select count(*) N1 from a where object_name like 'B%' or object_name like 'V%') b, 5 (select count(*) N2 from a where object_name like 'C%' or object_name like 'V%') c, 6 (select count(*) N3 from a where object_name like 'D%' or object_name like 'V%') d, 7 (select count(*) N4 from a where object_name like 'E%' or object_name like 'V%') e, 8 (select count(*) N5 from a where object_name like 'F%' or object_name like 'V%') f, 9 (select count(*) N6 from a where object_name like 'G%' or object_name like 'V%') g, 10 (select count(*) N7 from a where object_name like 'H%' or object_name like 'V%') h, 11 (select count(*) N8 from a where object_name like 'I%' or object_name like 'V%') i, 12 (select count(*) N9 from a where object_name like 'J%' or object_name like 'V%') j; Explained. Elapsed: 00:00:00.15 SQL> @getplan 'general,outline,starts' Enter value for plan type: PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------- Plan hash value: 2527411742 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 130 | 123K (1)| 00:24:46 | | 1 | NESTED LOOPS | | 1 | 130 | 123K (1)| 00:24:46 | | 2 | NESTED LOOPS | | 1 | 117 | 111K (1)| 00:22:17 | | 3 | NESTED LOOPS | | 1 | 104 | 99032 (1)| 00:19:49 | | 4 | NESTED LOOPS | | 1 | 91 | 86653 (1)| 00:17:20 | | 5 | NESTED LOOPS | | 1 | 78 | 74274 (1)| 00:14:52 | | 6 | NESTED LOOPS | | 1 | 65 | 61895 (1)| 00:12:23 | | 7 | NESTED LOOPS | | 1 | 52 | 49516 (1)| 00:09:55 | | 8 | NESTED LOOPS | | 1 | 39 | 37137 (1)| 00:07:26 | | 9 | NESTED LOOPS | | 1 | 26 | 24758 (1)| 00:04:58 | | 10 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 | | 11 | SORT AGGREGATE | | 1 | 66 | | | |* 12 | TABLE ACCESS FULL| A | 91587 | 5903K| 12379 (1)| 00:02:29 | | 13 | VIEW | | 1 | 13 | 12379 (1)| 00:02:29 | | 14 | SORT A