【MySQL】一道MySQL综合题

题：下表是一张商品出售统计表，写一段简单的sql查询，查询出每种商品类型每个月的出售总额，其中类型1为实体商品，类型2为虚拟商品。表名goods_count。

id（自增id）	sold_time（出售时间戳）	amount（价格）	goods_type（商品类型）
1	1425265920	23.50	2
2	1428203520	50.00	1
3	1430709120	100.00	1
4	1430795520	65.25	1
5	1431659520	255.20	2

要求打印如下结果：

月份	实体商品	虚拟商品
2015-03	0.00	23.50
2015-04	50.00	0.00
2015-05	162.25	255.20

模拟

create DATABASE  `test`;

CREATE TABLE `goods_count`(
`id` int AUTO_INCREMENT PRIMARY KEY ,
`sold_time` int ,
`amount` FLOAT,
`goods_type` TINYINT
);

INSERT INTO `goods_count`(sold_time,amount,goods_type) VALUES(1425265920,23.50 ,2);
INSERT INTO `goods_count`(sold_time,amount,goods_type) VALUES(1428203520,50.00 ,1);
INSERT INTO `goods_count`(sold_time,amount,goods_type) VALUES(1430709120,100.00 ,1);
INSERT INTO `goods_count`(sold_time,amount,goods_type) VALUES(1430795520,65.25 ,1);
INSERT INTO `goods_count`(sold_time,amount,goods_type) VALUES(1431659520,255.20 ,2);

分析:

(1) 按月获取实体商品的出售总额

SELECT ALL from_unixtime(sold_time, '%Y-%m') as m, IF(sum(amount) IS NULL , 0, round(sum(amount),2) ) as s 
FROM `goods_count`  
WHERE goods_type=1 
GROUP BY m   

（2）按月获取虚拟商品的出售总额

SELECT ALL from_unixtime(sold_time, '%Y-%m') as m, IF(sum(amount) IS NULL , 0, round(sum(amount),2) ) as s 
FROM `goods_count` 
WHERE goods_type=2
GROUP BY m 

（3）现在的问题是如何将两个表连接在一起？

• 左连接：

SELECT ALL t1.m as '月份', IF(t1.s IS NULL , 0, t1.s) as '实体商品', IF(t2.s IS NULL , 0, t2.s)  as '虚拟商品' 
FROM (
SELECT ALL from_unixtime(sold_time, '%Y-%m') as m, round(sum(amount),2) as s 
FROM `goods_count`  
WHERE goods_type=1 
GROUP BY m 
) as t1 
LEFT JOIN
(
SELECT ALL from_unixtime(sold_time, '%Y-%m') as m, round(sum(amount),2)  as s 
FROM `goods_count` 
WHERE goods_type=2
GROUP BY m
) as t2 
ON t1.m = t2.m;

• 右连接:

SELECT ALL t1.m as '月份', IF(t1.s IS NULL , 0, t1.s) as '实体商品', IF(t2.s IS NULL , 0, t2.s)  as '虚拟商品' 
FROM (
SELECT ALL from_unixtime(sold_time, '%Y-%m') as m, round(sum(amount),2) as s 
FROM `goods_count`  
WHERE goods_type=1 
GROUP BY m 
) as t1 
RIGHT JOIN
(
SELECT ALL from_unixtime(sold_time, '%Y-%m') as m, round(sum(amount),2)  as s 
FROM `goods_count` 
WHERE goods_type=2
GROUP BY m
) as t2 
ON t1.m = t2.m;

• 右连接优化:

SELECT ALLIF( t1.m IS NULL, t2.m, t1.m) as '月份', IF(t1.s IS NULL , 0, t1.s) as '实体商品', IF(t2.s IS NULL , 0, t2.s)  as '虚拟商品' 
FROM (
SELECT ALL from_unixtime(sold_time, '%Y-%m') as m, round(sum(amount),2) as s 
FROM `goods_count`  
WHERE goods_type=1 
GROUP BY m 
) as t1 
RIGHT JOIN
(
SELECT ALL from_unixtime(sold_time, '%Y-%m') as m, round(sum(amount),2)  as s 
FROM `goods_count` 
WHERE goods_type=2
GROUP BY m
) as t2 
ON t1.m = t2.m;

• 思考：
  此处的主要问题在于ON的连接条件，导致不能将未对应的月份显示出来。

最后只想到了一个最暴力的方法==》三表连接

SELECT ALL a.m as '月份', IF(t1.s IS NULL , 0, t1.s) as '实体商品', IF(t2.s IS NULL , 0, t2.s)  as '虚拟商品' 
FROM 
(select from_unixtime(sold_time, '%Y-%m') as m 
from goods_count 
group by m 
) as a 
left join (
SELECT ALL from_unixtime(sold_time, '%Y-%m') as m, round(sum(amount),2) as s 
FROM `goods_count`  
WHERE goods_type=1 
GROUP BY m 
) as t1 
on a.m = t1.m 
left JOIN
(
SELECT ALL from_unixtime(sold_time, '%Y-%m') as m, round(sum(amount),2)  as s 
FROM `goods_count` 
WHERE goods_type=2
GROUP BY m
) as t2 
on a.m =t2.m;