SQL 查看某个项目是否在数据库列中出现多次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9736944/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
See whether an item appears more than once in a database column
提问by CallumVass
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
salesidis the column I wish to check for, any help would be appreciated, thanks.
salesid是我想检查的专栏，任何帮助将不胜感激，谢谢。
回答by Solomon Rutzky
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.)
- As this is a GROUP BY operation, a HAVING clause will filter it instead of a WHERE
- 您不能执行 SELECT * 因为此操作需要 GROUP BY 并且列需要位于 GROUP BY 或聚合函数中（即 COUNT、SUM、MIN、MAX、AVG 等）
- 由于这是一个 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:
编辑2：
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
回答by Mark J. Bobak
How about:
怎么样：
select salesid from AXDelNotesNoTracking group by salesid having count(*) > 1;
回答by LConrad
To expand on Solomon Rutzky's answer, if you are looking for a piece of data that shows up in a range (i.e. more than once but less than 5x), you can use
为了扩展 Solomon Rutzky 的答案，如果您正在寻找显示在某个范围内（即不止一次但小于 5 倍）的数据，您可以使用
having count(*) > 1 and count(*) < 5
And you can use whatever qualifiers you desire in there - they don't have to match, it's all just included in the 'having' statement. https://webcheatsheet.com/sql/interactive_sql_tutorial/sql_having.php
您可以在其中使用您想要的任何限定词——它们不必匹配，它们都包含在“拥有”语句中。 https://webcheatsheet.com/sql/interactive_sql_tutorial/sql_have.php
回答by Ali Issa
try this:
尝试这个：
select salesid,count (salesid) from AXDelNotesNoTracking group by salesid having count (salesid) >1
