sql-server - 统计每组返回的记录数

Question

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

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

它将返回类似于以下内容的表格:

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

我需要获取 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

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

Answer 1

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

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

结果是:

╔════════╦════════╦════════╦════════╦═══════╗
║  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有一个演示。

更新如果需要根据 COUNT 列进行过滤，可以使用派生表或 CTE:

派生表:

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

结果:

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

sqlfiddle with this option .