SQL 子查询和临时表格

最新推荐文章于 2024-07-28 03:33:40 发布
最新推荐文章于 2024-07-28 03:33:40 发布
阅读量519 收藏
点赞数
分类专栏： SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m493096871/article/details/89453202
版权

首个子查询解决方案

  1. 首先，我们需要按照日期和渠道分组。然后按事件数（第三列）排序，这样可以快速得出第一个问题的答案。 
    SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2
ORDER BY 3 DESC;
  2. 可以看出，要获得这一结果，提供了整个原始表格。查询的附加部分包括 *，并且我们需要为表格设置别名。此外，是在 SELECT 语句中（而不是 FROM）中提供表格。 
    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;
  3. 最后，我们在以下语句中能够获得显示每个渠道一天的平均事件数的表格。 
    SELECT channel, AVG(events) AS average_events
FROM (SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2) sub
GROUP BY channel
ORDER BY 2 DESC;

 

格式清晰的查询

与之前的示例相比，在这个格式清晰的示例中，我们很容易就看出要从哪个表格中获取数据。此外，如果在子查询后面有 GROUP BY、ORDER BY、WHERE、HAVING 或任何其他语句，则按照外部查询的同一级别缩进，正如最后一个示例所显示的，它是上个练习的最后一个解决方案。

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;

下面的查询很相似，但是向外部查询应用了其他逻辑，因此按照外部查询的级别缩进。而内部查询逻辑的缩进级别与内部表格匹配。

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;

最后两个查询容易读懂多了！

 

  1. 以下是从 orders 表格中获取第一个订单的年/月信息的查询。

    SELECT DATE_TRUNC('month', MIN(occurred_at))
FROM orders;

  2. 然后，为了获取每个订单的平均值，我们可以在一个查询中执行所有的任务。但是为了便于阅读，我在下面提供了两个查询，单独执行每一步。

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

 

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

    首先，我要算出与每个销售代表相关的总销售额 (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_amt_usd。 
    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;
    然后，我们仅从该表格中获取销售额最高的区域。可以通过两种方法来获取，一种是使用子查询后的最大值，另一种是按降序排序，然后获取最高值。 
    SELECT MAX(total_amt)
FROM (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) sub;
    最终，我们要获取具有该区域销售额的总订单量： 
    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 MAX(total_amt)
FROM (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) sub);
    结果就是 Northeast，总订单为 1230378 个。
     

  3. 对于购买标准纸张数量 (standard_qty) 最多的客户（在作为客户的整个时期内），有多少客户的购买总数依然更多？

    首先，我们要得出购买标准纸张数量 (standard_qty) 最多的客户。以下查询获取了该客户，以及总消费：

    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;

    现在，我将使用上述信息获取总消费更高的所有客户：

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

    上述查询列出了具有更多订单的客户列表。我们还可以使用另一个简单的子查询获取数量。

    SELECT COUNT(*)
FROM (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) inner_tab)
) counter_tab;

  4. 对于（在作为客户的整个时期内）总消费 (total_amt_usd) 最多的客户，他们在每个渠道上有多少 web_events？

    我们首先需要获取在整个客户时期内消费最多的客户。

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

    现在，我们要获取该企业（可以使用 id 进行匹配）在每个渠道上的事件数。

    SELECT a.name, w.channel, COUNT(*)
