有表yyy
票号 序号 保管员
A01C200801010001 1 30002
A01C200801010001 2 30017
A01C200801010001 3 30002
A01C200801010001 4 30002
A01C200801010002 1 30012
A01C200801010002 2 30012
.....正常表中一个票号中只能存在一个保管员,现在如何将存在两个保管员的票号选出,并将其票号按其保管员个数来修改例如
A01C200801010001 1 30002
B01C200801010001 2 30017
A01C200801010001 3 30002
A01C200801010001 4 30002
A01C200801010002 1 30012
A01C200801010002 2 30012
如果发现在一个票号里有两个保管员存在将其中一个保管员对应的票号改成B01开头
票号 序号 保管员
A01C200801010001 1 30002
A01C200801010001 2 30017
A01C200801010001 3 30002
A01C200801010001 4 30002
A01C200801010002 1 30012
A01C200801010002 2 30012
.....正常表中一个票号中只能存在一个保管员,现在如何将存在两个保管员的票号选出,并将其票号按其保管员个数来修改例如
A01C200801010001 1 30002
B01C200801010001 2 30017
A01C200801010001 3 30002
A01C200801010001 4 30002
A01C200801010002 1 30012
A01C200801010002 2 30012
如果发现在一个票号里有两个保管员存在将其中一个保管员对应的票号改成B01开头
where exists (
select 1 from yyy where 票号=a.票号 and 保管员=a.保管员 and 序号<>a.序号
)
--or:
select a.*
from yyy a,(select 票号 from yyy group by 票号 having count(distinct 保管员)>1) as t
where a.票号=t.票号
from yyy a,(select 票号 from yyy group by 票号 having count(distinct 保管员)>1) as t
where a.票号=t.票号楼上的能查出来,可如何改呢,我看了,最后一张票号内就包含两个保管员
set 票号=char(ascii('A')+(select count(distinct 保管员) from yyy where 票号=a.票号 and 保管员<a.保管员))+stuff(a.票号,1,1,'')
from yyy a
where exists (
select 1 from yyy
where 票号=a.票号 and 保管员<a.保管员
)
(select t.* from tb t where 序号 = (select min(序号) from tb where 票号 = t.票号)) n
where m.票号 = n.票号 and m.保管员 = n.保管员
union all
select 'B01' + substring(m.票号,4,len(m.票号)) 票号 , 序号,保管员 from tb m
where 票号 + ',' + 保管员 not in (select t.票号 + ',' + t.保管员 from tb t where 序号 = (select min(序号) from tb where 票号 = t.票号))
insert into tb values('A01C200801010001', 1, '30002' )
insert into tb values('A01C200801010001', 2, '30017' )
insert into tb values('A01C200801010001', 3, '30002' )
insert into tb values('A01C200801010001', 4, '30002' )
insert into tb values('A01C200801010002', 1, '30012' )
insert into tb values('A01C200801010002', 2, '30012' )
goselect m.* from tb m,
(select t.* from tb t where 序号 = (select min(序号) from tb where 票号 = t.票号)) n
where m.票号 = n.票号 and m.保管员 = n.保管员
union all
select 'B01' + substring(m.票号,4,len(m.票号)) 票号 , 序号,保管员 from tb m
where 票号 + ',' + 保管员 not in (select t.票号 + ',' + t.保管员 from tb t where 序号 = (select min(序号) from tb where 票号 = t.票号))drop table tb/*
票号 序号 保管员
----------------------- ----------- ----------
A01C200801010001 1 30002
A01C200801010001 3 30002
A01C200801010001 4 30002
A01C200801010002 1 30012
A01C200801010002 2 30012
B01C200801010001 2 30017(所影响的行数为 6 行)
*/
--测试
declare @yyy table (
票号 varchar(20),
序号 int ,
保管员 varchar(10)
)
insert @yyy select
'A01C200801010001', 1, '30002'
union all select
'A01C200801010001', 2, '30017'
union all select
'A01C200801010001', 3, '30019'
union all select
'A01C200801010001', 4, '30002'
union all select
'A01C200801010002', 1, '30012'
union all select
'A01C200801010002', 2, '30012'
update a
set 票号=char(ascii(left(a.票号,1))+(select count(distinct 保管员) from @yyy where 票号=a.票号 and 保管员<a.保管员))+stuff(a.票号,1,1,'')
from @yyy a
where exists (
select 1 from @yyy
where 票号=a.票号 and 保管员<a.保管员
)select * from @yyy--结果
票号 序号 保管员
-------------------- ----------- ----------
A01C200801010001 1 30002
B01C200801010001 2 30017
C01C200801010001 3 30019
A01C200801010001 4 30002
A01C200801010002 1 30012
A01C200801010002 2 30012(所影响的行数为 6 行)
insert into tb values('A01C200801010001', 1, '30002' )
insert into tb values('A01C200801010001', 2, '30017' )
insert into tb values('A01C200801010001', 3, '30002' )
insert into tb values('A01C200801010001', 4, '30002' )
insert into tb values('A01C200801010002', 1, '30012' )
insert into tb values('A01C200801010002', 2, '30012' )
goselect * from
(
select m.* from tb m,
(select t.* from tb t where 序号 = (select min(序号) from tb where 票号 = t.票号)) n
where m.票号 = n.票号 and m.保管员 = n.保管员
union all
select 'B01' + substring(m.票号,4,len(m.票号)) 票号 , 序号,保管员 from tb m
where cast(票号 as varchar) + ',' + cast(保管员 as varchar) not in (select cast(票号 as varchar) + ',' + cast(保管员 as varchar) from tb t where 序号 = (select min(序号) from tb where 票号 = t.票号))
) t
order by substring(票号,4,len(票号)) , 序号drop table tb/*
票号 序号 保管员
----------------------- ----------- ----------
A01C200801010001 1 30002
B01C200801010001 2 30017
A01C200801010001 3 30002
A01C200801010001 4 30002
A01C200801010002 1 30012
A01C200801010002 2 30012(所影响的行数为 6 行)
*/
insert into yyy values('A01C200801010001',2,'30017')
insert into yyy values('A01C200801010001',3,'30002')
insert into yyy values('A01C200801010001',4,'30002')
insert into yyy values('A01C200801010002',1,'30012')
insert into yyy values('A01C200801010002',2,'30012')
select ph=case when (select count(distinct bgy) from yyy where ph=a.ph)>1 and bgy<>(select top 1 bgy from yyy group by ph,bgy having ph=a.ph order by count(bgy) desc) then stuff(ph,1,3,'B01') else ph end,
id,bgy from yyy a
create table yyy (ph varchar(100),id int,bgy varchar(10))insert into yyy values('A01C200801010001',1,'30002')
insert into yyy values('A01C200801010001',2,'30017')
insert into yyy values('A01C200801010001',3,'30019')
insert into yyy values('A01C200801010001',4,'30002')
insert into yyy values('A01C200801010002',1,'30012')
insert into yyy values('A01C200801010002',2,'30012')
SELECT PH=STUFF(PH,1,3,CHAR(ASCII('A')+IDD-1)+'01'),ID,BGY
FROM(
SELECT PH,ID,BGY,IDD=(SELECT COUNT(DISTINCT BGY) FROM YYY WHERE PH=A.PH AND BGY<=A.BGY) FROM YYY A
) B
PH ID BGY
---------------------------------------------------------------------------------------------------- ----------- ----------
A01C200801010001 1 30002
B01C200801010001 2 30017
C01C200801010001 3 30019
A01C200801010001 4 30002
A01C200801010002 1 30012
A01C200801010002 2 30012(6 行受影响)
insert into yyy values('A01C200801010001',2,'30017')
insert into yyy values('A01C200801010001',3,'30019')
insert into yyy values('A01C200801010001',4,'30002')
insert into yyy values('A01C200801010002',1,'30012')
insert into yyy values('A01C200801010002',2,'30012')
SELECT PH=STUFF(PH,1,3,CHAR(ASCII('A')+IDD-1)+'01'),ID,BGY
FROM(
SELECT PH,ID,BGY,IDD=(SELECT COUNT(DISTINCT BGY) FROM YYY WHERE PH=A.PH AND BGY<=A.BGY) FROM YYY A
) B
_____________________________________________________________
要是用update 更新回yyy如何做呢
--更新只要这一句
update a
set 票号=char(ascii(left(a.票号,1))+(select count(distinct 保管员) from yyy where 票号=a.票号 and 保管员<a.保管员))+stuff(a.票号,1,1,'')
from yyy a
where exists (
select 1 from yyy
where 票号=a.票号 and 保管员<a.保管员
)