SQL 查询以确定列中的值是唯一的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26199765/
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
SQL query to determine that values in column is unique
提问by HymanyBoi
How to write a query to just determine that a column values are unique?
如何编写查询来确定列值是唯一的？
回答by Ganesh_Devlekar
Try this:
尝试这个：
SELECT CASE WHEN count(distinct col1)= count(col1)
THEN 'column values are unique' ELSE 'column values are NOT unique' END
FROM tbl_name;
Note: This only works if 'col1' does not have the data type 'ntext' or 'text'. If you have one of these data types, use 'distinct CAST(col1 AS nvarchar(4000))' (or similar) instead of 'distinct col1'.
注意：这仅在“col1”没有“ntext”或“text”数据类型时才有效。如果您有这些数据类型之一，请使用“distinct CAST(col1 AS nvarchar(4000))”（或类似的）而不是“distinct col1”。
回答by FuzzyTree
select count(distinct column_name), count(column_name)
from table_name;
If the # of unique values is equal to the total # of values, then all values are unique.
如果唯一值的数量等于值的总数，则所有值都是唯一的。
回答by TT.
IF NOT EXISTS (
SELECT
column_name
FROM
your_table
GROUP BY
column_name
HAVING
COUNT(*)>1
)
PRINT 'All are unique'
ELSE
PRINT 'Some are not unique'
If you want to list those that aren't unique, just take the inner query and run it. HTH.
如果您想列出那些不是唯一的，只需使用内部查询并运行它。哈。
回答by Stijn
With this following query, you have the advantage of not only seeing if your columns are unique, but you can also see which combination is most non-unique. Furthermore, because you still see frequency 1 is your key is unique, you know your results are good, and not for example simply missing; something is less clear when using a HAVING clause.
使用以下查询，您不仅可以查看列是否唯一，还可以查看哪个组合最不唯一。此外，因为您仍然看到频率 1 是您的密钥是唯一的，所以您知道您的结果是好的，而不是例如简单地丢失；使用 HAVING 子句时，有些事情不太清楚。
SELECT Col1, Col2, COUNT(*) AS Freq
FROM Table
GROUP BY Col1, Col2
ORDER BY Freq DESC
回答by Gordon Linoff
If you want to check if all the values are unique andyou care about NULLvalues, then do something like this:
如果您想检查所有值是否唯一并且您关心NULL值，请执行以下操作：
select (case when count(distinct column_name) = count(column_name) and
(count(column_name) = count(*) or count(column_name) = count(*) - 1)
then 'All Unique'
else 'Duplicates'
end)
from table t;
回答by Matt Jones
Are you trying to return only distinct values of a column? If so, you can use the DISTINCT keyword. The syntax is:
您是否试图仅返回列的不同值？如果是这样，您可以使用 DISTINCT 关键字。语法是：
SELECT DISTINCT column_name,column_name
FROM table_name;
回答by Ricardo Sanchez
Use the DISTINCT keyword inside a COUNT aggregate function as shown below:
在 COUNT 聚合函数中使用 DISTINCT 关键字，如下所示：
SELECT COUNT(DISTINCT column_name) AS some_alias FROM table_name
The above query will give you the count of distinct values in that column.
上面的查询将为您提供该列中不同值的计数。
