【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;
每天积累一点点
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· Spring AI + Ollama 实现 deepseek-r1 的API服务和调用
· 数据库服务器 SQL Server 版本升级公告
· 程序员常用高效实用工具推荐,办公效率提升利器!
· C#/.NET/.NET Core技术前沿周刊 | 第 23 期(2025年1.20-1.26)