SQL（四）

最新推荐文章于 2022-05-12 16:33:49 发布
最新推荐文章于 2022-05-12 16:33:49 发布
阅读量351 收藏
点赞数
分类专栏： sql 文章标签： sql子查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_38300566/article/details/86503750
版权

目录

SQL子查询和临时表格

子查询

子查询（第一部分）

子查询（第二部分）

练习题目

with

练习题目

 

子查询

子查询（第一部分）

我们想要计算每个渠道每天的平均事件次数。

第一个表格将提供每个渠道每天的事件次数，然后我们将使用第二个查询对这些值求平均。

/*
select DATE_TRUNC('day',occurred_at) as day,
channel,
count(*) event_count
from web_events
group by 1,2
order by event_count DESC;

select *
from
(select DATE_TRUNC('day',occurred_at) as day,
channel,
count(*) event_count
from web_events
group by 1,2
order by event_count DESC) sub
*/

select channel,
avg(event_count) avg_event_counts
from
(select DATE_TRUNC('day',occurred_at) as day,
channel,
count(*) event_count
from web_events
group by 1,2 
) sub
group by channel;

子查询格式

在编写子查询时，查询很容易就看起来很复杂。为了便于阅读：

要记住的重要事项是，在使用子查询时，要让读者能够轻松地判断查询的哪个部分将一起执行。大部分人的做法是按照某种方式缩进子查询，

SELECT *
FROM (SELECT DATE_TRUNC('day',occurred_at) AS day,
                channel, COUNT(*) as events
      FROM web_events 
      GROUP BY 1,2
      ORDER BY 3 DESC) sub
GROUP BY channel
ORDER BY 2 DESC;

子查询（第二部分）

在上面的子查询中，你编写了一个子查询来创建表格，然后可以在 FROM 语句中查询该表格。但是，如果只返回一个值，则可以在逻辑语句中使用该值，例如 WHERE、HAVING，甚至 SELECT，该值可以嵌套在 CASE 语句中。

标量子查询的书写位置并不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说， 能够使用常数或者列名的地方，无论是 SELECT 子句、 GROUP BY 子句、 HAVING 子句，还是ORDER BY 子句，几乎所有的地方都可以使用。

注意：在条件语句中编写子查询时，不能包含别名。这是因为该子查询会被当做单个值（或者对于 IN 情况是一组值），而不是一个表格。同时注意，这里的查询对应的是单个值。如果我们返回了整个列，则需要使用 IN 来执行逻辑参数。如果我们要返回整个表格，则必须为该表格使用别名，并对整个表格执行其他逻辑。

/*
SELECT DATE_TRUNC('month', MIN(occurred_at))
FROM orders;
*/
      
SELECT AVG(standard_qty) avg_std, AVG(gloss_qty) avg_gls, AVG(poster_qty) avg_pst
FROM orders
WHERE DATE_TRUNC('month', occurred_at) =
(SELECT DATE_TRUNC('month', MIN(occurred_at)) FROM orders);

SELECT SUM(total_amt_usd)
FROM orders
WHERE DATE_TRUNC('month', occurred_at) =
(SELECT DATE_TRUNC('month', MIN(occurred_at)) FROM orders);

练习题目

以下是数据库的 ERD，当你处理下面的练习时，可能会用到该 ERD。

练习1：提供每个区域拥有最高销售额 (total_amt_usd) 的销售代表的姓名

#每个销售代表在每个区域总的销售额
SELECT s.name rep_name, 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 1,2
ORDER BY 3 DESC;

#每个区域的最大销售额
SELECT region_name, MAX(total_amt) total_amt
FROM(SELECT s.name rep_name, 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 1, 2) inner1
GROUP BY 1;

#将得到的两个表进行联结，得到最终结果
SELECT t1.rep_name, t1.region_name, t1.total_amt
FROM(SELECT s.name rep_name, 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 1,2
ORDER BY 3 DESC) t1
JOIN (SELECT region_name, MAX(total_amt) total_amt
FROM(SELECT s.name rep_name, 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 1, 2) inner1
GROUP BY 1) t2
ON t1.region_name = t2.region_name
AND t1.total_amt = t2.total_amt;

练习2：对于具有最高销售额 (total_amt_usd) 的区域，总共下了多少个订单 （total count orders） ？

#先获得最高销售额 (total_amt_usd) 的区域名称
select r.name
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 r.name                
order by sum(o.total_amt_usd) DESC                
limit 1

