34,593
社区成员
发帖
与我相关
我的任务
分享
if OBJECT_ID('A') is not null drop table A
create table A(khqq nvarchar(20),khnc nvarchar(20),khz nvarchar(20),khyf int)
if OBJECT_ID('B') is not null drop table B
select * into B from A where 1<>1
if OBJECT_ID('C') is not null drop table C
select * into C from A where 1<>1
insert into A
select '45331','澔a',NULL,400 union all
select '45331','澔b',null,1000 union all
select '4720','灵a', NULL,400 union all
select '4720','灵b', NULL,150 union all
select '4966','慧澄僧',NULL,50 union all
select '5041','嘎','林组',400 union all
select '5760','孤坟野','慧新',500 union all
select '806790','舞自飛','韩导组',650 union all
select '8729','心','飞',100 union all
select '8729','心','飞',250 union all
select '123456','其','飞',250 union all
select '9425','伟',null,700
;with b_new as(
SELECT b.khqq,SUM(b.khyf) khyf FROM A as b where
khz is null
group by b.khqq
)
--将数据插入表B
insert into B
select b.khqq,(select top 1 khnc from A WHERE khqq=b.khqq) as khnc,null,b.khyf from b_new
as b
select * from B
/*
khqq khnc khz khyf
45331 澔a NULL 1400
4720 灵a NULL 550
4966 慧澄僧 NULL 50
9425 伟 NULL 700
*/
--将数据插入表C
insert into C(khz,khyf)
SELECT b.khz,SUM(b.khyf) khyf FROM A as b where
khz is not null
group by b.khz
select khz,khyf from C
/*
khz khyf
飞 600
韩导组 650
慧新 500
林组 400
*/