SQL 习题总结 - with_sql with 语句练习题-优快云博客

本文链接：https://blog.youkuaiyun.com/mimimi7777/article/details/114866554

本文通过多个案例对比展示了 SQL 中使用 WITH 子句的方法及其优势。从基本的多表创建到复杂的数据分析场景，文章详细解释了如何利用 WITH 子句简化查询过程，提高代码的可读性和维护性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

How to use

The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database.
When creating multiple tables using WITH, you add a comma after every table except the last table leading to your final query.

WITH table1 AS (
          SELECT *
          FROM web_events),
     table2 AS (
          SELECT *
          FROM accounts)
SELECT *
FROM table1
JOIN table2
ON table1.account_id = table2.id;

The new table name is always aliased using table_name AS, which is followed by your query nested between parentheses.

WITH V.S. SUBQURIES

WITH - Don’t need the one additional table.

Questions

Provide the name of the sales_rep in each region with the largest amount of total_amt_usd sales.

Recall the subquries code:

select t3.rep_name, t3.region_name, t3.largest_amt 
from (select region_name, max(largest_amt ) largest_amt
      from (select s.name rep_name, r.name region_name, sum(total_amt_usd) largest_amt 
			from orders o 
			join accounts a 
			on a.id = o.account_id
			join sales_reps s
			on a.sales_rep_id = s.id
			join region r
			on s.region_id = r.id
			group by 1,2
			order by 3 desc) t1
			group by 1) t2
Join (select s.name rep_name, r.name region_name, sum(total_amt_usd) largest_amt
	  from orders o 
	  join accounts a 
	  on a.id = o.account_id
	  join sales_reps s
	  on a.sales_rep_id = s.id
	  join region r
	  on s.region_id = r.id
	  group by 1,2
	  order by 3 desc) t3
on t3.region_name = t2.region_name and t3.largest_amt = t2.largest_amt;

“With” Code:

WITH t1 AS (select s.name rep_name, r.name region_name, sum(total_amt_usd) largest_amt 
			from orders o 
			join accounts a 
			on a.id = o.account_id
			join sales_reps s
			on a.sales_rep_id = s.id
			join region r
			on s.region_id = r.id
			group by 1,2
			order by 3 desc),
     t2 AS (select region_name, max(largest_amt ) largest_amt
			from t1
			group by 1)
select t1.rep_name, t1.region_name, t1.largest_amt 
from t1
join t2
on t1.region_name=t2.region_name and t1.largest_amt=t2.largest_amt;

Note: 
我们想找最大的amount of total_amt_usd. 肯定要sort，就要想到ORDER ID。
其次为什么要用SUM不用count呢？因为我们要sum每个region的rep_name。如果用count的话，会导致region无法group by。

Analysis: 
We get there are 1098137.72 USD in southeast, and the rep_name is Earlie Schleusner.
We get there are 1010690.60 USD in northeast, and the rep_name is Tia Amato.
We get there are 886244.12 USD in west, and the rep_name is Georgianna Chisholm.
We get there are 675637.19 USD in midwest, and the rep_name is Charles Bidwell.

For the region with the largest sales total_amt_usd, how many total orders were placed?

Recall the subquries code:

select r.name, count(total) total_qty
from region r
join sales_reps s
on s.region_id = r.id
join accounts a
on a.sales_rep_id = s.id
join orders o
on o.account_id = a.id
group by 1
having sum(o.total_amt_usd) = (select max(largest_amt)
							   from (select r.name region_name, sum(o.total_amt_usd) largest_amt
							   from region r
							   join sales_reps s
							   on s.region_id = r.id
							   join accounts a
							   on a.sales_rep_id = s.id
							   join orders o
							   on o.account_id = a.id
							   group by 1
							   order by 2 desc)t1);

note:
count 和sum的区别
count 就是 returns the number of rows that matches a specified cirterion. i.e. where price = 18;
sum 就是 returns the total sum of a numeric column.
note:
这个是有condition的，所以要考虑where and having
WHERE => cannot be used with aggregate functions
HAVING => aggregate

*"With"* Code:

