select * from table where A in (select distinct A from table ) and B in (select distinct B from table)
上面那个写错了,写成了查不重复记录SELECT * FROM TABLE WHERE A IN (SELECT A FROM TABLE GROUP BY A HAVING COUNT(*) > 1) AND B IN (SELECT B FROM TABLE GROUP BY B HAVING COUNT(*) > 1)
select distinct t1.c,t1.b,t1.a from tab3 t1,tab3 t2 where t1.b=t2.b and t1.a=t2.a and t1.c<>t2.c
SELECT * FROM TABLE WHERE A IN (SELECT A FROM TABLE GROUP BY A HAVING COUNT(*) > 1) AND B IN (SELECT B FROM TABLE GROUP BY B HAVING COUNT(*) > 1)这个应该正确的吧!
SELECT TABLE1.a, TABLE1.b, TABLE1.c FROM TABLE1 RIGHT OUTER JOIN (SELECT a, b FROM TABLE1 GROUP BY a, b HAVING COUNT(*) > 1) AS TABLE2 ON TABLE1.a = TABLE2.a AND TABLE1.b = TABLE2.b GROUP BY TABLE1.a, TABLE1.b, TABLE1.c这个可以了!
A字段 B字段 C字段
1 2 3
2 1 4
1 3 5
3 2 6
2 1 7这样的样子>?
FROM TABLE
WHERE A IN
(SELECT A
FROM TABLE
GROUP BY A
HAVING COUNT(*) > 1) AND B IN
(SELECT B
FROM TABLE
GROUP BY B
HAVING COUNT(*) > 1)
from tab3 t1,tab3 t2
where t1.b=t2.b and t1.a=t2.a and t1.c<>t2.c
FROM TABLE
WHERE A IN
(SELECT A
FROM TABLE
GROUP BY A
HAVING COUNT(*) > 1) AND B IN
(SELECT B
FROM TABLE
GROUP BY B
HAVING COUNT(*) > 1)这个应该正确的吧!
可是我试过了,不行!
我描述的详细一些!
有如下的表:
A字段 B字段 C字段
1 2 3 *
1 2 4 *
2 2 5
3 2 6
1 2 7 *
我现在希望把头两条数据和最后一条(加星的)找出来!A字段数据重复且B字段数据也重复的记录,但是C字段数据不同!
FROM TABLE1 RIGHT OUTER JOIN
(SELECT a, b
FROM TABLE1
GROUP BY a, b
HAVING COUNT(*) > 1) AS TABLE2 ON TABLE1.a = TABLE2.a AND
TABLE1.b = TABLE2.b
GROUP BY TABLE1.a, TABLE1.b, TABLE1.c这个可以了!