Database environment: SQL SERVER 2005
The data for the existing table is as follows, the ID is the primary key, and P1,P2 is the string type, if the value of the P1,p2 field of the current row equals another row
The value of the field P2,P1, the 2 rows are recorded as a group. For example, id=1 and id=5 belong to the same set of data. The same set of data shows only the smallest ID
The row record, no group of data is displayed.
Implementation ideas:
The table is self-correlating leftist, assuming that the alias of the table is a, B, associated with the ID, and the associated result set is filtered. If the b.id is empty, it is reserved,
If b.ID is not empty, only records with a.id smaller than b.id are retained.
Implementation of SQL script:
/*1. Data Preparation*/ withx0 as(SELECT 1 asID,'A' asP1,'B' asP2/*UNION all SELECT 0 as ID, ' A ' as P1, ' B ' as P2*/ UNION All SELECT 2 asID,'C' asP1,'D' asP2UNION All SELECT 3 asID,'E' asP1,'F' asP2UNION All SELECT 4 asID,'D' asP1,'C' asP2UNION All SELECT 5 asID,'B' asP1,'A' asP2UNION All SELECT 6 asID,'H' asP1,'J' asP2UNION All SELECT 7 asID,'T' asP1,'U' asP2UNION All SELECT 8 asID,'J' asP1,'H' asP2/*UNION all SELECT 9 as ID, ' I ' as P1, ' L ' as P2 UNION all SELECT as ID, ' J ' as P1, ' K ' as p2*/ ),/*2. Go to the heavy*/X1 as(SELECTID, p1, p2 from(SELECTID, p1, p2, Row_number () Over(PARTITION byP1, p2ORDER byID) asRN fromx0) TWHERERn= 1 ) /*3. Evaluation*/ SELECTa.id, A.P1, A.p2 fromX1 a Left JOINX1 B onB.p1=A.P2 andB.p2=A.P1WHEREb.ID is NULL ORa.ID<b.ID
View Code
The effect of the final implementation
There are also netizens proposed by ASCII to achieve, his implementation of SQL script is as follows:
withC1 as(SELECT 1 asID,'A' asP1,'B' asP2/*UNION all SELECT 0 as ID, ' A ' as P1, ' B ' as P2*/ UNION All SELECT 2 asID,'C' asP1,'D' asP2UNION All SELECT 3 asID,'E' asP1,'F' asP2UNION All SELECT 4 asID,'D' asP1,'C' asP2UNION All SELECT 5 asID,'B' asP1,'A' asP2UNION All SELECT 6 asID,'H' asP1,'J' asP2UNION All SELECT 7 asID,'T' asP1,'U' asP2UNION All SELECT 8 asID,'J' asP1,'H' asP2/*UNION all SELECT 9 as ID, ' I ' as P1, ' L ' as P2 UNION all SELECT as ID, ' J ' as P1, ' K ' as P2*/), C2 as(SELECT MIN(ID) asmin_id fromC1GROUP by ASCII(p1)+ ASCII(p2))SELECTC1.* fromC1JOINC2 onId=min_id
View Code
At first glance, it seems that the same needs can be achieved. In fact, there are 2 problems with this notation:
1. If P1,P2 is more than one character, the ASCII method will only take the first character of the ASCII
2.ASCII (' A ') +ascii (' D ') =ascii (' B ') +ascii (' C '), for such data, cannot be distinguished in ASCII way
(End of this article)
Self-correlating removes duplicate data from a group