对mysql 查询实战3-题目，进行一个解答
11、查询每⽉产品交易与退款情况
目标：查询每⽉产品交易（交易总额，交易数）与退款情况（退款总额，退款数）
1，先把日期格式化
使用 EXTRACT
SELECT EXTRACT(YEAR_MONTH FROM t.trans_date) AS months FROM transactions t;
这个不符合我们常见的年月格式，还是用DATE_FORMAT
使用DATE_FORMAT
SELECT id, product_id, amount, DATE_FORMAT(t.trans_date, '%Y-%m') AS months
FROM transactions t;
2，统计成功的 ,加个flag标记， 0 成功， 1 失败
SELECT product_id, amount AS sum_amount,
DATE_FORMAT(t.trans_date, '%Y-%m') AS months, 0 AS flag
FROM transactions t WHERE state = 'success' ;3，统计失败的，加个flag标记， 0 成功， 1 失败
SELECT product_id, SUM(amount) AS sum_amount,
DATE_FORMAT(r.trans_date, '%Y-%m') AS months, 1 AS flagFROM transactions t, refund r
WHERE t.id = r.trans_id
GROUP BY product_id, months;4， 拼接到一起
SELECT product_id, amount AS sum_amount,
DATE_FORMAT(t.trans_date, '%Y-%m') AS months, 0 AS flag
FROM transactions t WHERE state = 'success'
UNION
SELECT product_id, SUM(amount) AS sum_amount,
DATE_FORMAT(r.trans_date, '%Y-%m') AS months, 1 AS flagFROM transactions t, refund r
WHERE t.id = r.trans_id
GROUP BY product_id, months;5，再进行判断统计
SELECT months, product_id,
SUM(CASE WHEN flag=0 THEN sum_amount ELSE 0 END) AS success_total,
COUNT(CASE WHEN flag=0 THEN 1 ELSE NULL END) AS success_count,
SUM(CASE WHEN flag=1 THEN sum_amount ELSE 0 END) AS fail_total,
COUNT(CASE WHEN flag=1 THEN 1 ELSE NULL END) AS fail_count
FROM(
SELECT product_id, amount AS sum_amount,
DATE_FORMAT(t.trans_date, '%Y-%m') AS months , 0 AS flag
FROM transactions t WHERE state = 'success'
UNION ALL
SELECT product_id, SUM(amount) AS sum_amount,
DATE_FORMAT(r.trans_date, '%Y-%m') AS months , 1 AS flagFROM transactions t, refund r
WHERE t.id = r.trans_id
GROUP BY product_id, months
) AS tmp GROUP BY product_id, months
ORDER BY months,product_id;12、查询活动产品的平均价格
1，关联查询
SELECT * FROM sold AS s, activity AS a
WHERE s.product_id=a.product_id
AND s.purchase_date BETWEEN a.start_date AND a.end_date;2，进行统计： 计算平均值，总价格/总数量
SELECT s.product_id, SUM(s.num*a.price)/SUM(num) AS average_price
FROM sold AS s, activity AS a
WHERE s.product_id=a.product_id
AND s.purchase_date BETWEEN a.start_date AND a.end_date
GROUP BY a.product_id;3，保留两位小数
SELECT s.product_id, ROUND(SUM(s.num*a.price)/SUM(num),2) AS average_price
FROM sold AS s, activity AS a
WHERE s.product_id=a.product_id
AND s.purchase_date BETWEEN a.start_date AND a.end_date
GROUP BY a.product_id;13、统计部⻔每⽉收⼊概况
1，先统计一月的情况
SELECT department_id,
SUM(CASE MONTH WHEN 'Jan' THEN income ELSE 0 END) Jan_income
FROM department_income
GROUP BY department_id;2，再统计全年的
SELECT department_id,
SUM(CASE MONTH WHEN 'Jan' THEN income ELSE 0 END) Jan_income,
SUM(CASE MONTH WHEN 'Feb' THEN income ELSE 0 END) Feb_income,
SUM(CASE MONTH WHEN 'Mar' THEN income ELSE 0 END) Mar_income,
SUM(CASE MONTH WHEN 'Apr' THEN income ELSE 0 END) Apr_income,
SUM(CASE MONTH WHEN 'May' THEN income ELSE 0 END) May_income,
SUM(CASE MONTH WHEN 'Jun' THEN income ELSE 0 END) Jun_income,
SUM(CASE MONTH WHEN 'Jul' THEN income ELSE 0 END) Jul_income,
SUM(CASE MONTH WHEN 'Aug' THEN income ELSE 0 END) Aug_income,
SUM(CASE MONTH WHEN 'Sep' THEN income ELSE 0 END) Sep_income,
SUM(CASE MONTH WHEN 'Oct' THEN income ELSE 0 END) Oct_income,
SUM(CASE MONTH WHEN 'Nov' THEN income ELSE 0 END) Nov_income,
SUM(CASE MONTH WHEN 'Dec' THEN income ELSE 0 END) Dec_income
FROM department_income
GROUP BY department_id3，改成用if的方式：
IF(expr, vl, v2)，如果表达式expr是TRUE(expr > 0 and expr NULL)，则IF()的返回值为v1；否则返回值为v2
SELECT department_id,
SUM(IF(MONTH = 'Jan' , income, 0)) Jan_income,
SUM(IF(MONTH = 'Feb' , income, 0)) Feb_income,
SUM(IF(MONTH = 'Mar' , income, 0)) Mar_income,
SUM(IF(MONTH = 'Apr' , income, 0)) Apr_income,
SUM(IF(MONTH = 'May' , income, 0)) May_income,
SUM(IF(MONTH = 'Jun' , income, 0)) Jun_income,
SUM(IF(MONTH = 'Jul' , income, 0)) Jul_income,
SUM(IF(MONTH = 'Aug' , income, 0)) Aug_income,
SUM(IF(MONTH = 'Sep' , income, 0)) Sep_income,
SUM(IF(MONTH = 'Oct' , income, 0)) Oct_income,
SUM(IF(MONTH = 'Nov' , income, 0)) Nov_income,
SUM(IF(MONTH = 'Dec' , income, 0)) Dec_income
FROM department_income
GROUP BY department_id;14、统计课程新学员数量
1，找出人员在课程里面一开始的学习时间，即筛选出新学员
SELECT lr.user_id, lr.course_id, MIN(lr.created_at) start_study_date
FROM learning_records lr
GROUP BY lr.user_id, lr.course_id;2，加个时间条件，比如180天内容
SELECT * FROM (
SELECT lr.user_id, lr.course_id, MIN(lr.created_at) start_study_date
FROM learning_records lr
GROUP BY lr.user_id, lr.course_id
) AS temp WHERE DATEDIFF('2020-06-01', start_study_date) <= 180;3，再进行统计人数
SELECT course_id, start_study_date, COUNT(user_id) FROM (
SELECT lr.user_id, lr.course_id, MIN(lr.created_at) start_study_date
FROM learning_records lr
GROUP BY lr.user_id, lr.course_id
) AS temp WHERE DATEDIFF('2020-06-01', start_study_date) <= 180
GROUP BY start_study_date, course_id
ORDER BY course_id, start_study_date;15、平均销售额：部⻔与公司对⽐
目标： 计算每月部门和公司的收入比较
1，日期格式化
SELECT id, sale_id, income,
DATE_FORMAT(created_at,'%Y-%m') belong_month
FROM income;2，先计算公司的收入
SELECT AVG(income) AS avg_income,
DATE_FORMAT(created_at,'%Y-%m') AS belong_month
FROM income
GROUP BY belong_month;3，先计算部门的收入，关联人员表
SELECT sp.department_id, AVG(income) AS avg_income,
DATE_FORMAT(created_at,'%Y-%m') AS belong_month
FROM income ic, sales_person sp
WHERE ic.sale_id = sp.id
GROUP BY belong_month, sp.department_id;4，公司表和部门表，根据公司做一个关联查询
SELECT d.department_id, c.belong_month,
d.avg_income AS department_avg_income, c.avg_income AS company_avg_income FROM (SELECT AVG(income) AS avg_income,
DATE_FORMAT(created_at,'%Y-%m') AS belong_month
FROM income
GROUP BY belong_month) AS c, (SELECT sp.department_id, AVG(income) AS avg_income,
DATE_FORMAT(created_at,'%Y-%m') AS belong_month
FROM income ic, sales_person sp
WHERE ic.sale_id = sp.id
GROUP BY belong_month, sp.department_id) AS d
WHERE c.belong_month = d.belong_month;5，再加个标记，判断是高，还是低了
-- if只能判断二元的，这边得用case when
SELECT d.department_id, c.belong_month,
(CASE WHEN d.avg_income > c.avg_income THEN 'higher'
WHEN d.avg_income < c.avg_income THEN 'lower' ELSE 'same' END) AS mark,
d.avg_income AS department_avg_income, c.avg_income AS company_avg_income FROM (SELECT AVG(income) AS avg_income,
DATE_FORMAT(created_at,'%Y-%m') AS belong_month
FROM income
GROUP BY belong_month) AS c,
(SELECT sp.department_id, AVG(income) AS avg_income,
DATE_FORMAT(created_at,'%Y-%m') AS belong_month
FROM income ic, sales_person sp
WHERE ic.sale_id = sp.id
GROUP BY belong_month, sp.department_id) AS d
WHERE c.belong_month = d.belong_month;总结：
在统计后，进行进一步过滤的时候，case when就非常好用了。if只能判断二元的，有限制。case when可以使用多元，可以统计各种情况，再聚合，把多列的，合并成单列数据，数据再进行处理就很方便了
上一篇： 《mysql 查询实战3-题目》
下一篇： 《mysql 日环比 统计》
