课程实践（一）续

案例五

点击(此处)折叠或打开

1. --5.1 bad
   
2. select count(*) from myemp where salary < 2000;
   
3. 
   
4.   COUNT(*)
   
5. ----------
   
6.          0
   
7. 
   
8. Elapsed: 00:00:58.42
   
9. 
   
10. select count(*) from myemp where salary between 2000 and 4000;
    
11. 
    
12.   COUNT(*)
    
13. ----------
    
14.    5636096
    
15. 
    
16. Elapsed: 00:00:40.69
    
17. 
    
18. 
    
19. select count(*) from myemp where salary > 4000;
    
20. 
    
21.   COUNT(*)
    
22. ----------
    
23.    8388608
    
24. 
    
25. Elapsed: 00:00:41.40
    
26. 
    
27. --5.2 good
    
28. select count(case when salary < 2000 then 1 else null end) as count1,
    
29.        count(case when salary between 2000 and 4000 then 1 else null end) as count2,
    
30.        count(case when salary > 4000 then 1 else null end) as count3
    
31. from myemp;
    
32. 
    
33.     COUNT1 COUNT2 COUNT3
    
34. ---------- ---------- ----------
    
35.          0 5636096 8388608
    
36. 
    
37. Elapsed: 00:00:42.14

结论：可以使用CASE子句在一个查询中返回多个结果，减少对表的扫描和访问次数。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1072430/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1072430/