在一张表中,如何找出记录中某些字段相同记录例如有下表
CREATE TABLE test
(
id bigint NOT NULL,
cid bigint,
value bigint,
CONSTRAINT test_pkey PRIMARY KEY (id)
) 有以下记录
id cid value
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 6
7 2 7
8 2 8
9 2 8如何输出8 2 8
9 2 8条件cid value是相同值的记录
CREATE TABLE test
(
id bigint NOT NULL,
cid bigint,
value bigint,
CONSTRAINT test_pkey PRIMARY KEY (id)
) 有以下记录
id cid value
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 6
7 2 7
8 2 8
9 2 8如何输出8 2 8
9 2 8条件cid value是相同值的记录
(
id bigint NOT NULL,
cid bigint,
`value` bigint,
CONSTRAINT test_pkey PRIMARY KEY (id)
);
insert into test values
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 1, 5),
(6, 2, 6),
(7, 2, 7),
(8, 2, 8),
(9, 2, 8);
select a.* from test a, test b where a.cid = b.cid and a.`value` = b.`value` group by a.id having count(a.id) > 1;==========
query result(2 records)
id cid value
8 2 8
9 2 8
FROM test a, test b
WHERE a.cid = b.cid
AND a.`value` = b.`value`
AND a.id <> b.id
ORDER BY id
from test A
inner join (select cid,value from test group by cid,value having count(*) > 1) B on A.cid=B.cid and A.value=B.value
二楼是因为ORDER BY id的问题,改为ORDER BY a.id也OK啦
select A.*
from test A,
(
select cid,value
from test
group by cid,value
having count(*) > 1
) B
where A.cid=B.cid and A.value=B.value
我个人觉得这样更直观些