MySQL使用Left join多表查询同时使用group by order by

时间: 2016-07-11 18:57 栏目: MySQL 浏览: 45051 赞: 73 踩: 19 字体:

以下为本篇文章全部内容:

今天给客户解决写一个功能,遇到一个问题,两个表进行left join查询的时候同时使用了group by 和 order by失效的问题,group by 比order by先执行,order by不会对group by 内部进行排序,如果group by后只有一条记录,那么order by 将无效。因此我们的排序就没有任何用处了。

先上表结构

关联的子表:

mysql> desc sl_predestine_price;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| predestine | int(11)       | YES  |     | 0       |                |
| price      | decimal(12,2) | YES  |     | 0.00    |                |
| addtime    | int(11)       | YES  |     | 0       |                |
| quantity   | int(11)       | YES  |     | 0       |                |
| cycle      | varchar(255)  | YES  |     | NULL    |                |
| member     | int(11)       | YES  |     | 0       |                |
| history    | int(11)       | YES  |     | 0       |                |
| status     | int(11)       | YES  |     | 0       |                |
+------------+---------------+------+-----+---------+----------------+

关联的主表:

mysql> desc sl_predestine;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| good_id | int(11) | YES  |     | 0       |                |
| type    | int(11) | YES  |     | 0       |                |
| good    | text    | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+

我要获取sl_predestine_price表里面所有的数据,并且获取sl_predestine表的good产品数据,并且根据子表里面member字段找出所属用户的数据。一开始我执行的命令是

执行下面命令前


我们先来看看正常的数据,由于命令行看,有些数据显示不全,我就用工具来显示了。这个是正常执行没有进行筛选的数据,我们仔细查看predestine字段和id,我们都是要取最大的。,可执行下面命令之后。

执行下面语句之后

SELECT * FROM liantong.sl_predestine_price where member=1;

1.jpg

select price.*,predestine.good from sl_predestine_price as price left join sl_predestine as predestine on price.predestine=predestine.id where price.member=1 group by price.predestine order by price.id


2.jpg

这里的值明显不是我想要的,我想要的是id和predestine都是要最大的。导致这样问题的原因是因为order by不会在group里面进行分组,所以分组之后去掉了想要的数据,因为group by比order by先执行。

select price.*,predestine.good from sl_predestine_price as price left join sl_predestine as predestine on price.predestine=predestine.id join (select max(id) as lastId from sl_predestine_price where member=1 group by predestine order by id desc) temp on temp.lastId=price.id

3.jpg

执行这个之后就对了,分组和排序都进行了。下面解释一下上面语句的意思,我是先查询符合条件的出来并且分组和排序出来,再进行链表查询。

select price.*,predestine.good from sl_predestine_price as price left join sl_predestine as predestine on price.predestine=predestine.id

这条语句进行了正常的left join 查询

join (select max(id) as lastId from sl_predestine_price where member=1 group by predestine order by id desc) temp on temp.lastId=price.id

这条语句是创建一个临时表,来进行查询,在这个临时表里面就是我们已经分组好和排序好的数据,只要和外面的表关联起来即可。在写SQL的时候可能遇到的坑会比较多。稍微不注意就会遇到坑了,这个还是自己基础要好点,我已经准备去复习SQL了。