查看项目是否在数据库列中出现多次

I want to check if a piece of data appears more than once in a particular column in my table using SQL. Here is my SQL code of what I have so far:

我想检查一块数据是否在使用SQL的表中的特定列中出现多次。这是我到目前为止的SQL代码：

select * from AXDelNotesNoTracking where count(salesid) > 1

salesid is the column I wish to check for, any help would be appreciated, thanks.

salesid是我想检查的专栏，任何帮助将不胜感激，谢谢。

3 个解决方案

#1

It should be:

它应该是：

SELECT SalesID, COUNT(*)
FROM AXDelNotesNoTracking
GROUP BY SalesID
HAVING COUNT(*) > 1

Regarding your initial query:

关于您的初始查询：

You cannot do a SELECT * since this operation requires a GROUP BY and columns need to either be in the GROUP BY or in an aggregate function (i.e. COUNT, SUM, MIN, MAX, AVG, etc.)
您不能执行SELECT *，因为此操作需要GROUP BY，并且列需要位于GROUP BY或聚合函数中（即COUNT，SUM，MIN，MAX，AVG等）
As this is a GROUP BY operation, a HAVING clause will filter it instead of a WHERE
由于这是GROUP BY操作，因此HAVING子句将过滤它而不是WHERE

Edit:

编辑：

And I just thought of this, if you want to see WHICH items are in there more than once (but this depends on which database you are using):

我只是想到了这个，如果你想在那里看到多个项目（但这取决于你使用的是哪个数据库）：

;WITH cte AS (
    SELECT  *, ROW_NUMBER() OVER (PARTITION BY SalesID ORDER BY SalesID) AS [Num]
    FROM    AXDelNotesNoTracking
)
SELECT  *
FROM    cte
WHERE   cte.Num > 1

Of course, this just shows the rows that have appeared with the same SalesID but does not show the initial SalesID value that has appeared more than once. Meaning, if a SalesID shows up 3 times, this query will show instances 2 and 3 but not the first instance. Still, it might help depending on why you are looking for multiple SalesID values.

当然，这只显示出现了具有相同SalesID但未显示多次出现的初始SalesID值的行。这意味着，如果SalesID显示3次，则此查询将显示实例2和3，但不显示第一个实例。不过，根据您寻找多个SalesID值的原因，它可能会有所帮助。

Edit2:

EDIT2：

The following query was posted by APC below and is better than the CTE I mention above in that it shows all rows in which a SalesID has appeared more than once. I am including it here for completeness. I merely added an ORDER BY to keep the SalesID values grouped together. The ORDER BY might also help in the CTE above.

以下查询由APC发布，并且比我上面提到的CTE更好，因为它显示了SalesID出现多次的所有行。我把它包括在这里是为了完整。我只是添加了一个ORDER BY来保持SalesID值组合在一起。 ORDER BY也可能有助于上述CTE。

SELECT *
FROM AXDelNotesNoTracking
WHERE SalesID IN
    (     SELECT SalesID
          FROM AXDelNotesNoTracking
          GROUP BY SalesID
          HAVING COUNT(*) > 1
    )
ORDER BY SalesID

#2

How about:

怎么样：

select salesid from AXDelNotesNoTracking group by salesid having count(*) > 1;

#3

try this:

尝试这个：

select salesid,count (salesid) from AXDelNotesNoTracking group by salesid having count (salesid) >1

#1