一个简单的问题没答上来，被鄙视了

[rottenapple]

表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

[tangren]

select id,city from 表A group by id,city having count(1)>1;

[Phoenix_99]

select id,city from 表A group by id,city having count(1)>1;

[iqlife]

没有什么的,正常的,什么都精通的不叫人,叫神........

[tangren]

上面有误，这个

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);

[tangren]

--或者这个
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;

[duqiangcise]

select id,city,row_number()over(parition by city order by id) row_num
from a
where row_num >1;

[jmcai520]

[Quote=引用 4 楼 tangren 的回复:]
上面有误，这个

SQL code
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'……
[/Quote]
师兄你这个也整的太复杂了把！

[tangren]

--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;

[tangren]

[Quote=引用 7 楼 jmcai520 的回复:]
引用 4 楼 tangren 的回复:
上面有误，这个

SQL code
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
……
[/Quote]
with部分是数据，
8楼要简单一些

[paopaoma1]

我只是来接分的~！

[bingdian1900]

select id,city from 表A
where id in （select id from 表A group by id,city having count(1)=1);

[rottenapple]

xiexie，结贴了