Count - SQL Server Management Studio 2017

Question

我有以下查询，它带来了 71965 行(包括重复项(。

SELECT
(REPLACE([DATE],'-','') + '_' + [Championship] + '_' + [Home_Team] + '_Vs_'+ [Away_Team]) 
FROM
[Soccer_Database].[dbo].[Ext_Match_Details]

通过下面的查询，我试图Count每个值出现的次数(以查找重复项(。

SELECT
(REPLACE([     DATE],'-','') + '_' + [Championship] + '_' + [Home_Team] + '_Vs_'+ [Away_Team]) ,
COUNT(REPLACE([DATE],'-','') + '_' + [Championship] + '_' + [Home_Team] + '_Vs_'+ [Away_Team]) AS COUNT
FROM
    [Soccer_Database].[dbo].[Ext_Match_Details]
GROUP BY
    (REPLACE([DATE],'-','') + '_' + [Championship] + '_' + [Home_Team] + '_Vs_'+ [Away_Team])

但是我得到 71958(没有重复项的行数(，并且在所有行中Count列都是 1。有什么帮助吗？

Answer 1

SELECT (REPLACE([DATE],'-','') + '_' + [Championship] + '_' + [Home_Team] + '_Vs_'+ [Away_Team]) as [Value] 
            , count(1) as [Count]
    FROM [Soccer_Database].[dbo].[Ext_Match_Details]
    group by (REPLACE([DATE],'-','') + '_' + [Championship] + '_' + [Home_Team] + '_Vs_'+ [Away_Team])

Answer 2

添加到末尾：

HAVING COUNT(*) > 1以过滤具有多行的组(= 重复项(。
如果要查看所有行，请改为
添加
ORDER BY [COUNT] DESC将重复项放在顶部。这是有效的，因为并非所有 COUNT 都是 1 - 原始查询的行数和您解释的GROUP BY存在差异。