17,140
社区成员




--5楼这个不对,改一下
with temp as(
select '001' id, 'BJ' city from dual union all
select '001' id, 'SH' city from dual union all
select '002' id, 'BJ' city from dual union all
select '002' id, 'BJ' city from dual union all
select '003' id, 'BJ' city from dual)
SELECT *
FROM temp a
WHERE (SELECT COUNT(1)
FROM temp b
WHERE b.id = a.id AND
b.city <> a.city) > 0;
--或者这个
with temp as(
select '001' id, 'BJ' city from dual union all
select '001' id, 'SH' city from dual union all
select '002' id, 'BJ' city from dual union all
select '002' id, 'BJ' city from dual)
SELECT *
FROM temp a
WHERE (SELECT COUNT(1)
FROM temp b
WHERE b.id = a.id AND
b.city = a.city) < 2;
with temp as(
select '001' id, 'BJ' city from dual union all
select '001' id, 'SH' city from dual union all
select '002' id, 'BJ' city from dual union all
select '002' id, 'BJ' city from dual)
SELECT id, city
FROM temp
WHERE id IN (SELECT id FROM (SELECT DISTINCT id, city FROM temp) GROUP BY id HAVING COUNT(1) > 1);