选择唯一和选择不同的区别。

I thought these were synonomous, but I wrote the following in Microsoft SQL:

我以为这些都是同步的，但我用Microsoft SQL写了如下内容:

Select Unique col from 
     (select col from table1 union select col from table2) alias

And it failed. Changing it to

它失败了。改变它

Select Distinct col from 
     (select col from table1 union select col from table2) alias

fixed it. Can someone explain?

固定它。谁能解释一下吗?

5 个解决方案

#1

SELECT UNIQUE is old syntax supported by Oracle's flavor of SQL. It is synonymous with SELECT DISTINCT.

SELECT UNIQUE是由Oracle的SQL风格支持的旧语法。它是SELECT DISTINCT的同义词。

Use SELECT DISTINCT because this is standard SQL, and SELECT UNIQUE is non-standard, and in database brands other than Oracle, SELECT UNIQUE may not be recognized at all.

之所以使用SELECT DISTINCT，是因为这是标准SQL，而SELECT UNIQUE不是标准的，而且在Oracle以外的数据库品牌中，SELECT UNIQUE可能根本不被识别。

#2

Unique is a keyword used in the Create Table() directive to denote that a field will contain unique data, usually used for natural keys, foreign keys etc.

Unique是Create Table()指令中使用的关键字，用来表示字段将包含唯一数据，通常用于自然键、外键等。

For example:

例如:

Create Table Employee(   
    Emp_PKey Int Identity(1, 1) Constraint PK_Employee_Emp_PKey Primary Key,  
    Emp_SSN Numeric Not Null Unique,  
    Emp_FName varchar(16),   
    Emp_LName varchar(16) 
)

i.e. Someone's Social Security Number would likely be a unique field in your table, but not necessarily the primary key.

也就是说，某人的社保号可能是表中的唯一字段，但不一定是主键。

Distinct is used in the Select statement to notify the query that you only want the unique items returned when a field holds data that may not be unique.

Select语句中使用了Distinct，以通知查询，当字段包含可能不是唯一的数据时，只需要返回惟一项。

Select Distinct Emp_LName
From Employee

You may have many employees with the same last name, but you only want each different last name.

您可能有许多员工的姓相同，但您只希望每个员工的姓不同。

Obviously if the field you are querying holds unique data, then the Distinct keyword becomes superfluous.

显然，如果正在查询的字段包含唯一的数据，那么不同的关键字将变得多余。

#3

select unique is not valid syntax for what you are trying to do

选择unique不是您要做的事情的有效语法

you want to use either select distinct or select distinctrow

您希望使用选择distinct或选择distinctrow

And actually, you don't even need distinct/distinctrow in what you are trying to do. You can eliminate duplicates by choosing the appropriate union statement parameters.

实际上，你甚至不需要在你所要做的事情中有明显的区别。您可以通过选择适当的union语句参数来消除重复。

the below query by itself will only provide distinct values

下面的查询本身只提供不同的值

select col from table1 
union 
select col from table2

if you did want duplicates you would have to do

如果你真的想要复制，你就得这么做

select col from table1 
union all
select col from table2

#4

Only In Oracle =>

SELECT DISTINCT and SELECT UNIQUE behave the same way. While DISTINCT is ANSI SQL standard, UNIQUE is an Oracle specific statement.

选择DISTINCT并选择UNIQUE。虽然不同的是ANSI SQL标准，唯一的是Oracle特定语句。

In other databases (like sql-server in your case) =>

SELECT UNIQUE is invalid syntax. UNIQUE is keyword for adding unique constraint on the column.

选择UNIQUE是无效语法。UNIQUE是在列中添加唯一约束的关键字。

SELECT DISTINCT

选择不同的

#5

1. Unique was the old syntax while Distinct is the new syntax,which is now the Standard sql.
2. 惟一的是旧语法，惟一的是新语法，现在是标准sql。
3. Unique creates a constraint that all values to be inserted must be different from the others. An error can be witnessed if one tries to enter a duplicate value. Distinct results in the removal of the duplicate rows while retrieving data.
4. Unique创建一个约束，所有要插入的值都必须与其他值不同。如果一个人试图输入一个重复的值，就可以看到一个错误。在检索数据时删除重复行的不同结果。

5. Example: SELECT DISTINCT names FROM student ;

   示例:从学生中选择不同的名称;

   CREATE TABLE Persons ( Id varchar NOT NULL UNIQUE, Name varchar(20) );

   创建表person (Id varchar NOT NULL UNIQUE, Name varchar(20));