接着 MySQL实战笔试(1)继续,有需要的可以查看(1)部分点击打开链接
此部分主要添加了case when 用法
根据下表输出#日期,客户数,订单数,销售额,早上0-7点的销售额,0-7点销售额占比
SELECT
SUBSTR(a.order_time, 1, 10) AS '日期',
COUNT(DISTINCT a.member_id) AS '客户数(去重)',
COUNT(a.order_id) AS '订单数',
SUM(
CASE
WHEN SUBSTR(a.order_time, 12, 5) <= '07:00' THEN
a.pay_nmt
ELSE
0
END
) AS '0-7点销售额',
SUM(a.pay_nmt) AS '总销售额',
SUM(
CASE
WHEN SUBSTR(a.order_time, 12, 5) <= '07:00' THEN
a.pay_nmt
ELSE
0
END
) / SUM(a.pay_nmt) AS '0-7点销售额占比'
FROM
table_order a
GROUP BY
SUBSTR(a.order_time, 1, 10)
结果如下