有一表如下ch jxdm jxnr
w0a001 610100 aaabbbb
sod008 740200 bbbcccc
s2c047 850100 ccceeee
woa001 610200 aaacccc
s0d008 740300 bbbeeee
s2c047 850300 cccdddd
w0a001 720200 bbbffff
s0d008 650100 aaahhhh
s2c047 910100 eeerrrr
........
........当ch相同,jxdm前三位相同(6位取3位),当ch相同,jxdm前三位不同的不要
得到如下新表
ch jxdm jxnr
w0a001 610100 aaabbbb
w0a001 610200 aaacccc
s0d008 740200 bbbcccc
s0d008 740300 bbbeeee
s2c047 850100 ccceeee
s2c047 850300 cccdddd
........
........
w0a001 610100 aaabbbb
sod008 740200 bbbcccc
s2c047 850100 ccceeee
woa001 610200 aaacccc
s0d008 740300 bbbeeee
s2c047 850300 cccdddd
w0a001 720200 bbbffff
s0d008 650100 aaahhhh
s2c047 910100 eeerrrr
........
........当ch相同,jxdm前三位相同(6位取3位),当ch相同,jxdm前三位不同的不要
得到如下新表
ch jxdm jxnr
w0a001 610100 aaabbbb
w0a001 610200 aaacccc
s0d008 740200 bbbcccc
s0d008 740300 bbbeeee
s2c047 850100 ccceeee
s2c047 850300 cccdddd
........
........
当ch相同,jxdm前三位不同,不要?
是这个意思?
(select ch , left(jxdm,3) jxdm from tb group by ch , left(jxdm,3) having count(*) > 1)
insert into tb values('w0a001', '610100', 'aaabbbb')
insert into tb values('sod008', '740200', 'bbbcccc')
insert into tb values('s2c047', '850100', 'ccceeee')
insert into tb values('woa001', '610200', 'aaacccc')
insert into tb values('s0d008', '740300', 'bbbeeee')
insert into tb values('s2c047', '850300', 'cccdddd')
insert into tb values('w0a001', '720200', 'bbbffff')
insert into tb values('s0d008', '650100', 'aaahhhh')
insert into tb values('s2c047', '910100', 'eeerrrr')
goselect m.* from tb m where exists
(select ch , left(jxdm,3) jxdm from tb where ch = m.ch and left(jxdm,3) = left(m.jxdm,3) group by ch , left(jxdm,3) having count(*) > 1)
order by m.ch , m.jxdmdrop table tb/*
ch jxdm jxnr
---------- ---------- ----------
s2c047 850100 ccceeee
s2c047 850300 cccdddd
(所影响的行数为 2 行)
*/
insert into @ts select 'w0a001','610100','aaabbbb'
union all select 's0d008','740200','bbbcccc'
union all select 's2c047','850100','ccceeee'
union all select 'w0a001','610200','aaacccc'
union all select 's0d008','740300','bbbeeee'
union all select 's2c047','850300','cccdddd'
union all select 'w0a001','720200','bbbffff'
union all select 's0d008','650100','aaahhhh'
union all select 's2c047','910100','eeerrrr'select * from @ts a,
(select ch , left(jxdm,3)jxdm from @ts
group by ch,left(jxdm,3) having count(1)>1)b
where a.ch=b.ch and left(a.jxdm,3)=b.jxdm
看看行不?
//Results:
s0d008 650100 aaahhhh
s0d008 740200 bbbcccc
s0d008 740300 bbbeeee
s2c047 850100 ccceeee
s2c047 850300 cccdddd
s2c047 910100 eeerrrr
w0a001 610100 aaabbbb
w0a001 610200 aaacccc
w0a001 720200 bbbffff
insert into #tb values('w0a001', '610100', 'aaabbbb')
insert into #tb values('s0d008', '740200', 'bbbcccc')
insert into #tb values('s2c047', '850100', 'ccceeee')
insert into #tb values('w0a001', '610200', 'aaacccc')
insert into #tb values('s0d008', '740300', 'bbbeeee')
insert into #tb values('s2c047', '850300', 'cccdddd')
insert into #tb values('w0a001', '720200', 'bbbffff')
insert into #tb values('s0d008', '650100', 'aaahhhh')
insert into #tb values('s2c047', '910100', 'eeerrrr')
goselect a.* from
#tb as a
inner join
(
select ch,left(jxdm,3) as jxdm from #tb
group by ch,left(jxdm,3) having count(*)>1
)as b on a.ch=b.ch and left(a.jxdm,3)=b.jxdm
order by a.ch,a.jxdmdrop table #tb--------------------------
s0d008 740200 bbbcccc
s0d008 740300 bbbeeee
s2c047 850100 ccceeee
s2c047 850300 cccdddd
w0a001 610100 aaabbbb
w0a001 610200 aaacccc
http://topic.csdn.net/u/20080227/14/8bce0844-bd15-42f0-9cda-a343d5d6601b.html?seed=2111206245
Select distinct M.ch,M.jxdm,M.jxnr From Test M Inner Join Test N On M.Ch=N.Ch And Left(M.Jxdm,3)=Left(N.Jxdm,3) And M.Jxdm<>N.Jxdm运行结果:
s0d008 740200 bbbcccc
s0d008 740300 bbbeeee
s2c047 850100 ccceeee
s2c047 850300 cccdddd
w0a001 610100 aaabbbb
w0a001 610200 aaacccc
insert into test_a(ch, jxdm, jxnr)
select
'w0a001', '610100' , 'aaabbbb' union select
'sod008', '740200', 'bbbcccc' union select
's2c047', '850100', 'ccceeee' union select
'woa001', '610200', 'aaacccc' union select
's0d008', '740300', 'bbbeeee' union select
's2c047', '850300', 'cccdddd' union select
'w0a001', '720200', 'bbbffff' union select
's0d008', '650100', 'aaahhhh' union select
's2c047', '910100', 'eeerrrr'
select * from test_a as a where exists(select * from test_a as b where
a.ch=b.ch and left(a.jxdm,3)=left(b.jxdm,3) group by b.ch, left(b.jxdm,3) having count(*)>1 )
drop table test_a