mysql collect_MySQL的一些sql语句（持续更新）

有些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.