#利用标量子查询得到所需结果
select r.name,sum(total) total_counts
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
where r.name = (select r.name
                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 r.name
                order by sum(o.total_amt_usd) DESC
                limit 1)
group by r.name;

练习3：有多少客户的购买总数（total）比购买标准纸张数量 (standard_qty) 最多的客户（在作为客户的整个时期内）的购买总数（total）还多？

select a.name account_name,sum(o.standard_qty) total_std, sum(o.total) total
from accounts a
join orders o
on o.account_id = a.id
group by a.name
order by 2 DESC
limit 1;

SELECT a.name
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY 1
HAVING SUM(o.total) > (SELECT total
FROM (SELECT a.name act_name, SUM(o.standard_qty) tot_std, SUM(o.total) total
FROM accounts a
JOIN orders o
ON o.account_id = a.id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1) sub);

练习4：对于总消费前十名的客户，他们的平均终身消费 (total_amt_usd) 是多少?

SELECT AVG(tot_spent)
FROM (SELECT a.id, a.name, SUM(o.total_amt_usd) tot_spent
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY 3 DESC
LIMIT 10) temp;

with

WITH 语句经常称为公用表表达式（简称 CTE）。虽然这些表达式和子查询的目的完全一样，但是实际更常用，因为更容易看懂其中的逻辑。

练习题目

练习之前的题目

练习1：提供每个区域拥有最高销售额 (total_amt_usd) 的销售代表的姓名

WITH t1 AS (
SELECT s.name rep_name, 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 1,2
ORDER BY 3 DESC),
t2 AS (
SELECT region_name, MAX(total_amt) total_amt
FROM t1
GROUP BY 1)
SELECT t1.rep_name, t1.region_name, t1.total_amt
FROM t1
JOIN t2
ON t1.region_name = t2.region_name AND t1.total_amt = t2.total_amt;

练习2：对于具有最高销售额 (total_amt_usd) 的区域，总共下了多少个订单 （total count orders） ？

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);

练习3：有多少客户的购买总数（total）比购买标准纸张数量 (standard_qty) 最多的客户（在作为客户的整个时期内）的购买总数（total）还多？

WITH t1 AS (
SELECT a.name account_name, SUM(o.standard_qty) total_std, SUM(o.total) total
FROM accounts a
JOIN orders o
ON o.account_id = a.id
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1),
t2 AS (
SELECT a.name
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY 1
HAVING SUM(o.total) > (SELECT total FROM t1))
SELECT COUNT(*)
FROM t2;

练习4：对于总消费前十名的客户，他们的平均终身消费 (total_amt_usd) 是多少?

WITH t1 AS (
SELECT a.id, a.name, SUM(o.total_amt_usd) tot_spent
FROM orders o
JOIN accounts a
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY 3 DESC
LIMIT 10)
SELECT AVG(tot_spent)
FROM t1;

 

