数据库开发-SQL-王坤朋.ppt

1、为什么不能这么执行,= *为什么不能这么执行* = SELECT 3, 1.1 货物贸易, A.S_BANKORGCODE, SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT1,-收入部分本期发生额 SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE

2、0 END) AS F_AMT2,-收入部分1当月累计额 SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT3,-收入部分去年1同月累计额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT4,-支出部分本期发生额 SUM

3、(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHISYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT5,-支出部分1-当月累计额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END) AS F_AMT6 -支出部分去年1同月累计额 FROM SESSION.TB_INCOMEPAYOUT_GOODS

4、 A WHERE F_AMT1 0 OR F_AMT2 0 OR F_AMT3 0 OR F_AMT4 0 OR F_AMT5 0 OR F_AMT6 0 GROUP BY A.S_BANKORGCODE =,SQL执行顺序,= 一条普通的SQL = SELECT 3, 1.1 货物贸易, A.S_BANKORGCODE, SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END),-收入部分本期发生额 SUM(CASE WHEN

5、A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END),-收入部分1当月累计额 SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END),-收入部分去年1同月累计额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTA

6、RT AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END),-支出部分本期发生额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHISYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END),-支出部分1-当月累计额 SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END) -支出部分去年1

7、同月累计额 FROM SESSION.TB_INCOMEPAYOUT_GOODS A GROUP BY A.S_BANKORGCODE HAVING ( SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) 0 OR SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DTHISYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0

8、END) 0 OR SUM(CASE WHEN A.C_TYPE = I AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END) 0 OR SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHISMONTHSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) 0 OR SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DTHI

9、SYEARSTART AND DTHISMONTHEND THEN A.F_AMT ELSE 0 END) 0 OR SUM(CASE WHEN A.C_TYPE = P AND A.D_PAYEEDATE BETWEEN DLASTYEARSTART AND DLASTMONTHEND THEN A.F_AMT ELSE 0 END) 0 ) = 执行顺序 1：FROM 字句 组装来自不同表或试图的数据 2：WHERE 对FROM 返回的数据进行过滤 注意（WHERE 后边 表达式的字段都是FROM 后表中的字段，如果出现伪列谓词动作会报错） 3：GROUP BY 对WHERE 过滤后的数据

10、进行分组 4：聚集函数如SUM、COUNT 等 如果聚集函数中有表达式 先计算表达式在执行聚集函数 5：HAVING 对分组后的经聚合函数计算后的数据进行过滤 6：计算所有的表达式如 CASE WHEN 7：使用ORDER BY 对结果集进行排序 8：SELECT 集合输出,经常用到的表连接,JOIN 内连接等价于等值连接 SELECT * FROM EMP A JOIN DEPT B ON A.DEPTNO = B.DEPTNO (连接键主外键，如果不是则必须建立索引) 等价于 SELECT * FROM EMP A,DEPT B WHERE A.DEPTNO = B.DEPTNO LEFT

11、 OUTER JOIN 左外连接（存在某种业务关联）,等值连接是最初级的连接也是最不负责任的连接,比如 我想知道员工表中，每个部门工资总和是多少。 SELECT A.DEPTNO,B.DNAME,SUM(A.SAL) FROM EMP A LEFT OUTER JOIN DEPT B ON A.DEPTNO = B.DEPTNO GROUP BY A.DEPTNO,B.DNAME 这里 DEPT（部门表起到翻译作用）,SQL是面向集合的,UNION 排序去重 并集 前提条件列的数量和数据类型必须兼容 UNION ALL并集 UNION 等价于 SELECT DISTINCT FROM ( UN

12、ION ALL ) DISTINCT 进行隐式排序,子查询,SELECT * FROM ( SELECT * FROM EMP WHERE EMPNO = 7802 ) 标量子查询 放在SELECT 列中的子查询 SELECT E.ENAME,D.LOC, ( SELECT B.COMM FROM BONUS B WHERE B.ENAME = E.ENAME ) AS RECEIVED FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO 这是最简单的自查询 介绍个复杂的子查询,临时表,三种 1、（）内的有名，匿名 做子查询时用到 2、WITH TMP，调试

13、汇总时用到 3、存储过程定义的临时表,函数-单行,单行函数 常用到的 SUBSTR,截取字符串 | 字符连接 COALESCE（A,B） 当A出现null时用B来代替 CASE WHEN 表达式 IS NULL IS NOT NULL,函数-多行函数,AVG SUM COUNT如果count函数内的字段出现重复值怎么办？ MAX,MIN,谓词,出现在WHERE后面的关键字 = = = 1000,常见问题,在一个表中查找与其他表不匹配的记录 想查询中增加关联而不影响其他关联 消除笛卡尔积（n-1原则） 如何处理中间结果集全零数据 如何处理聚集于联接混合操作 1：在调用聚集函数时使用关键字 2：使

14、用子查询，先消除重复值。 删除重复记录 DELETE FROM DUPES WHERE ID NOT IN (SELECT MIN(ID) FROM DUPES GROUP BY NAME),索引,索引：是表的一个或个列的键值的有序列表，存在索引文件中。 采取B+树形式存储 CREATE INDEX IDX_TEST1 ON TABPE(COLUMN1); CREATE INDEX IDX_TEST1 ON TABLE(COLUMN1，COLUMN2，COLUMN3) 列出表的索引 SELECT * FROM EMP WHERE DEPTNO = 20 是否走索引 DB2 SELECT * F

15、ROM SYSCAT.INDEXES A JOIN SYSCAT.INDEXCOLUSE B ON A.INDNAME = B.INDNAME WHERE A.TABNAME = TB_INCOME_ITEM AND A.TABSCHEMA = DB2ADMIN ORACLE SELECT * FROM SYS.ALL_IND_COLUMNS WHERE TABLE_NAME = EMP AND TABLE_OWNER = SCOTT,经典案例,SELECT C.* FROM ( SELECT A.I_SEQNO,A.S_LEVYNO,A.S_CUSTORGCODE,A.S_CUSTORGNA

16、ME, A.D_CUSTOMSDATE,A.C_TRADECLASS,A.C_BALANCETYPE,A.F_ENTRTYAMT, A.C_LISTOPERTYPE ,B.S_BANKORGCODE AS BANKORGCODE,B.S_BANKORGNAME AS BANKORGNAME, B.S_PAYEENAME AS SPAYEENAME,B.C_PAYEEATTR AS CPAYEEATTR, B.S_PAYEEORGCODE AS SPAYEEORGCODE,B.S_SWIFTBIC AS SSWIFTBIC, B.D_PAYEEDATE AS DPAYEEDATE FROM TB_INCOME_LIST A LEFT OUTER JOIN TB_INCOME_ITEM B ON A.S_LEVYNO = B.S_LEVYNO WHERE A.C_BALANCETYPE 3 AND A.S_CUSTORGCODE = 788832354 AND A.C_TRADECLASS = 2 )C LEFT OUTER JOIN TR_DAY_RECQUAN_DETAIL D ON D.S_ORGCODE = C.S_CUSTORGCODE AND D.D_CUSTOMER