Column: table fields A, B, C, D
1 1 2 55
1 1 2 232
2, 2, 3, 656
2, 3, 5, 845
2, 3, 5, 840


Want is the structure of the A, B, C, D
1 1 2 55
1 1 2 232
2, 3, 5, 845
2, 3, 5, 840

CodePudding user response:

 
SELECT * FROM TABLE A 
WHERE the EXISTS (SELECT 1 FROM the TABLE WHERE A.A=A AND a.=B AND A.C=C AND a. d. & lt;> D) 

CodePudding user response:

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
[A] the Create table # T (int, int [B], [C] int, int) [D] 
Insert # T 
Select 1,1,2,55 union all 
Select 1,1,2,232 union all 
Select 2,2,3,656 union all 
Select 2,3,5,845 union all 
Select 2,3,5,840 
Go 
- the end of the test data 
SELECT a. * 
The FROM # T a 
The JOIN # T b 
ON b.A=a.A 
AND b.B=a. 
AND the biggest=a.C 
AND b.D & lt;> A. d.; 

CodePudding user response:

D is the free field don't do

CodePudding user response:

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
[A] the Create table # T (int, int [B], [C] int, int) [D] 
Insert # T 
Select 1,1,2,55 union all 
Select 1,1,2,232 union all 
Select 2,2,3,656 union all 
Select 2,3,5,845 union all 
Select 2,3,5,840 
Go 
- the end of the test data 
SELECT a. * 
The FROM # T a 
The JOIN (SELECT *, ROW_NUMBER () OVER (PARTITION BY A, B ORDER BY GETDATE ()) rn FROM # T) B 
ON b.A=a.A 
AND b.B=a. 
AND the biggest=a.C 
AND b.r n> 1; 

Results the same

CodePudding user response:

ROW_NUMBER () OVER (PARTITION BY A, B is excuse me this what consciousness

CodePudding user response:

references A great chicken 5 floor response:

ROW_NUMBER () OVER (PARTITION BY A, B is excuse me this what consciousness

Generated according to A, B group, each group on the list, to judge the same A \ B, A total of how much data, the above wrote A should be less PARTITION BY A, B, C

CodePudding user response:

- test data
If not object_id (N 'Tempdb for.. # T ') is null
Drop table # T
Go
[A] the Create table # T (int, int [B], [C] int, int) [D]
Insert # T
Select 1,1,2,55 union all
Select 1,1,2,232 union all
Select 2,2,3,656 union all
Select 2,3,5,845 union all
Select 2,3,5,840

Select * from # T a where the exists (
Select * from # T where A=a.A and B=a. and C=a.C and (D<> A. d.)
)