ZLuby
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
别名在sql中的使用
qq_58504001的博客
08-26 1675
大多数数据库系统（例如 MySQL, PostgreSQL, SQL Server）允许在 ORDER BY 子句中使用别名。但是，请注意，不同的数据库系统可能有不同的规则。因此，建议在实际使用中测试一下你的查询，或者查阅相关的数据库系统文档。这是因为 WHERE 子句是在选择结果行之前应用的，因此别名尚未可用。在 SQL 中，别名（alias）是一个临时的名字，用于列或表。：通常可以使用别名，因为 HAVING 子句是在聚合后应用的，此时别名已经可用。别名的使用取决于数据库系统和查询的具体情况。
SQL 别名、通配符、聚合函数、子查询、GROUP BY 、LIMIT
weixin_55747284的博客
04-14 229
SQL 中别名、通配符、聚合函数、子查询、GROUP BY 、LIMIT的使用
关于mqsql中子查询起别名的问题
YingjiePei的博客
01-13 1321
mqsql中子查询起别名一定要起在子查询括号的外面，如： select count(*) count() from dataanalyst as table2 left join (select * from company where company.companySize = '150-500人' )as table1 on table1.companyId=table2.companyId...
SQL查询语句起别名
weixin_46245201的博客
01-15 4956
SQL查询语句起别名 --直接写后面 select dept_id deptID from dept; --加AS再写后面 select dept_id as deptID from dept; --用""括起来 select dept_id "deptID" from dept; --加AS,用""括起来 select dept_id as "deptID" from dept;
SQL入门之第十七讲——子查询
henry_rhy的博客
05-12 2441
子查询顾名思义，就是在在查询中，嵌套了一层子查询，用来完成更复杂的数据检索，子查询的结果将作为主查询的条件来使用 子查询主要的类型有： 子查询+ WHERE子句 子查询+ FROM子句 子查询+ SELECT子句 SQL语句中嵌套语句，是比较常见的，子查询用括号括起来。 基本语法： select <字段名> from <表名> where ( select <字段名> from <表名> ); 一般称外面的嵌套的语句为 主查询（也叫外查询），里面
中国区域sql 四级 全
05-09
中国区域sql 四级
详解SQL四种语言：DDLDMLDCLTCL
03-03
数据库定义语言：定义数据库的结构。其主要命令有CREATE，ALTER，DROP等，下面用例子详解。...还是挺常用的）COMMENT–为数据字典添加备注数据库操作语言：SQL中处理数据库中的数据其主要命令有INSERT,UPDATE,DELETE等
SQL四大功能
01-07
SQL四大功能 网上下载的，大家都可以看下。蛮不错的归纳。都很基础。菜鸟贴。各位莫笑
Sqlserver2000 四合一版本（中文版）
06-01
sqlserver2000个人版 sqlserver2000企业版 sqlserver2000开发版
mysql 表别名 子查询,SQL表/子查询别名约定
weixin_39811842的博客
02-18 262
I've been writing SQL for a number of years now on various DBMS (Oracle, SQL Server, MySQL, Access etc.) and one thing that has always struck me is the seemingly lack of naming convention when it come...
sql中用于子查询的几个关键词 any(some是 any的别名)，all,in,exists
kbx8916的博客
10-17 7526
sql中几个子查询关键词any,all,exists,in in,exists in表示值是否存在子查询结果集中 select * from A where name in(select name from B)  exists是表示子查询是否返回结果，而不管返回的具体内容。 select *from A where exists( select 1 from B where n
sql语句中from后面是子查询，需要给子查询加别名吗？
qq_42449963的博客
05-21 6298
我这里只谈论Oracle和mysql，因为我只会这两种数据库，O(∩_∩)O哈哈~ 结论： mysql中需要加，而Oracle中可以不加 证明： 同样的一条sql语句select * from (select ename from emp);，在mysql中执行会报错，错误如下： [SQL] select * from (select ename from emp); [Err] 1248 - Every derived table must have its own alias 如果在Oracle中执行
sql查询给表起别名要点（涉及嵌套查询）
热门推荐
weixin_39386430的博客
09-08 1万+
可以通过空格或者as给表起别名 但是注意如果操作的数据库是Oracle的话，只能使用空格，as不符合Oracle的语法。 举个栗子 简单查询中使用别名 select * from student s where s.id = '10'; 在简单的查询中使用别名，一般没有特别需要注意的地方，要做的操作少 复杂查询中使用别名 题目概要：有三个表格，student（sno,sname,ssex,sbi...
SQL查询规范1--单表嵌套查询需要给子查询赋予别名 as
weixin_59970720的博客
08-01 987
需求： 现有订单最早出账账期； 软件access；（测试MySQL也会报错） 单表查询， 需要嵌套子查询遇到了错误， 错误代码：子查询没有赋予别名， -- 查询订单首次出现的账期(错误代码) SELECT code1, month1, crm_bill_order_info.server_start_date FROM ( SELECT crm_bill_order_info.order_code AS code1, Min( crm_bill_order_info.account_
sql语句中的子查询
以梦为马 不负韶华
10-29 9013
 一、子查询分类： 1.独立子查询：子查询语句可以独立查询 2.相关子查询： 独立子查询：子查询可以独立运行。 相关子查询： 子查询中引用了父查询的结果，不可以独立运行。           所有查询都可以用相关子查询实现。exists(...)返回一个布尔值，只要exists括号里查到了数据exists()就返回true,否则返回false，如果返回true执行 exists前...
HQL子查询别名问题
xieganyu3460的博客
10-12 1680
HQL子查询别名问题      HQL的书写，select * from (select * from table_name) ;      执行此HQL，应该会报错：FAILED: ParseException line 3:52 cannot recognize input near '&lt;EOF&gt;' '&lt;EOF&gt;' '&lt;EOF&gt;' in subquery...
sql 四张表大数据查询
最新发布
09-08
SQL 四张表大数据查询是指在进行数据库查询时，涉及到四张表的查询操作...总的来说，SQL四张表大数据查询需要灵活运用连接语句和合适的查询优化方法，以实现复杂的查询需求，并在保证查询性能的前提下获取所需的结果。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值