SELECT TEST.*
FROM TEST
JOIN (SELECT b1, COUNT(*)
FROM TEST
WHERE a1 = '1'
GROUP BY b1
HAVING COUNT(*) > 1) testb ON TEST.b1 = testb.b1
JOIN (SELECT c1, COUNT(*)
FROM TEST
WHERE a1 = '1'
GROUP BY c1
HAVING COUNT(*) = 1) testc ON TEST.c1 = testc.c1
WHERE TEST.a1 = '1'
FROM TEST
JOIN (SELECT b1, COUNT(*)
FROM TEST
WHERE a1 = '1'
GROUP BY b1
HAVING COUNT(*) > 1) testb ON TEST.b1 = testb.b1
JOIN (SELECT c1, COUNT(*)
FROM TEST
WHERE a1 = '1'
GROUP BY c1
HAVING COUNT(*) = 1) testc ON TEST.c1 = testc.c1
WHERE TEST.a1 = '1'
WHERE EXISTS (SELECT 1 FROM TEST B
WHERE A.A1 = B.A1 AND A.B1 = B.B1
GROUP BY A1,B1 HAVING COUNT(*) > 1 AND COUNT(DISTINCT C1) >1))
from test,
(
select a1,b1
from test
group by a1,b1
having count(c1)>1
) temp
where test.a1=temp.a1 and test.b1=temp.b1 and test.a1=1
3 b1值相同的两条记录中,若C1的值也相同,则不要
select a1,b1,c1
from test,
(
select a1,b1
from test
group by a1,b1
having count(c1)>1
) temp
where test.a1=temp.a1 and test.b1=temp.b1 and test.a1=1
group by a1,b1,c1
having count(*)=1