SQL查询以确定列中的值是唯一的

How to write a query to just determine that a column values are unique?

如何编写查询以确定列值是唯一的？

7 个解决方案

#1

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;

#2

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.

如果唯一值的数量等于值的总数，则所有值都是唯一的。

#3

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.

如果要列出那些不唯一的，只需执行内部查询并运行它。 HTH。

#4

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

#5

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;

#6

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.

上面的查询将为您提供该列中不同值的计数。

#7

If you want to check if all the values are unique and you care about NULL values, 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;

#1