表A是一个缓慢变化维度,记录了用户在不同时期所在的城市,现在需要把换过城市的用户,城市列出,怎么实现。
ID City FromDate ToDate
001 BJ 2003/03/01 2008/01/01
001 SH 2008/01/02
002 BJ 2003/03/01 2008/01/01
002 BJ 2008/01/02 结果
001 BJ
001 SH
ID City FromDate ToDate
001 BJ 2003/03/01 2008/01/01
001 SH 2008/01/02
002 BJ 2003/03/01 2008/01/01
002 BJ 2008/01/02 结果
001 BJ
001 SH
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);
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;
from a
where row_num >1;
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;
8楼要简单一些
where id in (select id from 表A group by id,city having count(1)=1);