计算每个组返回的记录数

I have a general query such as:

我有一个一般性的查询，例如:

SELECT col1, col2, col3, col4
FROM Table1 AS t1
GROUP BY col1, col2, col3, col4

It will return a table something similar to:

它将返回类似于:

col1   col2   col3   col4    
data1  data2  data3  data4
data1  data2  data3  data5
data10 data11 data12 data13
data10 data11 data12 data14
data10 data11 data12 data15

I need to get the count for col1 to display data1 is returned twice and data10 is returned three times. The table should look like:

我需要获取col1的计数，以显示两次返回data1，三次返回data10。该表应如下:

col1   col2   col3   col4   Count  
data1  data2  data3  data4  2
data1  data2  data3  data5  2
data10 data11 data12 data13 3
data10 data11 data12 data14 3
data10 data11 data12 data15 3

I tried count(*) in the select statement but that doesn't work.

我在select语句中尝试了count(*)，但它不起作用。

1 个解决方案

#1

You can use the OVER clause (SQL Server 2008+):

您可以使用OVER子句(SQL Server 2008+):

SELECT col1, col2, col3, col4, COUNT(*) OVER(PARTITION BY col1) [Count]
FROM Table1 AS t1

The results are:

结果是:

╔════════╦════════╦════════╦════════╦═══════╗
║  COL1  ║  COL2  ║  COL3  ║  COL4  ║ COUNT ║
╠════════╬════════╬════════╬════════╬═══════╣
║ data1  ║ data2  ║ data3  ║ data4  ║     2 ║
║ data1  ║ data2  ║ data3  ║ data5  ║     2 ║
║ data10 ║ data11 ║ data12 ║ data13 ║     3 ║
║ data10 ║ data11 ║ data12 ║ data14 ║     3 ║
║ data10 ║ data11 ║ data12 ║ data15 ║     3 ║
╚════════╩════════╩════════╩════════╩═══════╝

Here is a sqlfiddle with a demo of this.

这是一个演示的sqlfiddle。

UPDATE If you need to filter based on the COUNT column, you can use a derived table or a CTE:

如果需要根据COUNT列进行筛选，可以使用派生表或CTE:

Derived Table:

派生表:

SELECT *
FROM (  SELECT col1, col2, col3, col4, COUNT(*) OVER(PARTITION BY col1) [Count]
        FROM Table1) A
WHERE [Count] > 2

CTE:

;WITH CTE AS
(
    SELECT col1, col2, col3, col4, COUNT(*) OVER(PARTITION BY col1) [Count]
    FROM Table1
)
SELECT *
FROM CTE 
WHERE [Count] > 2

Results:

结果:

╔════════╦════════╦════════╦════════╦═══════╗
║  COL1  ║  COL2  ║  COL3  ║  COL4  ║ COUNT ║
╠════════╬════════╬════════╬════════╬═══════╣
║ data10 ║ data11 ║ data12 ║ data13 ║     3 ║
║ data10 ║ data11 ║ data12 ║ data14 ║     3 ║
║ data10 ║ data11 ║ data12 ║ data15 ║     3 ║
╚════════╩════════╩════════╩════════╩═══════╝

sqlfiddle with this option.

sqlfiddle这个选项。

#1