相信很多高校都在用DATABASE SYSTEM CONCEPTS(Fifth Edition) 《数据库系统概念》(第五版)这本教材
在讲到3.8.2 The With Clause 这部分时,书中给出的例题是“Find all branches where the total account deposit is greater than the average of the total account deposits at all branches”翻译成中文大概意思是:找出所有支行,其存款总额大于总的平均存款。
书中给出的例程,blablabla的敲上去以后居然会报错。
此处笔者给出正确的代码,可自行比较:
1 WITH branch_total (branch_name, value) AS 2 (SELECT branch_name,SUM(balance) 3 FROM account 4 GROUP BY branch_name), 5 branch_total_avg(value) AS 6 (SELECT AVG(value) 7 FROM branch_total) 8 9 SELECT branch_name 10 FROM branch_total_avg,branch_total 11 WHERE branch_total.value >= branch_total_avg.value
需要注意的是点有:
1.WITH AS(....)需要用括号括起子查询
2.WITH AS (SLECET * FROM table1), /*此处用逗号隔开*/ WITH AS (SLECET * FROM table2)
3.WITH table_x AS (SLECET * FROM table1), table_y AS (SLECET * FROM table_X)/*此处为嵌套调用,注意比较与第二点的区别*/