请教SQL~
TABLE A
id zzh name time
01 2000 hubei 2007-2-1
01 2000 hubei 2007-3-1
07 2000 hebei 2007-4-1
02 2000 henan 2007-2-1
02 2000 henan 2007-3-1
03 3000 shanxi 2008-1-1
04 3000 jiangxi 2009-1-1
04 5000 fujian 2009-1-1
05 7000 guangdong 2009-2-1
==========================
期望查询出id不同，但是 zzh 相同的纪录
TABLE A
id zzh name time
01 2000 hubei 2007-2-1
01 2000 hubei 2007-3-1
02 2000 henan 2007-2-1
02 2000 henan 2007-3-1
03 3000 shanxi 2008-1-1
04 3000 jiangxi 2009-1-1
[解决办法]
--这样?
--SQL2000
create table A(id nvarchar(10), zzh int, [name] nvarchar(10), [time] datetime)
insert A select '01 ', 2000, 'hubei ', '2007-2-1 '
union all select '01 ', 2000, 'hubei ', '2007-3-1 '
union all select '07 ', 2000, 'hebei ', '2007-4-1 '
union all select '02 ', 2000, 'henan ', '2007-2-1 '
union all select '02 ', 2000, 'henan ', '2007-3-1 '
union all select '03 ', 3000, 'shanxi ', '2008-1-1 '
union all select '04 ', 3000, 'jiangxi ', '2009-1-1 '
union all select '04 ', 5000, 'fujian ', '2009-1-1 '
union all select '05 ', 7000, 'guangdong ', '2009-2-1 '
select * from A as tmp
where exists(select 1 from A where id <> tmp.id and zzh=tmp.zzh)
