update tablea set count = count + case when(select count(*) from tableb where a.phone = phone) > 0 then 1 else 0 end from tablea ainsert into tablea select phone,1 from tableb where phone not in(select phone ffom tablea)
declare @a table(id int identity(1,1),phone int,count int) insert into @a select 123,1 insert into @a select 456,2 insert into @a select 789,3 insert into @a select 555,2declare @b table(id int identity(1,1),phone int) insert into b select 111 insert into b select 456 insert into b select 789 update a set count=a.count+1 from @a a,@b b where a.phone=b.phoneinsert into @a select phone,1 from @b b where not exists(select 1 from @a where phone=b.phone)select * from @a/* id phone count ----------- ----------- ----------- 1 123 1 2 456 3 3 789 4 4 555 2 5 111 1 */
update a set [count]=[count]+IsNULL(( select count(1) from @b where a.phone=phone group by phone),0) from @a a Insert into @a(phone,count) select phone,1 from @b b where not exists( select * from @a where b.phone=phone)
update a set count=count+1 from a,b where a.phone=b.phoneinsert into a select phone,1 from b where not exists(select 1 from a where phone=b.phone)
insert into a select phone,1 from b where not exists(select 1 from a where phone=b.phone)子默大师,你的两个版本都好像没考虑一种情况也....如果b表中有两条相同记录,应该算两次才对,而你只算一次...
To: libin_ftsafe(子陌红尘) 老大,我又借你的数据了!:) To:sanfeng123 () 我们名字好像啊!declare @a table(id int identity(1,1),phone int,count int) insert into @a select 123,1 insert into @a select 456,2 insert into @a select 789,3 insert into @a select 555,2declare @b table(id int identity(1,1),phone int) insert into @b select 111 insert into @b select 456 insert into @b select 789 update a set [count]=a.[count]+b.c1 from @a a,(select phone,count(*) as c1 from @b group by phone) b where a.phone=b.phoneinsert @a select phone,count(*) as c1 from @b b where not exists (select * from @a a where a.phone=b.phone) group by phoneselect * from @a
declare @a table(id int identity(1,1),phone int,count int) insert into @a select 123,1 insert into @a select 456,2 insert into @a select 789,3 insert into @a select 555,2declare @b table(id int identity(1,1),phone int) insert into @b select 111 insert into @b select 111 insert into @b select 456 insert into @b select 456 insert into @b select 789 update a set count=a.count+(select count(*) from @b where phone=a.phone) from @a a where exists(select 1 from @b where phone=a.phone)insert into @a select phone,count(*) from @b b where not exists(select 1 from @a where phone=b.phone) group by phoneselect * from @a/* id phone count ----------- ----------- ----------- 1 123 1 2 456 4 3 789 4 4 555 2 5 111 2 */
update a set count=a.count+1 from @a a inner join @b b on a.phone=b.phoneinsert into @a select phone,1 from @b b where not exists(select 1 from @a where phone=b.phone)
set count = count + case when(select count(*) from tableb where a.phone = phone) > 0
then 1
else 0
end
from tablea ainsert into tablea
select phone,1
from tableb
where phone not in(select phone ffom tablea)
insert into @a select 123,1
insert into @a select 456,2
insert into @a select 789,3
insert into @a select 555,2declare @b table(id int identity(1,1),phone int)
insert into b select 111
insert into b select 456
insert into b select 789
update a
set
count=a.count+1
from
@a a,@b b
where
a.phone=b.phoneinsert into @a
select
phone,1
from
@b b
where
not exists(select 1 from @a where phone=b.phone)select * from @a/*
id phone count
----------- ----------- -----------
1 123 1
2 456 3
3 789 4
4 555 2
5 111 1
*/
select count(1) from @b where a.phone=phone
group by phone),0)
from @a a Insert into @a(phone,count)
select phone,1
from @b b where not exists(
select * from @a
where b.phone=phone)
To:sanfeng123 () 我们名字好像啊!declare @a table(id int identity(1,1),phone int,count int)
insert into @a select 123,1
insert into @a select 456,2
insert into @a select 789,3
insert into @a select 555,2declare @b table(id int identity(1,1),phone int)
insert into @b select 111
insert into @b select 456
insert into @b select 789
update a set [count]=a.[count]+b.c1
from @a a,(select phone,count(*) as c1 from @b group by phone) b
where a.phone=b.phoneinsert @a
select phone,count(*) as c1 from @b b where not exists
(select * from @a a where a.phone=b.phone) group by phoneselect * from @a
insert into @a select 123,1
insert into @a select 456,2
insert into @a select 789,3
insert into @a select 555,2declare @b table(id int identity(1,1),phone int)
insert into @b select 111
insert into @b select 111
insert into @b select 456
insert into @b select 456
insert into @b select 789
update a set count=a.count+(select count(*) from @b where phone=a.phone)
from @a a where exists(select 1 from @b where phone=a.phone)insert into @a select phone,count(*) from @b b
where not exists(select 1 from @a where phone=b.phone)
group by phoneselect * from @a/*
id phone count
----------- ----------- -----------
1 123 1
2 456 4
3 789 4
4 555 2
5 111 2
*/
from @a a inner join @b b on a.phone=b.phoneinsert into @a select phone,1
from @b b where not exists(select 1 from @a where phone=b.phone)