declare @tb_1 table
(
type int,
num int
)
insert into @tb_1
select 1,10
union
select 2,15
union
select 3,25
declare @tb_2 table
(
ID int ,
f0010 int,
f0020 int,
f0030 int
)insert into @tb_2 (ID)
select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
如何用一条语句更新@tb_2得到:
1 10 15 25
2 10 15 25
3 10 15 25
4 10 15 25
5 10 15 25
6 10 15 25
7 10 15 25
(
type int,
num int
)
insert into @tb_1
select 1,10
union
select 2,15
union
select 3,25
declare @tb_2 table
(
ID int ,
f0010 int,
f0020 int,
f0030 int
)insert into @tb_2 (ID)
select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
如何用一条语句更新@tb_2得到:
1 10 15 25
2 10 15 25
3 10 15 25
4 10 15 25
5 10 15 25
6 10 15 25
7 10 15 25
f0010=(select num from @tb_1 where type=1),
f0020=(select num from @tb_1 where type=2),
f0030=(select num from @tb_1 where type=3)
set f0010 = 10,
f0020 = 15,
f0030 = 25
(
type int,
num int
)
insert into @tb_1
select 1,10
union
select 2,15
union
select 3,25
declare @tb_2 table
(
ID int ,
f0010 int,
f0020 int,
f0030 int
) insert into @tb_2 (ID)
select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7 update @tb_2 set
f0010=(select num from @tb_1 where type=1),
f0020=(select num from @tb_1 where type=2),
f0030=(select num from @tb_1 where type=3)select * from @tb_2
from (select num from @tb_1 where type=1)a,
(select num from @tb_1 where type=2)b,
(select num from @tb_1 where type=3)c
(
type int,
num int
)
insert into @tb_1
select 1,10
union
select 2,15
union
select 3,25
union
select 4,30
declare @tb_2 table
(
ID int ,
f0010 int,
f0020 int,
f0030 int
)insert into @tb_2 (ID)
select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
select * from @tb_2select case when type=1 then 1
when type in(2,3) then 2
when type=4 then 3 end as type,sum(num) as sum_num
from @tb_1
group by case when type=1 then 1
when type in(2,3) then 2
when type=4 then 3 end
update a set a.f0010=case when b.type=1 then b.sum_num end,
a.f0020=case when b.type=2 then b.sum_num end,
a.f0030=case when b.type=3 then b.sum_num end
from @tb_2 as a,(select case when type=1 then 1
when type in(2,3) then 2
when type=4 then 3 end as type,sum(num) as sum_num
from @tb_1
group by case when type=1 then 1
when type in(2,3) then 2
when type=4 then 3 end
) as b