mysql 查询实战3-解答

对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 flag
 FROM 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 flag
 FROM 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 flag
 FROM 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_id

3，改成用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;