SQL distinct handling multi-column issues

When you do the ETL work for SSIS today, one of the left join components will run with more records. Analyzed, the reason for this problem is that the column of data in the right table is duplicated. The run policy of the left join can be understood to compare the associated fields of the right table against the associated fields of each record in left table, and duplicate records will be generated if the associated fields of the right table are duplicated. If the left table is duplicated and the right table is not duplicated, there will be no more records. For example, if the data for the left table A and right table B are as follows

Span style= "FONT-SIZE:14PX;" >a table

id	Span style= "FONT-SIZE:14PX;" >name
1	Zhang San
2	John Doe
3	Harry
4	Wanglu

Span style= "FONT-SIZE:14PX;" >b table

id	Span style= "FONT-SIZE:14PX;" >description
1	Span style= "FONT-SIZE:14PX;" > inline
1	students will
2	outer Union
3	Youth League

If the ID is used as the associated field with a LEFT JOIN B table, the result will produce 5 records, one more than the right table. (By the way, if the right table does not repeat, the result count of the left join will be the same as that of the Open table)

Table A LEFT join B results

Id	Name	Description
1	Tom	Inner Joint Department
1	Tom	Department of Students
2	John doe	Outreach Department
3	Harry	Communist youth league
4	Wanglu	Null

In fact, I want the result to correspond to the left table a one by one, do not have duplicate records. This can be achieved through the lookup component of SSIS, but it is inefficient. So I thought of the duplicate records in the right table to get rid of the join two tables. First of all, naturally think of using the distinct function to weigh

SELECT DISTINCT ID, Description  from      B

The result is 1 records are not removed, because distinct is in multi-column, that is, must be ID and description all the same will be rejected.

After searching the internet, it was said that using select *, COUNT (distinct name) from the table group by name such statements are feasible, but I interviewed in SQL Server will be an error. Had to do their own hands, clothed, think about it, in fact, you can use the following statement

SELECT    ID, (SELECTMax(Description)　　　　
From  -as   Descriptionfrom      B
GROUP  by Id

Further thinking found that SQL Server has First_value and Last_value functions, can also implement

SELECT    DISTINCT ID,    First_value   (PARTITION by
　　　　　　　　　　　　　　　　　ORDER  by  as Description  from    B

In the second method, the parameter of partition by must be adjusted by the parameters of Id,order by, which makes the method more flexible. These two methods are measured by the efficiency of the same, the first kind of a little bit faster. Unfortunately, the second method is not supported in SSIS, only with the first group by approach.

SQL distinct handling multi-column issues