使用with语句~~
(你不问出来,我也快忘了有这么回事)WITH
DEPT_COSTS AS ( SELECT D.department_name, SUM(E.salary) AS dept_total
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.department_id = D.department_id
GROUP BY D.department_name),
AVG_COST AS ( SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM DEPT_COSTS)
SELECT *
FROM DEPT_COSTS
WHERE dept_total > (SELECT dept_avg FROM AVG_COST)
ORDER BY department_name;
(你不问出来,我也快忘了有这么回事)WITH
DEPT_COSTS AS ( SELECT D.department_name, SUM(E.salary) AS dept_total
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.department_id = D.department_id
GROUP BY D.department_name),
AVG_COST AS ( SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM DEPT_COSTS)
SELECT *
FROM DEPT_COSTS
WHERE dept_total > (SELECT dept_avg FROM AVG_COST)
ORDER BY department_name;
太爱你啦!!!多谢!再问多一句:
with DEPT_COSTS AS ( SELECT D.department_name, SUM(E.salary) AS dept_total
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.department_id = D.department_id
GROUP BY D.department_name)
这一句,如果后面多次用到DEPT_COSTS,那么DEPT_COSTS对应的查询语句是只查一次,还是多次呢?
您好,我们是“2006中国杰出数据库工程师评选”活动组委会。
您的帖子已经被我们转载到本次评选官方网站的“专家在线答疑”区。
http://www.bestdba.cn/match_discussion.aspx在那里,进入本次评选复选的90位数据库工程师将与您展开积极的互动。
一方面,他们会为您的问题提供满意的答案,
另一方面,也邀请您为他们投上宝贵的选票。2006-7-8 ~ 2006-7-25日,每天我们将从当天参与"有奖投票"的网友
中抽取3名幸运者,赠送由IBM提供的精美礼品一份!此外,您还可以在“专家在线答疑”区提出新的问题并参与讨论。您的帖子位于:
http://www.bestdba.cn/match_discussion3.aspx?pointid=199&pointid2=1&pointid3=5&pcount=stc非常感谢您对本次活动的支持!
--------------------------------------------------------------
注册日期: Jul,2006
发贴数: 0 1.建立临时表(##)的那种
2.做个物化视图