【mysql】复杂语句的分析过程

在项目中看到前辈写的sql，尝试去理解
公司项目需要保密 就不透露表的结构和数据了，大家看思路就好

select  ci.car_number,
            ci.brand_model,
            ci.authority_oilwear,stat.staticMonth,ifnull(stat.total_mileage,0) as totalMileage, ifnull(stat.oilMass,0) as oilMass,
            ifnull(round(stat.oilMass/stat.total_mileage*100,2),0) as avgOilMass
            from t_dmv_car_info ci
        join
        ( select mT.staticMonth,curt.total_mileage,ft.oilMass from (
				SELECT 1 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 2 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 3 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 4 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 5 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 6 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 7 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 8 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 9 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 10 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 11 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 12 AS staticMonth FROM DUAL ) mT
            left join
            (
                SELECT f.car_id, DATE_FORMAT( f.business_date, '%m' ) as staticMonth, sum( fd.oil_mass ) AS oilMass
                FROM t_dmv_fee f
                    LEFT JOIN t_dmv_fee_detail fd ON fd.fee_id = f.id AND fd.fee_type = 1
                WHERE f.fee_category = 3  AND DATE_FORMAT( f.business_date, '%Y' ) ='2022' and f.car_id = ''
                group by DATE_FORMAT( f.business_date, '%m' )
            ) ft on ft.staticMonth = mT.staticMonth
            left join
            (  SELECT cur.car_id,DATE_FORMAT( cur.begin_time, '%m' ) as staticMonth,sum( cur.total_mileage ) AS total_mileage
                    FROM t_dmv_car_use_record cur
                    WHERE DATE_FORMAT( cur.begin_time, '%Y' ) = '2022' and cur.car_id = ''
                    GROUP BY DATE_FORMAT( cur.begin_time, '%m' )
                ) curt on curt.staticMonth = mT.staticMonth
        ) stat where ci.id = ''
        order by stat.staticMonth
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

乍一看很吓人，确实很吓人，但是慢慢看还是可以捋清楚思路的

第一步：明确需要的字段分别来做哪些表

查询某一年中每个月的 耗油量，行驶公里数，以及一些基础字段
最后字段：车牌号、官方油耗、品牌车型、月份、出车公里数、加油量、平均油耗

其中：
车辆信息表 car_info 车牌号、官方油耗、品牌车型
car_number\authority_oilwear\brand_model\

车辆使用记录表 car_use_record 出车公里数
total_mileage,

费用详情表 fee_detail 加油量
oil_mass

平均油耗= 加油量/ 出车公里数

第二步：从每个表中拿到对应的数据

由于是与月份相关的，所以从每个表中拿到数据后 构建成新表

1.补充知识点：构建一年中各个月份表

可能有更好的方法

SELECT 1 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 2 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 3 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 4 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 5 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 6 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 7 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 8 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 9 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 10 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 11 AS staticMonth FROM DUAL
		UNION ALL
		SELECT 12 AS staticMonth FROM DUAL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

上方sql获得月份表，临时表名为 mT

2.拿到费用表中的数据 作为新表ft

费用表和费用详情表join
根据年份和车辆id，查找车辆id，月份，月份总耗油量
结果根据月份进行分组 结果为新表 ft

SELECT f.car_id,
 DATE_FORMAT( f.business_date, '%m' ) as staticMonth, 
 sum( fd.oil_mass ) AS oilMass
FROM t_dmv_fee f
LEFT JOIN t_dmv_fee_detail fd ON fd.fee_id = f.id AND fd.fee_type = 1
WHERE f.fee_category = 3  AND DATE_FORMAT( f.business_date, '%Y' ) ='2022' and f.car_id = ''
group by DATE_FORMAT( f.business_date, '%m' )
1
2
3
4
5
6
7

3.拿到车辆使用记录表中的数据 作为新表curt

车辆使用表中 根据年份和车辆id 根据开始时间月份分组 查询 车辆id，开始时间的月份，
统计月份总公里数 结果作为新表 curt

SELECT cur.car_id,
DATE_FORMAT( cur.begin_time, '%m' ) as staticMonth,
sum( cur.total_mileage ) AS total_mileage
FROM t_dmv_car_use_record cur
WHERE DATE_FORMAT( cur.begin_time, '%Y' ) = '2022' and cur.car_id = ''
GROUP BY DATE_FORMAT( cur.begin_time, '%m' )
1
2
3
4
5
6

4.以上三个表 根据staticMonth 字段left join

查询结果作为新表 stat

select mT.staticMonth,curt.total_mileage,ft.oilMass from mt
left join ft on ft.staticMonth = mT.staticMonth
left join curt on curt.staticMonth = mT.staticMonth
1
2
3

5.关联stat和car_info表 获取最后的结果

select  ci.car_number,
            ci.brand_model,
            ci.authority_oilwear,stat.staticMonth,
            ifnull(stat.total_mileage,0) as totalMileage, ifnull(stat.oilMass,0) as oilMass,
            ifnull(round(stat.oilMass/stat.total_mileage*100,2),0) as avgOilMass
            from t_dmv_car_info ci
        join stat
where ci.id = ''
1
2
3
4
5
6
7
8

总结

根据sql去捋清楚思路不难，但是要我自己写还是很困难的，多看多练才行
select sql的本质就是查出满足条件的数据，所有要先明确需要哪些数据，从哪个表中找，需要与什么关联。