使用联合和子查询获取前5条记录 [英] getting top 5 records using union and subqueries

问题描述

要获得前3名评论以及2个喜欢的博客(共5条记录)，我正在使用此查询

 选择 顶部 3 * 来自(选择 顶部  10000  NEWID()nid，blogid，COUNT(*)从计数 Blog_Comment 其中 isAdmin = 0 和 isActive = 1 组 作者 BlogId 顺序  by 计算 desc )b
 联盟
选择 顶部  2  * 来自(顶部 来自算起不 喜欢 '  MS％'  group   by  BlogId  by 计算
工作得很好..但是想得到重复的blogids .. 

 nid Blogid计数
E3128722-1C3C-4DFF-B099-080FA587562E  129  11
C6C55763-F395-4332-8E39-5443E3D81DA2  129  2
58072BB7-B957-4725-A3AB-6529A4931FB4 141 7
8B46BC4A-6A41-46B8-ACCC-8722D5713D46 124 2
7CE73B0C-573C-43A4-9620-F452E6D5435B 140 7 
 

我如何在这里获得唯一的Blogid ...请帮助

谢谢
解决方案
 
如果我正确地解释了您的SQL，

-您有两个单独的sql语句

-第一个返回3行

-第二个返回2行

-两个查询都是独立的(基于SQL语法)

-两个查询的结果都通过并集合并.



因此，这意味着在第一个查询中没有条件会从第二个查询中排除博客ID，反之亦然.这意味着解决方案是针对数据的.



现在，不知道这些表之间的关系，我只是在猜测，但也许您可以将查询修改为以下内容:

 选择 顶部  3  *
来自(选择博客ID，
             COUNT(*)个计数
      来自 Blog_Comment
      其中 isAdmin = 0
      和 isActive = 1
      组  by  BlogId
      排序  by 计数 desc )b
联盟
选择 顶部  2  *
来自(选择 BlogId，
              SUM(Likecount)计数
      来自 Blog_Like
      其中 PartnerId 不 喜欢 '  MS％'
      和 BlogId 不是  in (选择 顶部  3 
                                Blogid，
                                COUNT(*)个计数
                         来自 Blog_Comment
                         其中 isAdmin = 0
                         和 isActive = 1
                         组  by  BlogId
                         顺序 依据计算为 desc )
      组  by  BlogId
      顺序  by 计算 desc ) class ="code-keyword">顺序 按计数
 

该查询很可能可以简化很多，但是这需要一些有关数据模型的知识.
 
 
To get top 3 commented as well as 2 Liked blogs (total 5 records), i am using this query

 

select Top 3* from (select Top 10000 NEWID() nid,blogid,COUNT(*) counts from Blog_Comment where isAdmin=0 and isActive=1    group by BlogId order by counts desc ) b
 union
select top 2 * from (select  Top 10000 NEWID() nid,BlogId,SUM(Likecount) counts from Blog_Like where PartnerId not like 'MS%'  group by BlogId order by counts desc ) a  order by nid


working nice.. but getting duplicate blogids for suppose..

nid	                               blogid	counts
E3128722-1C3C-4DFF-B099-080FA587562E	129	11
C6C55763-F395-4332-8E39-5443E3D81DA2	129	2
58072BB7-B957-4725-A3AB-6529A4931FB4	141	7
8B46BC4A-6A41-46B8-ACCC-8722D5713D46	124	2
7CE73B0C-573C-43A4-9620-F452E6D5435B	140	7


how can i get here unique blogids...Please help

Thanks
解决方案
If I''m interpreting you SQL correctly:

- you have two separate sql statements

- first one returns 3 rows

- second one returns 2 rows

- both queries are independent (based on SQL syntax)

- the results from both queries are combined by union.



So this means that there is no condition in the first query that would exclude the blog id from the second query or vice versa. This means that the solution is data specific.



Now without knowing the relations between those tables, I''m just guessing but perhaps you could modify the query to something like:

select Top 3 *
from (select blogid,
             COUNT(*) counts
      from Blog_Comment
      where isAdmin=0
      and isActive=1
      group by BlogId
      order by counts desc ) b
union
select top 2 *
from (select  BlogId,
              SUM(Likecount) counts
      from Blog_Like
      where PartnerId not like 'MS%'
      and BlogId not in (select top 3
                                blogid,
                                COUNT(*) counts
                         from Blog_Comment
                         where isAdmin=0
                         and isActive=1
                         group by BlogId
                         order by counts desc)
      group by BlogId
      order by counts desc ) a  order by counts


Most likely the query could by simplified a lot but that would require some knowledge about the data model.


                        
这篇关于使用联合和子查询获取前5条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！