with t1 as (select r.name region_name, sum(o.total_amt_usd) largest_amt
		    from region r
		    join sales_reps s
		    on s.region_id = r.id
		    join accounts a
		    on a.sales_rep_id = s.id
		    join orders o
		    on o.account_id = a.id
		    group by 1
		    order by 2 desc),
     t2 as (select max(largest_amt) from t1)
select r.name, count(total) total_qty
from region r
join sales_reps s
on s.region_id = r.id
join accounts a
on a.sales_rep_id = s.id
join orders o
on o.account_id = a.id
group by 1
having sum(o.total_amt_usd) = (select * from t2);

ANALYSIS:
This provides the Northeast with 2357 orders.

note:
with and subquery 的区别
顺序不一样。
with -> 
with t1 as (), as ()
select ....
subquery 是先select ... from 各种table

How many accounts had more total purchases than the account name which has bought the most standard_qty paper throughout their lifetime as a customer?

Recall the subquries code:

select count(*)
from (select a.name
		 from accounts a
		 join orders o
		 on a.id = o.account_id
		 group by 1
         having sum(o.total) > (select total_qty from
		                                           (select a.name, sum(o.standard_qty), sum(o.total) total_qty
											     	from accounts a
													join orders o
													on a.id = o.account_id
													group by 1
													order by 2 desc
													limit 1) t1))t2

note:
Having 比较的是两个不同表的相同parameter

“With” Code:

with t1 as (select a.name, sum(o.standard_qty), sum(o.total) total_qty
		         from accounts a
				 join orders o
			     on a.id = o.account_id
				 group by 1
				 order by 2 desc
				 limit 1),
       t2 as (select a.name
				 from accounts a
				 join orders o
				 on a.id = o.account_id
				 group by 1
				 having sum(o.total) > (select total_qty from t1) )
select count(*)
from t2;

ANALYSIS
The  number of accounts is 3.

For the customer that spent the most (in total over their lifetime as a customer) total_amt_usd, how many web_events did they have for each channel?

Recall the subquries code:

select a.name, w.channel, count(*)
from web_events w
join accounts a
on w.account_id = a.id
where a. id = (select account_id from
				(select a.name, o.account_id, sum(o.total_amt_usd) total_spent
				from orders o
				join accounts a
				on a.id = o.account_id
				group by 1,2
				order by 3 desc
				limit 1) t1)
group by 1,2
order by 3 desc;

“With” Code:

with t1 as (select a.name, o.account_id, sum(o.total_amt_usd) total_spent
				from orders o
				join accounts a
				on a.id = o.account_id
				group by 1,2
				order by 3 desc
				limit 1)
select a.name, w.channel, count(*)
from web_events w
join accounts a
on w.account_id = a.id
where a. id = (select account_id from t1)
group by 1,2
order by 3 desc;

What is the lifetime average amount spent in terms of total_amt_usd for the top 10 total spending accounts?

Recall the subquries code:

select avg(total_spent)
from (
select a.id, a.name, sum(o.total_amt_usd) total_spent
from accounts a
join orders o
on o.account_id = a.id
group by 1,2
order by 3 desc
limit 10) t1;

“With” Code:

with t1 as (select a.id, a.name, sum(o.total_amt_usd) total_spent
					from accounts a
					join orders o
					on o.account_id = a.id
					group by 1,2
					order by 3 desc
					limit 10)
select avg(total_spent)
from t1;

What is the lifetime average amount spent in terms of total_amt_usd, including only the companies that spent more per order, on average, than the average of all orders.
select

Recall the subquries code:

select avg(avg_spent)
from(select o.account_id, avg(o.total_amt_usd) avg_spent
      from orders o
      group by 1
      having avg(total_amt_usd) > (select avg(total_amt_usd) all_orders
                                 from orders))t1

“With” Code:

with t1 as (select o.account_id, avg(o.total_amt_usd) avg_spent
			      from orders o
			      group by 1
			      having avg(total_amt_usd) > (select avg(total_amt_usd) all_orders
			                                 from orders))
select avg(avg_spent) from t1;

ANALYSIS:
The average amount is 4721.1397439971747168.