update t1 set biaozhi = case when ctn > initnum then 1 when ctn = initnum then 0 else -1 end from t1 inner join (select id1, count(*) as ctn from t2 group by id1) b on t1.id1 = b.id1
update T1 set Biaozhi=( case when T1.InitNum>T.num then 1 when T1.InitNum=T.num then 0 when T1.InitNum<T.num then -1 else T1.InitNum end ) from T1 join ( select Id1 as 'Id' ,count(1) as 'num' from T2 group by Id1 )T on T1.Id=T.Id
update T1 set BiaoZhi=M.Biaozhi from T1, (select id,case when T1.InitNum<T.Cnt then 1 else 0 end as Biaozhi from T1,(select id count(*) as Cnt from T2 from id) T where T1.id=T.id)M where T1.id=M.id
update T1 set BiaoZhi=M.Biaozhi from T1, (select id,case when T1.InitNum<T.Cnt then 1 when T1.InitNum=T.Cnt then 0 else -1 end as Biaozhi from T1,(select id count(*) as Cnt from T2 from id) T where T1.id=T.id)M where T1.id=M.id
update T1 set BiaoZhi=M.Biaozhi from T1, (select T1.id,case when T1.InitNum<T.Cnt then 1 when T1.InitNum=T.Cnt then 0 else -1 end as Biaozhi from T1,(select id, count(*) as Cnt from T2 group by id) T where T1.id=T.id)M where T1.id=M.id
update T1 set Biaozhi=( case when T1.InitNum>T.num then 1 when T1.InitNum=T.num then 0 when T1.InitNum<T.num then -1 else T1.InitNum end ) from T1 join ( select Id1 as 'Id' ,count(1) as 'num' from T2 group by Id1 )T on T1.Id=T.Id
--测试代码 if exists(select * from sysobjects where id = object_id(N'[dbo].[T1]') and objectproperty(id,N'isusertable') = 1) drop table [dbo].[T1]if exists(select * from sysobjects where id = object_id(N'[dbo].[T2]') and objectproperty(id,N'isusertable') = 1) drop table [dbo].[T2]create table T1 ( id int, InitNum int, BiaoZhi int )insert T1 select 1,2,0 union all select 2,1,0 union all select 3,3,0create table T2 ( id1 int, id2 int ) insert T2 select 1,2 union all select 1,3 union all select 1,4 union all select 2,1 union all select 3,5update T1 set biaozhi = ( case when T.Num > T1.InitNum then 1 when T.Num = T1.InitNum then 0 when T.Num < T1.InitNum then -1 end ) from t1 inner JOIN (select id1 as ID,count(1) as Num from T2 group by id1)T on T.id = T1.idselect * from T1/* 测试结果id InitNum BiaoZhi ----------- ----------- ----------- 1 2 1 2 1 0 3 3 -1(所影响的行数为 3 行) */
update T1 set BiaoZhi=M.Biaozhi from T1, (select id,case when T1.InitNum<T.Cnt then 1 when T1.InitNum=T.Cnt then 0 else -1 end as Biaozhi from T1,(select id count(*) as Cnt from T2 from id) T where T1.id=T.id)M where T1.id=M.id
用这个语句就可以解决T2中没有T1中记录的问题,关键是left join的应用 update T1 set BiaoZhi=M.Biaozhi from T1, (select T1.id,case when T1.InitNum<isnull(T.Cnt,0) then 1 when T1.InitNum=isnull(T.Cnt,0) then 0 else -1 end as Biaozhi from T1 left join (select id1 count(*) as Cnt from T2 from id group by id1) T on T1.id=T.id1) M where T1.id=M.id
set Biaozhi=(
case when T1.InitNum>T.num
then 1
when T1.InitNum=T.num
then 0
when T1.InitNum<T.num
then -1
else T1.InitNum
end
)
from T1
join (
select Id1 as 'Id'
,count(1) as 'num'
from T2
group by Id1
)T on T1.Id=T.Id
(select id,case when T1.InitNum<T.Cnt then 1 else 0 end as Biaozhi
from T1,(select id count(*) as Cnt from T2 from id) T
where T1.id=T.id)M where T1.id=M.id
(select id,case when T1.InitNum<T.Cnt then 1 when T1.InitNum=T.Cnt then 0 else -1 end as Biaozhi
from T1,(select id count(*) as Cnt from T2 from id) T
where T1.id=T.id)M where T1.id=M.id
(select T1.id,case when T1.InitNum<T.Cnt then 1 when T1.InitNum=T.Cnt then 0 else -1 end as Biaozhi
from T1,(select id, count(*) as Cnt from T2 group by id) T
where T1.id=T.id)M where T1.id=M.id
set Biaozhi=( case when T1.InitNum>T.num then 1
when T1.InitNum=T.num then 0
when T1.InitNum<T.num then -1 else T1.InitNum end )
from T1
join ( select Id1 as 'Id' ,count(1) as 'num' from T2 group by Id1 )T on T1.Id=T.Id
if exists(select * from sysobjects where id = object_id(N'[dbo].[T1]') and objectproperty(id,N'isusertable') = 1)
drop table [dbo].[T1]if exists(select * from sysobjects where id = object_id(N'[dbo].[T2]') and objectproperty(id,N'isusertable') = 1)
drop table [dbo].[T2]create table T1
(
id int,
InitNum int,
BiaoZhi int
)insert T1
select 1,2,0 union all
select 2,1,0 union all
select 3,3,0create table T2
(
id1 int,
id2 int
)
insert T2
select 1,2 union all
select 1,3 union all
select 1,4 union all
select 2,1 union all
select 3,5update T1 set biaozhi =
(
case when T.Num > T1.InitNum then 1
when T.Num = T1.InitNum then 0
when T.Num < T1.InitNum then -1
end
)
from t1
inner JOIN (select id1 as ID,count(1) as Num from T2 group by id1)T on T.id = T1.idselect * from T1/*
测试结果id InitNum BiaoZhi
----------- ----------- -----------
1 2 1
2 1 0
3 3 -1(所影响的行数为 3 行)
*/
set BiaoZhi=M.Biaozhi from T1,
(select id,case when T1.InitNum<T.Cnt then 1 when T1.InitNum=T.Cnt then 0 else -1 end as Biaozhi
from T1,(select id count(*) as Cnt from T2 from id) T
where T1.id=T.id)M where T1.id=M.id
update T1
set BiaoZhi=M.Biaozhi from T1,
(select T1.id,case when T1.InitNum<isnull(T.Cnt,0) then 1 when T1.InitNum=isnull(T.Cnt,0) then 0 else -1 end as Biaozhi from T1 left join (select id1 count(*) as Cnt from T2 from id group by id1) T on T1.id=T.id1) M where T1.id=M.id