- 1、遇到的问题
- 2、思路(sql语言的执行)
- 2.1、不能给left join的子查询传参
- 2.2、gruop 后,无法组内排序,也无法选择指定的数据展示
- 2.2.1、记录
- 2.2.2、结论
- 2.3、where,group,having的执行顺序
- 3、解决方案
1、遇到的问题
开发中别的部门需要我整理一份sql给他们用。
写sql时,一下几点要求是主要难点
- 必须是一条sql带出多个表的数据
- 评论表的数据通过user_id和用户表关联,一个user_id会对应多行评论表数据,一行评论表只对应一个user_id。需要所有的user数据,以及user的最新评论的评论内容。
第二点要求不是简单的使用max聚合函数就能解决的。我们先看看表结构。
表结构很简单,就是常见的用户和评论。
max函数只能获得数据分组后,分组中某个字段的最大值。这个函数可以获取user_id对应的最大的id,或者最大的创建时间,但是不能获取到这些id(或者创建时间)所在行行数据(比如评论内容)
下面是我一开始想到的解决思路。
2、思路(sql语言的执行)
2.1、不能给left join的子查询传参
子查询中是可以传外部的参数的,比如经常使用到的exists的子查询很多时候都会接受外部传来的参数。
一次性查多个表的话,left join 就很容易做到,令
select ···
from t_user user
left join t_comment comment on user.id = user.id
只是comment和user.id不是一一对应的关系,一个user_id会带出多个comment,所以不能直接连接t_comment。
不能直接连接t_comment,那我就连接过滤后的t_comment。过滤的操作就是子查询。下面我们默认t_comment表的id越大,评论就越新。
select user.name, comment.comment lastestComment
from t_user user
left join (
select * from t_comment innerComment
where innerComment.id = (
select Max(id)
from t_comment doubleInnerComment
where doubleInnerComment.user_id = user.id
group by doubleInnerComment.user_id
)
) comment on comment.user_id = user.id;
left join的子查询中,我希望过滤掉其它的评论,只保留user_id对应的id最大的(也就是评论最新的)的评论。那t_user表和子查询关联的话,就能通过user.id关联到唯一的t_comment行数据。
问题就出现在子查询的过滤。为了得到id最大的评论,在最里面的子查询中,我需要将外部的user.id作为参数传进来。上面这段代码执行以后会报这样的错误。
Error Code: 1054. Unknown column 'user.id' in 'where clause'
这个user.id就是指最里面的子查询的user.id(因为将子查询删除之后,不会报错),left join 的子查询无法像其他子查询一样传参。
2.2、gruop 后,无法组内排序,也无法选择指定的数据展示
2.2.1、记录
还是上面的思路,子查询过滤。
我想让子查询结果集group by user_id,尝试着选择每个组中id最大的那一行为最终结果。
Mysql中group的最后结果是系统默认的,比如说对于下图左边的的数据,经下方的sql后会变成右边的数据
select * from t_comment group by user_id
能看出来,group后有三组,Mysql都是取每组中的第一行数据作为最终的结果集。
我尝试着选择每组中id最大的数据为最终结果,使用having语句,但是失败了,因为having是对分组之后的数据进行条件过滤,不是分组前。
还是以上面的那数据集为例。我们执行下面的sql
SELECT * FROM t_comment group by user_id having id = 3;
如果having是对分组前的数据进行筛选,那至少能查出id = 3的评论,但实际上查出来的结果是空。因为having是对分组完成后的数据进行筛选,而分组完成后,没有id=3的数据,所以结果为空。
之后我希望通过order by字段来规定排序,我误以为group by是通过列表的顺序,选择小组的第一个来展示。但是这个尝试也是错误地,因为order by也是对分组后的数据排序,而不是分组前。
执行下列代码
SELECT * FROM t_comment group by user_id order by id desc;
查出来的结果如下
2.2.2、结论
没有任何手段能控制group by的最终选择(至少我没找到),以后写sql直接规避这条路
2.3、where,group,having的执行顺序
其实执行顺序从sql的语句上就能看出来。
上面说having和order都是对分组后的数据操作,从语句上看,他们都是在group之后。所以是先分组,后进行这两个操作。如果想把having和order放在group之前,那会有语法错误。
SELECT * FROM t_comment group by user_id having id = 3;
SELECT * FROM t_comment group by user_id order by id desc;
同样的道理,语句写在前的,先执行,这个准则也适用于where group等。
比如下面的语句
SELECT * FROM t_comment where id > 2 group by user_id;
这里再贴一下SELECT * FROM t_comment group by user_id;
的结果集
如果group先执行,然后再根据where筛选,那结果就应该只有后两条数据。
如果是先进行where筛选,再执行分组,那结果就应该有三条数据,且第一条数据是id=3的数据,后两条不变。执行结果如下,证明了是先where,后group。
3、解决方案
理解了sql的执行语句之后,写出了下面的代码。
select user.id, user.name, comment.id commentId, comment.comment lastestComment
from t_user user
left join (
select * from t_comment innerComment
where id = (
select Max(id)
from t_comment doubleInnerComment
where doubleInnerComment.user_id = innerComment.user_id
group by user_id
)
) comment on comment.user_id = user.id;
最终结果如下,成功。
感谢提供的更高效的一种方法,子查询只用嵌套一层。
SELECT
user.id,
user.name,
comment.id as commentId,
comment.comment
FROM
t_user
USER LEFT JOIN t_comment COMMENT ON USER.id = COMMENT.user_id
WHERE
COMMENT.id IN ( SELECT max( id ) FROM t_comment GROUP BY user_id );