列： 表欄位 A , B, C, D
1 1 2 55
1 1 2 232
2 2 3 656
2 3 5 845
2 3 5 840
想要的結構 是 A,B,C,D
1 1 2 55
1 1 2 232
2 3 5 845
2 3 5 840
uj5u.com熱心網友回復：
SELECT * FROM TABLE A
WHERE EXISTS (SELECT 1 FROM TABLE WHERE A.A=A AND A.B=B AND A.C=C AND A.D<>D)
uj5u.com熱心網友回復：
--測驗資料
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([A] int,[B] int,[C] int,[D] int)
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
--測驗資料結束
SELECT a.*
FROM #T a
JOIN #T b
ON b.A = a.A
AND b.B = a.B
AND b.C = a.C
AND b.D <> a.D;
uj5u.com熱心網友回復：
D 欄位 有空 不做比較呢uj5u.com熱心網友回復：
--測驗資料
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([A] int,[B] int,[C] int,[D] int)
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
--測驗資料結束
SELECT a.*
FROM #T a
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.B
AND b.C = a.C
AND b.rn>1;
結果一樣
uj5u.com熱心網友回復：
ROW_NUMBER()OVER(PARTITION BY A,B 請問這個是啥意識uj5u.com熱心網友回復：
按照A,B分組，生成每組的自增列，來判斷同樣A\B的共多少資料，上邊那個少寫了一個應該是PARTITION BY A,B,C
uj5u.com熱心網友回復：
--測驗資料if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([A] int,[B] int,[C] int,[D] int)
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 exists(
select * from #T where A=a.A and B=a.B and C=a.C and(D<>a.D)
)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/146831.html
標籤：疑難問題