本文主要是介绍SQL,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
WITH的使用
|
|
WITH t1 AS ( SELECT r.name region_name, SUM(o.total_amt_usd) total_amt FROM sales_reps s JOIN accounts a ON a.sales_rep_id = s.id JOIN orders o ON o.account_id = a.id JOIN region r ON r.id = s.region_id GROUP BY r.name),
t2 AS ( SELECT MAX(total_amt) FROM t1)
SELECT r.name, SUM(o.total) total_orders FROM sales_reps s JOIN accounts a ON a.sales_rep_id = s.id JOIN orders o ON o.account_id = a.id JOIN region r ON r.id = s.region_id GROUP BY r.name HAVING SUM(o.total_amt_usd) = (SELECT * FROM t2);
|
WITH t1 AS( SELECT r.name region_name, SUM(o.total_amt_usd) total_amt, SUM(o.total) total_qty FROM sales_reps s JOIN accounts a ON a.sales_rep_id = s.id JOIN orders o ON o.account_id = a.id JOIN region r ON r.id = s.region_id GROUP BY r.name),
t2 AS( SELECT MAX(total_amt) total_amt FROM t1)
SELECT t1.region_name, t1.total_qty FROM t1 JOIN t2 ON t1.total_amt = t2.total_amt
|
|
|
这篇关于SQL的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!