关于大表化成小表,zxyufan(宇凡) 和 liujianjun(流星尔) 可能理解错我的意思了。我说的意思是用DB2的表表达式来化,同时又可以在一个sql语句里面完成,这样可以直接用在一些控件里面,比如PB的DATAWINDOW。DB2的这个功能也是我比较喜欢的。
DB2的表表达式其实就是动态的视图,其中的公共表达式的语法大概是:
with 表表达式定义,表表达式定义
主查询语句
下面是个例子:
(1)
WITH
PAYLEVEL AS
(SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL,
SALARY+BONUS+COMM AS TOTAL_PAY
FROM EMPLOYEE
WHERE EDLEVEL > 16),
(2)
PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS
(SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)
FROM PAYLEVEL
GROUP BY EDLEVEL, HIREYEAR)
(3)
SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)
FROM PAYLEVEL, PAYBYED
WHERE EDLEVEL=EDUC_LEVEL
AND HIREYEAR = YEAR_OF_HIRE
AND TOTAL_PAY < AVG_TOTAL_PAY