FROM accounts a
JOIN web_events w
ON a.id = w.account_id AND a.id =  (SELECT id
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 1) inner_table)
GROUP BY 1, 2
ORDER BY 3 DESC;

    我添加了 ORDER BY，其实并没特别的理由，并添加了客户名称，确保仅从一个客户那获取数据。
     

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

    首先，我们需要找出总消费 (total_amt_usd) 在前十名的客户。 
    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 (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;
  6. 比所有客户的平均消费高的企业平均终身消费 (total_amt_usd) 是多少？

    首先，算出所有客户的总消费 (total_amt_usd) 平均值： 
    SELECT AVG(o.total_amt_usd) avg_all
FROM orders o
JOIN accounts a
ON a.id = o.account_id;
    然后，只获取高于这一平均值的客户。 
    SELECT o.account_id, AVG(o.total_amt_usd)
FROM orders o
GROUP BY 1
HAVING AVG(o.total_amt_usd) > (SELECT AVG(o.total_amt_usd) avg_all
FROM orders o
JOIN accounts a
ON a.id = o.account_id);
    最后，算出这些值的平均值。 
    SELECT AVG(avg_amt)
FROM (SELECT o.account_id, AVG(o.total_amt_usd) avg_amt
FROM orders o
GROUP BY 1
HAVING AVG(o.total_amt_usd) > (SELECT AVG(o.total_amt_usd) avg_all
FROM orders o
JOIN accounts a
ON a.id = o.account_id)) temp_table;

 

第一个 WITH (CTE)

下面是“你的第一个子查询”部分的问题和解决方案。

问题：你需要算出每个渠道每天的平均事件数。

解决方案：

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

我们使用 WITH 语句重新编写查询。

注意：你可以获取内部查询：

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

我们在此部分放入 WITH 语句。注意，在下面我们将表格的别名设为 events：

WITH events AS (
SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2)

现在，我们可以像对待数据库中的任何其他表格一样使用这个新创建的 events 表格：

WITH events AS (
SELECT DATE_TRUNC('day',occurred_at) AS day,
channel, COUNT(*) as events
FROM web_events
GROUP BY 1,2)

SELECT channel, AVG(events) AS average_events
FROM events
GROUP BY channel
ORDER BY 2 DESC;

对于上述示例，我们只需一个额外的表格，但是想象下我们要创建第二个表格来从中获取数据。我们可以按照以下方式来创建额外的表格并从中获取数据：

WITH table1 AS (
SELECT *
FROM web_events),

table2 AS (
SELECT *
FROM accounts)


SELECT *
FROM table1
JOIN table2
ON table1.account_id = table2.id;

然后，你可以按照相同的方式使用 WITH 语句添加越来越多的表格。底部的练习将确保你掌握了这些新查询的所有必要组成部分。

 

WITH 解决方案

以下是使用 WITH 条件重新编写的之前问题的答案。这些查询通常更容易读懂。

  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) 的区域，总共下了多少个订单？
      
    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. 对于购买标准纸张数量 (standard_qty) 最多的客户（在作为客户的整个时期内），有多少客户的购买总数依然更多？
      
    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) 最多的客户，他们在每个渠道上有多少 web_events？
      
    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 1)
