A question about DISTINCT. Some people often ask questions similar to this. The data in the table is as follows: idaabb201734561453554160656416065625066426063898920.00002. expected results are that some people often ask questions similar to this,
The data in the table is as follows:
ID AA BB
1 123 456
1 4535 54
1 60 6564
1 60 656
2 50 664
2 60 6
3 89
4 40 4242
The expected result is:
ID AA BB
1 123 456
2 50 664
3 89
4 40 4242
Of course, the environment is SQL Server
The answer and analysis are as follows:
1. many friends want to solve the problem with distinct, but it is impossible. disctinct ignores repeated records,
However, it ignores completely consistent record duplication, rather than the record where a field already exists, so only
Such syntax
Select distinct ID, AA, BB from tName
Others such as select distinct (ID), AA, BB from tName or
The statement of select ID, distinct AA, and BB is invalid.
2. use group by and aggregate functions
Select ID, MAX (AA) as aa, MAX (BB) as bb from tName group by ID
The following result is displayed:
ID AA BB
1 4535 6564
2 60 664
3 89
4 40 4242
The ID is unique, but not necessarily the fields that follow the same record
3. use a temporary table
Select IDENTITY (INT, 1, 1) as TID, ID, AA, BB into # Tmp from tName
Select t1.ID, t1.AA, t1.BB from # Tmp t1 where t1.TID in
(Select min (T2.TID) from # Tmp t2 group by t2.ID)
In this way, the expected results are displayed.
But I used two T-SQL statements,
In addition, if the data volume is large, the performance problem will be very prominent.
So far, I have not found a way to implement the same functionality with a T-SQL statement,
If anyone has one
Success, the data in the table is as follows: id aa bb 1 123 456 1 4535 54 1 60 6564 1 60 656 2 664 2 60 6 3 89 4 40 4242 expected result is...
