表TEMP
A B
11 22
11 22
11 33
22 44
22 44
22 55
.......
结果
A B
11 22
11 33
22 44
22 55就是把A列值相同但B列值不同的数据筛选出来,,,
A B
11 22
11 22
11 33
22 44
22 44
22 55
.......
结果
A B
11 22
11 33
22 44
22 55就是把A列值相同但B列值不同的数据筛选出来,,,
Select A, B From [TEMP] Group By A, B這兩條語句都可以得到你要的結果?難道你要的結果不是這樣的?
表TEMP
A B
11 22
11 22
11 33
22 44
22 44
22 55
33 11
33 11
.......
结果还是
A B
11 22
11 33
22 44
22 55
A,B列都相同的就不用显用显示,仅把A列值相同但B列值不同的数据筛选出来。
insert @T
select 'a' , 01,15 union
select 'a', 02,16 union
select 'b', 03,16 --select count(*) from @t
--select * from @T where count(*) >1
select s_name,count(s_name) as count from @T group by s_name having count(s_name)>1
from TEMP
group by A
可能我写的有问题,,
表TEMP
A B
11 22
11 22
11 33
22 44
22 44
22 55
33 11
33 11
.......
结果还是
A B
11 22
11 33
22 44
22 55
A,B列都相同的就不用显用显示,仅把A列值相同但B列值不同的数据筛选出来。
--------------
加上一個條件即可Select A, B From [TEMP] Group By A, B Having Count(*) > 1
可能我的举例有点误导大家,,其实我只要A列值相同B列值却不同的数据筛选出,GROUP BY 光加个条件同样会把A相同B也相同的查出。
----------------這樣就可以啊
Select A, B From [TEMP] Group By A, B Having Count(*) > 1
你測試下看看
我测试过的!结果还是会把A相同B也相同的查出
----------------
考慮不全,改寫下Select Distinct * From [TEMP]
Where A In( Select A From (Select Distinct * From [TEMP]) A Group By A Having Count(A) > 1)
Where A In(Select A From [TEMP] Group By A Having Count(Distinct B) > 1)
(A Int,
B Int)
Insert [TEMP] Select 11, 22
Union All Select 11, 22
Union All Select 11, 33
Union All Select 22, 44
Union All Select 22, 44
Union All Select 22, 55
Union All Select 33, 11
Union All Select 33, 11
GO
Select Distinct * From [TEMP]
Where A In(Select A From [TEMP] Group By A Having Count(Distinct B) > 1)
GO
Drop Table [TEMP]
--Result
/*
A B
11 22
11 33
22 44
22 55
*/