SELECT a.name, w.channel, COUNT(*)
FROM accounts a
JOIN web_events w
ON a.id = w.account_id AND a.id =  (SELECT id FROM t1)
GROUP BY 1, 2
ORDER BY 3 DESC;
  5. 对于总消费前十名的客户，他们的平均终身消费 (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;
  6. 比所有客户的平均消费高的企业平均终身消费 (total_amt_usd) 是多少？
      
    WITH t1 AS (
SELECT AVG(o.total_amt_usd) avg_all
FROM orders o
JOIN accounts a
ON a.id = o.account_id),
t2 AS (
SELECT o.account_id, AVG(o.total_amt_usd) avg_amt
FROM orders o
GROUP BY 1
HAVING AVG(o.total_amt_usd) > (SELECT * FROM t1))
SELECT AVG(avg_amt)
FROM t2;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

月夜星星雨
关注
专栏目录
MySQL中的子查询,最好最全的子查询分类
妖怪的博客
10-24 1722
MySQL中的子查询,最好最全的子查询分类,子查询表达式有四种类型，分别为表格(tabular)、柱形(columnar)、行(row)、和标量(scalar)。
SQL学习-2.8 子查询和临时表格
Jasminexjf的博客
05-02 384
3.练习-首个子查询 在写带有子查询的句子时，为了阅读起来更容易，应该要按照正确的缩进方式，将内查询整体进行缩进。 问题： 对现有的这个的数据库进行分析，那些渠道平均每天发送给Patch&Posey的流量最大 解法：首先建立该问题的内查询：统计每个渠道每天的访问次数(count(*)),然后基于内查询的结果集进行外查询：计算访问次数的平均值avg(). SELECT chann...
SQL 查询语句（五）子查询
weixin_55845054的博客
01-02 473
select语句中嵌套select语句，被嵌套的select语句就叫子查询，子查询可以出现在：select..(select)..from..(select)..where..(select)..案例：找出比最低工资高的员工姓名和工资？注意错误语句：select ename,sal from emp where sal>min(sal);因为where后面不能使用分组函数正确思路：第一步，查询出最低工资select min(sal) from emp;第二步，查询大于最低工资的员工名字和工资：select
mysql子查询和临时表的区别
最新发布
weixin_41100491的博客
07-28 121
MySQL子查询与临时表的区别 作为一名经验丰富的开发者，我经常被问到关于MySQL子查询和临时表的区别。今天，我将通过这篇文章，详细解释这两者之间的差异，并指导如何使用它们。 子查询与临时表概述 在MySQL中，子查询和临时表都是用来处理数据集合的查询技术。它们的主要区别在于使用场景和性能。 子查询 子查询是在另一个查询...
mysql 子查询 as table_MySQL基础三种类型的子查询
weixin_42318727的博客
01-19 848
4、复制表子查询(1)复制表(1)拷贝表结构CREATE TABLE newadmin LIKE admin;(2)拷贝表结构和数据(但约束与索引除外)CREATE TABLE newadmin AS ( SELECT * FROM admin ) ;(3)拷贝表结构+数据CREATE TABLE newadmin LIKE admin;INSERT INTO newadmin SEL...
SQL：子查询以及实际工作中的应用
qq_45186811的博客
03-11 1786
最近在给客户进行系统切换，非直连升直连；切换新软件，需要在新软件中导入期初资料；通过excel表格收集期初资料，然后进行SQLServer数据库的导入导出；在系统上线前，需要对客户提供的期初资料进行测试，确保客户提供的期初资料的完整性和准确性；期初资料包括：商品、供应商、客户、门店库存、总部库存等等；
小黑小波比.region查询sql语句
u010489586的专栏
11-11 1132
1.获取当前用户id,带id在表中查另一个id,然后带着id在表中查region
sql 子查询 in 临时表
04-20
而IN子查询和临时表都是子查询的一种形式。 1. IN子查询：IN子查询用于判断某个值是否存在于子查询的结果集中。它的语法形式如下： ``` SELECT 列名 FROM 表名 WHERE 列名 IN (子查询) ``` 子查询可以是一个...
Oracle SQL：多表连接与子查询中的ALL运算符应用
在Oracle SQL中，这些技术尤其关键，因为Oracle数据库系统支持各种类型的连接和复杂的子查询操作。 首先，我们要了解多表连接。这涉及到将两个或多个表格的数据结合在一起，以便于分析和检索。Oracle SQL提供了多种...
sql查询表中根据某列排序的任意行语句
09-10
在这个例子中，子查询用于生成带有行号的临时结果集。 4. **外部查询（主查询）**：使用子查询的结果，通过`where`子句进一步过滤数据，得到最终想要的行。 5. **排序（ORDER BY）**：在`over(order by 列 desc)`...
Flink SQL中的动态表和临时表
热门推荐
huahuaxiaoshao的博客
07-22 1万+
1 动态表 1.1 数据流上的关系查询       关系型SQL与stream processing对比如下： SQL 流处理 有限元组 无限元组 完整数据集上的查询 无法基于所有数据查询 查询会结束 查询不会结束       Materialized View被定义为一条SQL查询，其会缓存查询结果。但当所查询的表(基表)被修改时，缓存的结果将过期。· &nb
region.sql
01-12
这个是我刚刚在http://www.stats.gov.cn/tjsj/tjbz/xzqhdm/上面找的最新的行政代码
省市区sql语句之：（三）区1
正在改bug的博客
10-29 404
DROP TABLE IF EXISTS `areas`; CREATE TABLE `areas` ( `id` int(11) NOT NULL auto_increment, `areaid` varchar(20) NOT NULL, `area` varchar(50) NOT NULL, `cityid` varchar(20) N...
数据库之简单SQL（一）
博客
04-05 1268
本篇文章是笔者学习《数据库系统概念》总结来的，主要介绍SQL的基本结构和概念。 SQL语言有以下几个部分： 数据定义语言（Data-Definition Language，DDL）：SQL DDL提供定义关系模式、删除关系以及修改关系模式的命令。 数据操纵语言（Data-Manipulation Language，DML）：SQL DML提供从数据库中查询信息，以及在数据库中插入元组、删除元...
Mysql学习笔记九--子查询
weixin_44501366的博客
06-01 1260
1. mysql表子查询 子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询 单行子查询:指只返回一行数据的子查询语句 多行子查询:指返回多行数据的子查询 使用关键字 in 2. 子查询临时表 将子查询当作一张临时表来使用 要求: 查询ecshop中各个类别中,价格最高的商品 第一步:先得到 各个类别中,价格最高的商品 第二步:把第一步的子查询当作临时表 3. all 和 any 关键字 问题: 显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号. 问题:
SqlServer_临时表查询和内联子查询的使用；
cyb专栏
07-10 3938
功能：查询订单表中每个顾客第一次下订单的订单号和时间； (1)、临时表使用 USE AdventureWorks2008 GO SELECT soh.CustomerID, MIN(soh.OrderDate) AS OrderDate INTO #MinOrderDates FROM Sales.SalesOrderHeader soh GROUP BY soh.CustomerID; S
sqlserver自定义折叠符号
終99 的博客
07-10 4881
官方没有这个功能!  是不是很不人性化,  类似 VS  #region  #endregion 的功能很方便 ,  不过机制的宝宝想到一个黑科技可以实现此功能 .  很简单 就是 ;  begin  --  end 折叠之前 : begin  --L_Menu 表select * from  Meeting order by M_CreateTime descselect * from  Mee...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值