例如: SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE ‘SMITH%';
SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE ‘SMITH%';
你可以用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%';
SELECT COUNT(*),SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE ‘SMITH%';
你可以用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%';
根据不同的判断条件汇,总值不同的列体现
菜鸟学sql必须经历的一个门槛专题
搜一搜
SELECT DEPT_NO, COUNT(*),SUM(SAL)
FROM EMP
group by DEPT_NO