In the table, duplicate values may be included. That's not a problem, but sometimes you might want to just list different values (distinct). Keyword DISTINCT is used to return only different values.
Table A:
Table B:
1. Acting on a single row
Select distinct name from A
The following results are performed:
2. Acting on multi-column Example 2.1
Select DISTINCT name, ID from A
The following results are performed:
In fact, it is the same way that access and SQL Server support it, depending on the name and ID two fields.
Example 2.2
Select distinct Xing, Ming from B
Returns the following results:
The returned result is two lines, indicating that distinct is not the Xing and Ming two columns "string concatenation" after the heavy, but respectively for the Xing and Ming columns.
3.COUNT Statistics
Select COUNT (distinct name) from A; --the number of name to go back in the table, SQL Server support, and access does not support
Count is not able to count multiple fields, and the following SQL cannot be run in SQL Server and access.
Select COUNT (distinct name, id) from A;
To use it, use nested queries, as follows:
Select COUNT (*) from (select Distinct Xing, name from B) as M;
4.distinct must be placed at the beginning
Select ID, distinct name from A; --The error will be indicated because the distinct must be placed at the beginning
5. Other
The field selected in the distinct statement can only be a field specified by distinct, and other fields are not possible. For example, if Table A has a memo column, if you want to get Distinc name and the corresponding Memo field, it is not possible to go directly through distinct. However, there are other ways to implement a discussion of how SQL Server will stitch up multiple lines of content into one row
Usage of distinct in SQL