Usage of distinct in SQL (four sample analyses) _mssql

When using MySQL, there are times when you need to query for records that are not duplicated in a field, although MySQL provides a distinct keyword to filter out excess duplicates, but it often only uses it to return the number of records that are not duplicates, rather than using it to return all values that are not logged. The reason for this is that distinct can only return its target field. And can not return to other fields, this problem let me troubled for a long time, with distinct can not be solved, I only use the double loop query to solve, and so for a very large amount of data station, will undoubtedly directly affect the efficiency, So I wasted a lot of time.

In a table, you might include duplicate values. This is not a problem, but sometimes you may want to list only the different values (distinct). Keyword DISTINCT is used to return only a different value.

Table A:

Example 1

Copy Code code as follows:

Select distinct name from A

The results of the implementation are as follows:

Example 2

Copy Code code as follows:

Select DISTINCT name, ID from A

The results of the implementation are as follows:

In fact, it is based on "Name+id" , and distinct is also on the name and ID , which is supported both by Access and SQL Server .

Example 3: Statistics

Copy Code code as follows:

Select COUNT (distinct name) from A; --The number of name resets in the table, SQL Server support, and access does not support
Select COUNT (distinct name, id) from A; Both--sql server and access do not support

Example 4

Copy Code code as follows:

Select ID, distinct name from A; -the error is prompted because the distinct must be placed at the beginning

Other

The field in the distinct statement that the select displays can only be fields specified by distinct, and other fields are not available. For example, if Table A has a memo column, it is impossible to get distinc name and the corresponding "Memo" field directly through distinct.

However, there are other ways to discuss the issue of how SQL Server can concatenate a column of rows into one row