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
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
=========
就一个规律:id不同zhh 相同的。
--SQL2000create 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)
id zzh
01 2000
01 2000
02 2000
02 2000
03 3000
04 3000
05 7000
07 2000
==========================
期望查zzh重复,但是id不重复的 没别的 可能是我自己把结果写错了,惭愧啊~
TABLE A
id zzh
01 2000
01 2000
02 2000
02 2000
03 3000
04 3000
07 2000