MySql学习笔记——子查询①（作为Select后返回字段）

5.1.1 业务需求：计算7月份某周每个会员订单数，以及每个会员订单占整体比

思路拆解：…… 待补充
思路复盘：…… 待补充

5.1.1  业务需求：计算7月份某周每个会员订单数，以及每个会员订单占整体比
-- 子查询-select

-- 我写的代码

SELECT
dimMemberID,
order_num,
(SELECT sum(order_num) from 
(
SELECT 
dimMemberID,
COUNT(salesNo) AS order_num
from fct_sales
WHERE dimDateID between '20170703' and '20170709'  
           and dimMemberID<>0 
GROUP BY dimMemberID
) a ) AS order_total,
CONCAT( ROUND( order_num /
(SELECT sum(order_num) from (
SELECT 
dimMemberID,
COUNT(salesNo) AS order_num
from fct_sales
WHERE dimDateID between '20170703' and '20170709'  
           and dimMemberID<>0 
GROUP BY dimMemberID
) a )  ,4 )*100 , '%') AS ratio

from 
(
SELECT 
dimMemberID,
COUNT(salesNo) AS order_num
from fct_sales
where dimDateID between '20170703' and '20170709'  -- 7月份某周
           and dimMemberID<>0 
GROUP BY dimMemberID
) a

-- 人家写的代码

# 计算7月份某周每个会员订单数，以及每个会员订单占整体比
select dimMemberID -- 每个会员
         ,count(distinct salesNo) as order_number -- 每个会员对应订单数( 要去重吗?)
				 ,(select count(salesNo) from dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID<>0) AS order_total
         ,count(distinct salesNo)/(select count(salesNo) from dw.fct_sales where dimDateID between '20170703' and '20170709' and dimMemberID<>0) as order_rate -- 常数项/订单占比
from dw.fct_sales -- 订单表
where dimDateID between '20170703' and '20170709'  -- 7月份某周
           and dimMemberID<>0 
group by dimMemberID;

部分输出结果：

– 学习记录，作为复盘使用