关于Mysql子查询的三个应用场景

应用场景

一个select语句的查询结果能够作为另一个语句的输入值，主要有三种情况，分别为：

1.子查询可以出现在Where子句中，作为过滤条件

select 列名
from 表名
where 列操作符（select 列名 from 表名）

2.也可以出现在from子句中，作为一个临时表使用

select 列名
from （select 列名 from 表名）

3.能够出现在select list中，作为一个字段值来返回

select 列名，(select 列名 from 表名)
from 表名
where 列操作符

注意事项：

子查询只能有一个字段的情况
1. 子查询在select上面，一定是只能一个字段；如果超过一个字段，代码会报错
2. 子查询在where后面，当做一个过滤条件,这个字段也必须有且只有一个
可以有多个字段的情况
1. 当子查询在from后面，是可以有多个字段的，因为他就是一张表；
2. 注意：子查询在from后面做临时表来用,必须给子查询生成的临时表取别名，否则会报错
时间型字符，在 where 过滤如果是常量，需要用''；数字则不需要
子查询中，表是可以不一样的

子查询在Where中作过滤条件

2017年7月2号统计累计购买金额在100到200的会员，寻找这批会员的消费记录

先找出当日累计购买金额在100-200之间的会员用having来筛选

用查找出来的会员做过滤条件，查找消费记录

-- 先找出当日累计购买金额在100-200之间的会员
SELECT dimMemberID 
      ,SUM(AMT) as money
FROM dw.fct_sales
where dimDateID ='20170702'
and dimMemberID <> 0
group by dimMemberID
-- having sum(AMT)>100 and sum(AMT)<200;
having money BETWEEN 100 and 200;
-- 用查找出来的会员做过滤条件，查找消费记录
SELECT *
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <>0
and dimMemberID in (
                    SELECT dimMemberID 
                       -- ,SUM(AMT) as money    子查询在where后面，当做一个过滤条件,这个字段也必须有且只有一个,所以最后结果要返回过滤条件
                    FROM dw.fct_sales
                    where dimDateID ='20170702'
                    and dimMemberID <> 0
                    group by dimMemberID
                    having sum(AMT)>100 and sum(AMT)<200);
                    -- having money BETWEEN 100 and 200 );  不能直接用命名的列名money，要写全函数，因为前面语句没有出现money，所以为了避免报错，之后尽量写全函数

子查询在from中做临时表

2017年7月2日对每位会员累计购买金额进行分段

统计2017年7月2日每位会员的累计购买金额

把第一步统计出来的数据作为临时表，对统计出来的数据进行分组

-- 1、统计2017年7月2日每位会员的累计购买金额
SELECT dimMemberID 
      ,SUM(AMT) as money 
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <> 0
group by dimMemberID ;
-- 2、把第一步统计出来的数据作为临时表，对统计出来的数据进行分段
SELECT dimMemberID 
      ,money 
      ,case when money <100 then 'D'
            when money >=100 and money <500 then 'C'
            when money >=500 and money <1000 then 'B'
            when money >=1000 then 'A'
            else '其它'
       end as type1
FROM (SELECT dimMemberID                      /*临时表可以生成多个字段，并不是每个字段都需要用也行*/
            ,SUM(AMT) as money 
      FROM dw.fct_sales
      where dimDateID = '20170702'
      and dimMemberID <> 0
      group by dimMemberID) as sn;            /*子查询在from后面做临时表来用,必须给这个子查询生成的临时表取一个表的别名，否则会报错*/

子查询在select作为一个字段来返回

2017年7月2日计算每个会员购买金额，以及每个会员购买金额占总体金额的比

2017年7月2日每个会员购买金额

总体金额

合并两个表

注意：在合并时where的条件要写全，不要遗漏，不然容易出现逻辑错误

-- 1、2017年7月2日每个会员购买金额
SELECT dimMemberID 
      ,SUM(AMT) as money
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <>0                   /*去除非会员,即 dimMemberID 为 0 的数据*/
group by dimMemberID ;
-- 2、总体金额
SELECT SUM(AMT) 
FROM dw.fct_sales
where dimDateID = '20170702';
-- 3、合并两个表   注意在合并时where的条件要写全，不要遗漏，不然容易出现逻辑错误
SELECT dimMemberID 
      ,SUM(AMT) as money
      ,(SELECT SUM(AMT)                                /*统计总金额*/
        FROM dw.fct_sales
        where dimDateID = '20170702'
        and dimMemberID<>0) as total_money
      ,CONCAT(                                         /*加上%号*/
       ROUND(                                          /*四舍五入保留4位小数，带%一定要思考保留小数位数*/
       SUM(AMT)/(SELECT SUM(AMT) 
                 FROM dw.fct_sales
                 where dimDateID = '20170702'
                 and dimMemberID<>0),4)*100,'%') 
       as member_rate                                  /*合并表，统计占比*/
FROM dw.fct_sales
where dimDateID = '20170702'
and dimMemberID <>0                                    /*去除非会员,即 dimMemberID 为 0 的数据*/
group by dimMemberID ;