单个查询打印所有计数大于 10 的行 [英] single query to print all the rows whose count is greater than 10

问题描述

我有一个名为 Table1 的表，其定义如下.

I have a table named Table1 whose definition is as below.

Id  int False   
Source  nvarchar(MAX)   True    
Dest    nvarchar(MAX)   True    
Port    nvarchar(MAX)   True    
DgmLen  nvarchar(MAX)   True    
Flags   nvarchar(MAX)   True    
Payload nvarchar(MAX)   True    

现在我想打印这个表中源"计数大于 10 的所有行.

Now I want to print all the rows of this table whose "source" count is greater than 10.

首先我使用这个查询来获取表中源的数量:

Firstly I have used this query to fetch the count of sources in the table:

Select Source,count(*) t_count from Table1 group by Source

并且它已经获取了以下数据:

and it has has fetched the following data:

Source            t_count
2-170.125.32.3  1
2-172.125.32.10 1
2-190.125.32.10 11
2-190.125.32.3  1
2-192.125.32.10 1
2-192.125.32.3  6

现在我想打印所有具有Source = 2-190.125.32.10"的行，因为其 t_count 大于 10.

Now I want to print all the rows having "Source = 2-190.125.32.10" as its t_count is greater than 10.

如何在单个查询中编写此内容.

推荐答案

如果我猜对了，那么 :-

If I got you correctly, then :-

select * from Table1 where Source in
(
Select Source from Table1 group by Source having count(*) > 10
)

这将返回 Table1 中 Source 列值出现超过 10 次的所有行.

This return all those rows from Table1 who have the Source column value appearing more than 10 times.

-

select * from Table1 t1 join
(Select Source, Dest from Table1 group by Source, Dest having count(*) > 10) t2
on t1.Source = t2.Source and t1.Dest = t2.Dest

这里，表 t2 返回出现 10 次以上的 Source, Dest 组合，并与基表 Table1 连接.

Here, the table t2 returns combination of Source, Dest appearing more than 10 times and joins it with the base table Table1.

这篇关于单个查询打印所有计数大于 10 的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！