案例五
结论:可以使用CASE子句在一个查询中返回多个结果,减少对表的扫描和访问次数。
点击(此处)折叠或打开
- --5.1 bad
- select count(*) from myemp where salary < 2000;
-
- COUNT(*)
- ----------
- 0
-
- Elapsed: 00:00:58.42
-
- select count(*) from myemp where salary between 2000 and 4000;
-
- COUNT(*)
- ----------
- 5636096
-
- Elapsed: 00:00:40.69
-
-
- select count(*) from myemp where salary > 4000;
-
- COUNT(*)
- ----------
- 8388608
-
- Elapsed: 00:00:41.40
-
- --5.2 good
- select count(case when salary < 2000 then 1 else null end) as count1,
- count(case when salary between 2000 and 4000 then 1 else null end) as count2,
- count(case when salary > 4000 then 1 else null end) as count3
- from myemp;
-
- COUNT1 COUNT2 COUNT3
- ---------- ---------- ----------
- 0 5636096 8388608
-
- Elapsed: 00:00:42.14
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1072430/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1072430/