mysql 查询结果并集 悟饭救我

[qq_34354426]

想要上面那个查询只返回三条数据，下面那个按时间倒叙排列并且有分页limit 0,10 最后合并起来

[「已注销」]

悟饭救我

[「已注销」]

SELECT * FROM cms_content_comments LEFT JOIN ( SELECT parent_id , COUNT(1) AS likecount FROM cms_content_comments WHERE news_id = 1 AND like_it = 1 GROUP BY parent_id ) t ON cms_content_comments.id = t.parent_id WHERE comment <> '' AND comment IS NOT NULL ORDER BY t.likecount DESC , UNION all SELECT * FROM cms_content_comments LEFT JOIN ( SELECT parent_id , COUNT(1) AS likecount FROM cms_content_comments WHERE news_id = 1 AND like_it = 1 GROUP BY parent_id ) t ON cms_content_comments.id = t.parent_id WHERE comment <> '' AND comment IS NOT NULL ORDER BY comment_time desc　

[二月十六]

mysql不太熟悉……你试试这样行吗？

SELECT * FROM (
SELECT TOP 3 *
FROM    cms_content_comments
        LEFT JOIN ( SELECT  parent_id ,
                            COUNT(1) AS likecount
                    FROM    cms_content_comments
                    WHERE   news_id = 1
                            AND like_it = 1
                    GROUP BY parent_id
                  ) t ON cms_content_comments.id = t.parent_id
WHERE   comment <> ''
        AND comment IS NOT NULL
ORDER BY t.likecount DESC )t1
  
UNION all
SELECT * FROM (        
SELECT  *
FROM    cms_content_comments
        LEFT JOIN ( SELECT  parent_id ,
                            COUNT(1) AS likecount
                    FROM    cms_content_comments
                    WHERE   news_id = 1
                            AND like_it = 1
                    GROUP BY parent_id
                  ) t ON cms_content_comments.id = t.parent_id
WHERE   comment <> ''
        AND comment IS NOT NULL
ORDER BY comment_time desc　
limit 0,10)t2

另外我这个是悟空

[「已注销」]

引用 3 楼 sinat_28984567 的回复:

mysql不太熟悉……你试试这样行吗？

SELECT * FROM (
SELECT TOP 3 *
FROM    cms_content_comments
        LEFT JOIN ( SELECT  parent_id ,
                            COUNT(1) AS likecount
                    FROM    cms_content_comments
                    WHERE   news_id = 1
                            AND like_it = 1
                    GROUP BY parent_id
                  ) t ON cms_content_comments.id = t.parent_id
WHERE   comment <> ''
        AND comment IS NOT NULL
ORDER BY t.likecount DESC )t1
  
UNION all
SELECT * FROM (        
SELECT  *
FROM    cms_content_comments
        LEFT JOIN ( SELECT  parent_id ,
                            COUNT(1) AS likecount
                    FROM    cms_content_comments
                    WHERE   news_id = 1
                            AND like_it = 1
                    GROUP BY parent_id
                  ) t ON cms_content_comments.id = t.parent_id
WHERE   comment <> ''
        AND comment IS NOT NULL
ORDER BY comment_time desc　
limit 0,10)t2

另外我这个是悟空

[二月十六]

奥MySQL 没有top …，把上边那个top 3去掉在order by 后边加limit 3试试

[「已注销」]

引用 5 楼 sinat_28984567 的回复:

奥MySQL 没有top …，把上边那个top 3去掉在order by 后边加limit 3试试

小心琪琪

[「已注销」]

其实就是基于你昨天的查询，想实现一个，按点赞排序前三条的结果，三条以后就按照时间排序。

[「已注销」]

引用 5 楼 sinat_28984567 的回复:

奥MySQL 没有top …，把上边那个top 3去掉在order by 后边加limit 3试试

目前就想到了一个并集数据的方法，悟空可有啥好方法伐

[二月十六]

select t1.* from (
SELECT  cms_content_comments.*,t.likecount
FROM    cms_content_comments
        LEFT JOIN ( SELECT  parent_id ,
                            COUNT(1) AS likecount
                    FROM    cms_content_comments
                    WHERE   news_id = 1
                            AND like_it = 1
                    GROUP BY parent_id
                  ) t ON cms_content_comments.id = t.parent_id
WHERE   comment <> ''
        AND comment IS NOT NULL
ORDER BY t.likecount DESC limit 3) as  t1
     
union 
select * from (
SELECT  cms_content_comments.*,t.likecount
FROM    cms_content_comments
        LEFT JOIN ( SELECT  parent_id ,
                            COUNT(1) AS likecount
                    FROM    cms_content_comments
                    WHERE   news_id = 1
                            AND like_it = 1
                    GROUP BY parent_id
                  ) t ON cms_content_comments.id = t.parent_id
WHERE   comment <> ''
        AND comment IS NOT NULL
ORDER BY comment_time limit 0,10)t2

        

[「已注销」]

引用 9 楼 sinat_28984567 的回复:

select t1.* from (
SELECT  cms_content_comments.*,t.likecount
FROM    cms_content_comments
        LEFT JOIN ( SELECT  parent_id ,
                            COUNT(1) AS likecount
                    FROM    cms_content_comments
                    WHERE   news_id = 1
                            AND like_it = 1
                    GROUP BY parent_id
                  ) t ON cms_content_comments.id = t.parent_id
WHERE   comment <> ''
        AND comment IS NOT NULL
ORDER BY t.likecount DESC limit 3) as  t1
     
union 
select * from (
SELECT  cms_content_comments.*,t.likecount
FROM    cms_content_comments
        LEFT JOIN ( SELECT  parent_id ,
                            COUNT(1) AS likecount
                    FROM    cms_content_comments
                    WHERE   news_id = 1
                            AND like_it = 1
                    GROUP BY parent_id
                  ) t ON cms_content_comments.id = t.parent_id
WHERE   comment <> ''
        AND comment IS NOT NULL
ORDER BY comment_time limit 0,10)t2

        

版主大大好厉害啊，~~~~~崇拜~~~~~~~~