按指定年份的月份对5种不同类型的订单数据进行统计问题总结

按指定年份的月份对5种不同类型的订单数据进行统计问题总结

需求:

如下图,需要按左上角切换年份来获取每个月5种不同类型的订单个数,如果没有对应类型的数据,所有月份不管有没有数据,必须要显示,也需要填充为零。

《按指定年份的月份对5种不同类型的订单数据进行统计问题总结》

刚开始写出来的代码是无法满足每个月份都有,因此添加了一个12个月的月份视图作为主表,把月份全部显示出来解决

CREATE VIEW v_months AS
SELECT 1 as month UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12
select month from v_months

《按指定年份的月份对5种不同类型的订单数据进行统计问题总结》

最后的sql语句

      SELECT
        ifnull(o.type,
        0) type,
        count(o.type) num,
        ifnull(sum(o.money),
        0) money,
        m.month  
    FROM
        v_months m
		left join 
        os_order_form o  
            ON m.month = month(o.create_time) 
            AND year(create_time) = '2021-01-01T12:12:05' 
            AND o.tenant_id = 2 
    GROUP BY
        o.type,
        m.month 
    ORDER BY
        m.month;

《按指定年份的月份对5种不同类型的订单数据进行统计问题总结》

这样得到的结果已经满足每个月都有数据了,但是每个月的类型只有一种,还是不满足需求。

解决思路

其实对于每个月份的所有类型都显示出来和解决每个月份都显示出来是一样的思路,再创建一个5种类型的视图,和月份视图左关联,条件设置为1=1,再左关联订单主表进行查询。

创建类型视图

CREATE VIEW v_types AS
SELECT 0 as type UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 

select type from v_types

《按指定年份的月份对5种不同类型的订单数据进行统计问题总结》

最后的sql

select 
	t.type,
	count(o.type) num,
  ifnull(sum(o.money),0) money,
  m.month   
from 
	v_months m
left join 
	v_types t on 1=1 
left join 
  os_order_form o  
ON m.month = month(o.create_time) 
AND year(create_time) = '2021-01-01T12:12:05' 
GROUP BY
	t.type,
	m.month 

查询结果:其实是1-12个月,每个月都有0-4 五种类型(60条数据),由于显示问题,只能显示这么多

《按指定年份的月份对5种不同类型的订单数据进行统计问题总结》

下午又遇到了类似的查询

对于不同类型的订单数量,如果没有也,需要把对应的补充为0

《按指定年份的月份对5种不同类型的订单数据进行统计问题总结》

对于这样的问题,解决思路也和上面一样,要以显示的类型为主表,左连接查询订单表,条件是类型相同

SELECT
        vt.type,
        count(oof.type) num  
    FROM
        v_types vt  
    LEFT JOIN
        os_order_form oof  
            ON vt.type = oof.type 
            AND year(oof.create_time) = year('2021-01-01T12:12:05') 
    GROUP BY
        vt.type;

查询结果如下,对于type为4,没有则num为0

《按指定年份的月份对5种不同类型的订单数据进行统计问题总结》

刚开始调试这个sql的类型数据对不上,是因为没有加上ON vt.type = oof.type 的条件,并且把year(oof.create_time) = year(‘2021-01-01T12:12:05’) 放在了where条件后面。

原始写法

	SELECT
        vt.type,
        count(oof.type) num  
    FROM
        v_types vt  
    LEFT JOIN
        os_order_form oof  
            ON 1=1
            where year(oof.create_time) = year('2021-01-01T12:12:05') 
    GROUP BY
        vt.type;

查询结果:

《按指定年份的月份对5种不同类型的订单数据进行统计问题总结》

后来查资料发现left join使用时,需要对关联表的筛选条件放在on 后面,对主表的筛选条件放在where后面,不应该放在on后面

`sql查询的关键字顺序为  from -> where -> group by-> having ->order by`

`left join 是在from范围的所以先on条件筛选,然后再做left join`

`而where是在left join的结果之后再次筛选`

语句一   :   SELECT * FROM A LEFT JOIN B ON A.id=b.a_id AND A.id <3;

语句一(等价于SELECT *FROM A LEFT JOIN (SELECT * FROM B WHERE B.id < 3) b2 ON A.id = b2.a_id;)

按照on后的条件筛选B.id<3
1中查询出的结果与A left join

语句二:  SELECT * FROM A LEFT JOIN B ON A.id=b.a_id WHERE B.id <3;

语句二
先left join 
然后再根据where条件再次筛选

使用注意事项:

`ON后面的筛选条件主要是针对关联表的(对于主表的筛选条件不适用)`
`对于主表的筛选条件应该放在where后面,不应该放在ON后面`
`如果查询条件查询后才join连接应该把查询条件放在ON后面;如果要连接完毕后才筛选应该把条件放在where后面
`对于关联表可以先做子查询再join`

总结

遇到事情一定要多想一想,特别是同一类型的问题,不能在同一个地方跌倒两次。以后针对数据补全的问题都可以参考这种方式解决,如果有更好的方式到时再补充

    原文作者:DreamsComeTrue1
    原文地址: https://blog.csdn.net/csdnlaiyanqi/article/details/122055277
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