SQL

2021/11/4 19:11:06

编程Tag： select id name SQL total region amt

本文主要是介绍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的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

SQL

相关编程文章