各位大侠,有个问题请教,我现在有两张表A,B字段都有num,如
A B
num num
22011 22011
20133 22011
38211 22011
..... 38211
..... .....
这种情况,我先在要拿A表的字段来和B表的字段比较,就像上面的表一样,A表有个数据是22011,B表里面有三条这个记录,我现在要依次进行比较,比较到第一个,我就把他最后的数字变掉,想要得到我比对以后的数据是这样的:
A B
num num
22011 2201n
20133 2201y
38211 2201y
..... 3821n
..... .....
不知道能不能实现,请各位大侠帮帮忙,谢谢了!
A B
num num
22011 22011
20133 22011
38211 22011
..... 38211
..... .....
这种情况,我先在要拿A表的字段来和B表的字段比较,就像上面的表一样,A表有个数据是22011,B表里面有三条这个记录,我现在要依次进行比较,比较到第一个,我就把他最后的数字变掉,想要得到我比对以后的数据是这样的:
A B
num num
22011 2201n
20133 2201y
38211 2201y
..... 3821n
..... .....
不知道能不能实现,请各位大侠帮帮忙,谢谢了!
from B b1 LEFT join A On A.Num=B1.Num
create table A(num varchar(10))
create table B(num varchar(10))
insert into a values(22011)
insert into a values(20133)
insert into a values(38211)
insert into b values(22011)
insert into b values(22011)
insert into b values(22011)
insert into B values(38211)
goselect id = identity(int,1,1) , * into tmp from b truncate table binsert into b
select case when px = 1 then left(cast(m.num as varchar),len(cast(m.num as varchar)) - 1) + 'n' else left(cast(m.num as varchar),len(cast(m.num as varchar)) - 1) + 'y' end from
(
select * , px = (select count(1) from tmp where num = t.num and id < t.id) + 1 from tmp t
) m , a n
where m.num = n.numselect * from bdrop table a , b,tmp/*
num
----------
2201n
2201y
2201y
3821n(所影响的行数为 4 行)
*/
create table B(num varchar(10))
insert into a values(22011)
insert into a values(20133)
insert into a values(38211)
insert into b values(22011)
insert into b values(22011)
insert into b values(22011)
insert into B values(38211)
goselect id = identity(int,1,1) , * into tmp from b --改改这里
delete b from b ,a where b.num = a.numinsert into b
select case when px = 1 then left(cast(m.num as varchar),len(cast(m.num as varchar)) - 1) + 'n' else left(cast(m.num as varchar),len(cast(m.num as varchar)) - 1) + 'y' end from
(
select * , px = (select count(1) from tmp where num = t.num and id < t.id) + 1 from tmp t
) m , a n
where m.num = n.numselect * from bdrop table a , b,tmp/*
num
----------
2201n
2201y
2201y
3821n(所影响的行数为 4 行)
*/