有些sql语句第一次写错了,或者想了一下才知道怎么搞
1.查询收藏夹信息并显示收藏夹中有多少个收藏:
原写法:
SELECT A.collect_group_id, A.group_name, COUNT(*) total_num
FROM collect_group A
LEFT JOIN user_collect B
ON A.user_id = B.user_id
WHERE A.user_id = #{arg0}
GROUP BY A.collect_group_id
问题:用的外连接,不管收藏夹有没有收藏数据,没有收藏和收藏了一条数据都显示1
解决:
SELECT A.collect_group_id, A.group_name,
(SELECT COUNT(*) FROM user_collect B WHERE A.collect_group_id = B.collect_group_id) total_num
FROM collect_group A
WHERE A.user_id = #{arg0}
2.查询数据的时候显示这是第几条数据(例:百度贴吧的楼层数)
注意,要把@i:=@i+1放在后面,因为他是建立在数据的基础上的,要先查数据。位置改的话会报错。结果如下:
注意:下方有个小bug——B表必须要有任意一条数据的reply_status = 1,不然,查询结果是空
select *,@i:=@i+1 storey from
(select (select COUNT(*) from reply_info where reply_info.reply_post_id = A.reply_post_id and reply_info.reply_status = 1) num,
A.reply_post_id,A.user_id,A.reply_post_content,A.create_time,
C.user_name,C.user_img_url,C.vip_id
from (select *,@i:=0 from reply_post_info) A,reply_info B,nf_user C
WHERE B.reply_status = 1
AND A.user_id = C.user_id
AND A.reply_status = 1
AND A.post_id=#{param1}
GROUP BY A.reply_post_id
ORDER BY A.create_time) D
过程:
我先是这么写的:
SELECT @i:=@i+1 storey, COUNT(*) num,A.reply_post_id,A.user_id,A.reply_post_content,A.create_time,
C.user_name,C.user_img_url,C.vip_id
FROM (select *,@i:=0 from reply_post_info) A
LEFT JOIN reply_info B ON A.reply_post_id = B.reply_post_id
LEFT JOIN nf_user C ON A.user_id = C.user_id
WHERE A.reply_status = 1 AND C.status = 1 AND B.reply_status = 1 AND A.post_id=#{param1}
GROUP BY A.reply_post_id
ORDER BY A.create_time
后来发现缺陷:1.B表的reply_post_id为空的话,count(*)还是1,和本章第1个sql语句一个问题 2.storey这个楼层列的值不是按某个直观的顺序,例如id或者是插入时间
改成:
select @i:=@i+1 storey,(select COUNT(*) from reply_info where reply_info.reply_post_id = A.reply_post_id and reply_info.reply_status = 1) num,
A.reply_post_id,A.user_id,A.reply_post_content,A.create_time,
C.user_name,C.user_img_url,C.vip_id
from reply_post_info A,reply_info B,nf_user C,(select @i:=0) D
WHERE B.reply_status = 1
AND A.user_id = C.user_id
AND A.reply_status = 1
AND A.post_id=#{param1}
GROUP BY A.reply_post_id
缺陷:排序不是自己控制的,并且直接加上ORDER BY是不行的,因为ORDER BY是对查询结果排序,注意,是结果。那只能把现在查询出来的加上排序当做一张表再查询一次,再修改一下@i:=0的位置就OK了。
3.