【MySQL】一道MySQL综合题
2024-08-29 18:20:36
题：下表是一张商品出售统计表，写一段简单的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;
最新文章
- Android PopupWindow Dialog 关于 is your activity running 崩溃详解
- XV Open Cup named after E.V. Pankratiev. GP of Tatarstan
- Sphinx扩展安装安装
- am335x 电容屏驱动添加。
- 好用的php类库和方法
- Linux及安全期中总结
- TP复习12
- IDHttp的基本用法（转）
- http连接
- PHP 之 Laravel 框架安装及相关开源软件
- 《A First Course in Probability》-chaper7-期望的性质-相关系数
- 深入理解C/C++数组和指针
- 致网友Wonderfei的一封信（怎样选择自己主动化框架的几点拙见)
- 开发时候常用的js方法封装
- ko数组
- codeforces-4
- C#线程同步--限量使用
- vue 学习笔记—Es6
- shiro简单学习的简单总结
- Java参数验证Bean Validation 框架