🎈个人公众号:🎈 :✨✨ 可为编程 ✨✨ 🍟🍟
🔑个人信条:🔑 知足知不足 有为有不为 为与不为皆为可为🌵
🍉本篇简介:🍉 本片详细说明了MYSQL的GROUPBY使用规则和注意要点,并给出具体操作实例,如有出入还望指正。关注公众号【可为编程】回复【面试】领取最新面试题!!!
场景描述如下:
订单表
(包含字段有:订单ID[自增]、客户ID、下单时间、订单金额、商品名称),
采用一个SQL语句,查询出:
每一位客户最后一次的下单时间、订单金额、商品名称;以及每一位客户的累计订单总笔数、最大订单金额
看起来很简单哈,有的同学就会这样去写了,如下:
SELECT
MAX( good_price ) AS max_good_price,
COUNT( DISTINCT order_id, order_id ) AS sum_order_num,
order_person_id,
MAX( create_time ) AS create_time,
good_price,
order_name AS good_name
FROM
`t_order_info`
GROUP BY
order_person_id
在数据很少的时候,通过这种就很容易实现了,并且每一个用户当时只有一条对应的信息,也就是order_person_id所对应的商品信息只有互不相同的一条信息。
很显然这样是不合理的,当出现多条数据的时候就会产生对应的一个错误,那就是会取到其他的数据,显示的order_person_id为3,但却不是3的其他数据字段。所以当我们去查询的时候 发现数值不对。
SELECT * FROM `t_order_info` WHERE order_person_id = 5 ORDER BY create_time DESC;
然后继续查看是那种原因呢?
首先我们一定要明白,GROUPBY是每次只取表格的第一条数据,比如按照order_person_id进行分类,这样就会每次遇到相同的order_person_id时候,就会取第一个职,这样就出现了上面所展示的结果。
关注公众号【可为编程】回复【面试】领取最新面试题!!!
所以首先我们可以先查出来每个下单人所对应的最新下单时间以及下单人order_person_id编号,这样我们在拿着这组编号再去联查原始表INNER JOIN,将我们的时间最大值和其他相关字段查出来,同时要按照下单人ID进行分组,这样查出来的数据存在重复的订单数据,所以我们再嵌套一层GROUPBY order_person_id,这样就会按照含有重复数据集合中的默认id顺序进行分组并从该顺序中逐一取order_person_id的第一个值,然后生成新得集合。
对比两次GROUPBY查询:
SELECT
toi.*
FROM
( SELECT order_person_id, max( create_time ) time FROM t_order_info GROUP BY order_person_id ) tmp
INNER JOIN t_order_info toi ON tmp.order_person_id = toi.order_person_id
AND tmp.time = toi.create_time
ORDER BY order_person_id DESC
根据order_person_id进行排序查询,结果如下:
1723 小卡车 3 3
1724 小卡车 4 4
1572 小卡车-AB 5 5
1708 小卡车 6 6
1717 小卡车 6 6
1709 小卡车 7 7
1718 小卡车 7 7
1710 小卡车 8 8
1719 小卡车 8 8
1702 小卡车 9 9
1711 小卡车 9 9
1720 小卡车 9 9
1703 小卡车 10 10
1712 小卡车 10 10
1721 小卡车 10 10
1722 小卡车 11 11
SELECT
toi.*
FROM
( SELECT order_person_id, max( create_time ) time FROM t_order_info GROUP BY order_person_id ) tmp
INNER JOIN t_order_info toi ON tmp.order_person_id = toi.order_person_id
AND tmp.time = toi.create_time
默认GROUPBY 分组ID排序
1572 小卡车-AB 5 5 7
1702 小卡车 9 9 7
1703 小卡车 10 10 7
1708 小卡车 6 6 7
1709 小卡车 7 7 7
1710 小卡车 8 8 7
1711 小卡车 9 9 7
1712 小卡车 10 10 7
1717 小卡车 6 6 7
1718 小卡车 7 7 7
1719 小卡车 8 8 7
1720 小卡车 9 9 7
1721 小卡车 10 10 7
1722 小卡车 11 11 7
1723 小卡车 3 3 7
1724 小卡车 4 4 7
正式的SQL例子,采用INNERJOIN:
SELECT *
FROM (
SELECT toi.*
FROM (
SELECT
order_person_id,
max(create_time) TIME
FROM t_order_info
GROUP BY order_person_id
) tmp
INNER JOIN t_order_info toi ON tmp.order_person_id = toi.order_person_id
AND tmp.TIME = toi.create_time
) AS a
GROUP BY order_person_id;
-- GROUP BY 默认取第一个
关注公众号【可为编程】回复【面试】领取最新面试题!!!
第二步进行聚合函数的操作:
SELECT
MAX( good_price ) AS max_good_price,
COUNT( order_id ) AS sum_order_num,
MAX( create_time ) AS max_create_time,
order_person_id
FROM
`t_order_info`
GROUP BY
order_person_id;
最终版本:
最终版:
SELECT
MAX( toi1.good_price ) AS max_good_price,
COUNT( toi1.order_id ) AS sum_order_num,
toi1.order_person_id,
max_create_time,
b.good_price,
b.order_name,
b.order_person_id
FROM
`t_order_info`AS toi1 INNER JOIN (
SELECT
max(create_time) AS max_create_time, good_price,order_name,order_person_id
FROM
(
SELECT
toi.*
FROM
( SELECT order_person_id, max( create_time ) time FROM t_order_info GROUP BY order_person_id ) tmp
INNER JOIN t_order_info toi ON tmp.order_person_id = toi.order_person_id
AND tmp.time = toi.create_time
) AS a
GROUP BY
order_person_id
) AS b ON toi1.order_person_id = b.order_person_id
GROUP BY
b.order_person_id
关注公众号【可为编程】回复【面试】领取最新面试题!!!
还可以根据最新的时间进行分组,不采用INNERJOIN的形式。
SELECT * FROM (
SELECT
order_id,
order_person_id,
good_price,
order_name AS good_name,
create_time
FROM
`t_order_info`
WHERE create_time = (
SELECT
MAX( create_time ))
GROUP BY
order_person_id,
(SELECT MAX( create_time ))
ORDER BY
create_time DESC
) AS a GROUP BY
order_person_id
最终版本:
最终版:
SELECT
MAX( toi.good_price ) AS max_good_price,
COUNT( toi.order_id ) AS sum_order_num,
MAX( toi.create_time ) AS max_create_time,
toii.good_price,
toii.good_name,
toii.order_person_id
FROM
`t_order_info` AS toi
INNER JOIN (
SELECT
*
FROM
( SELECT order_person_id, good_price, order_name AS good_name, create_time FROM `t_order_info` GROUP BY order_person_id, ( SELECT MAX( create_time )) ORDER BY create_time DESC ) AS a
GROUP BY
order_person_id
) AS toii ON toi.order_person_id = toii.order_person_id
GROUP BY
toii.order_person_id
以下是其他排查语句,也先列在这里。
SELECT max(create_time), order_person_id FROM t_order_info GROUP BY order_person_id;
SELECT max(create_time), good_price,order_name,order_person_id FROM t_order_info GROUP BY order_person_id;
同时这里面里一个终极版本的写法:
select
temp.order_person_id 客户ID,
max(temp.create_time) 最后一次的下单时间,
temp.good_price 最后一次的下单金额,
temp.order_name 最后一次的下单商品,
count(*) 客户的累计订单总笔数,
max(good_price) 最大订单金额
from (select * from t_order_info order by create_time desc, order_id desc limit 9999999999) as temp
group by temp.order_person_id;
这是我的直属领导给我写的语句,多么的简短,美丽大方,留做纪念。
欢迎感兴趣的小伙伴一起探讨学习知识,以上是个人的一些总结分享,如有错误的地方望各位留言指出,十分感谢。觉得有用的话别忘点赞、收藏、关注,手留余香! 😗 😗 😗
这里是一个真诚的***青年技术交流QQ群:761374713***,不管你是大学生、社畜、想学习变成的其他人员,欢迎大家加入我们,一起成长,一起进步,真诚的欢迎你,不管是技术,还是人生,还是学习方法。有道无术,术亦可求,有术无道,止于术。
欢迎大家关注【可为编程】,成长,进步,编程,技术、掌握更多知识!