select 变压器维修=xh, 维修数量=sum(case when lx=1 then 1 else 0 end), 发货数量=sum(case when lx=2 then 1 else 0 end) from ( select 变压器维修 as xh, 维修数量 as sl,1 as lx from BYQSAVE union all select 变压器发货,发货数量 ,2 from BYQSAVE ) t group by xh
--> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([变压器维修] varchar(8),[维修数量] int,[变压器发货] varchar(8),[发货数量] int) insert [tb] select 'S7-10/10',1,'S9-30/10',1 union all select 'S9-30/10',1,'无',0 union all select 'S9-30/10',1,'S9-50/10',1 union all select 'S7-30/10',1,'S9-50/10',1 union all select 'S7-30/10',1,'S9-30/10',1select [变压器维修],[发货数量]=sum([维修数量]),[发货数量]=sum([发货数量]) from ( select [变压器维修],[维修数量],0 as [发货数量] from tb union all select [变压器发货],0,[发货数量] from tb where [变压器发货]<>'无' ) as t group by[变压器维修] /* 变压器维修 发货数量 发货数量 -------- ----------- ----------- S7-10/10 1 0 S7-30/10 2 0 S9-30/10 2 2 S9-50/10 0 2(4 行受影响) */
create table BYQSAVE(变压器维修 varchar(20),维修数量 int,变压器发货 varchar(20) ,发货数量 int) insert into BYQSAVE select 'S7-10/10', 1, 'S9-30/10', 1 union all select 'S9-30/10', 1, '无', 0 union all select 'S9-30/10', 1, 'S9-50/10', 1 union all select 'S7-30/10', 1, 'S9-50/10', 1 union all select 'S7-30/10', 1, 'S9-30/10', 1 ;with t as ( select 变压器维修 as 变压器 from BYQSAVE union select 变压器发货 from BYQSAVE ) select 变压器,维修数量=sum(case when a.变压器=b.变压器维修 then 维修数量 else 0 end), 发货数量=sum(case when a.变压器=b.变压器发货 then 发货数量 else 0 end) from t a,BYQSAVE b where 变压器<>'无' group by 变压器变压器 维修数量 发货数量 S7-10/10 1 0 S7-30/10 2 0 S9-30/10 2 2 S9-50/10 0 2
insert into BYQSUM(列1,列2,列3) select 变压器维修,sum(维修数量),sum(发货数量) from byqsave group by 变压器维修
--> 测试数据: [BYQSAVE] if object_id('[BYQSAVE]') is not null drop table [BYQSAVE] go create table [BYQSAVE] (变压器维修 varchar(8),维修数量 int,变压器发货 varchar(8),发货数量 int) insert into [BYQSAVE] select 'S7-10/10',1,'S9-30/10',1 union all select 'S9-30/10',1,null ,0 union all select 'S9-30/10',1,'S9-50/10',1 union all select 'S7-30/10',1,'S9-50/10',1 union all select 'S7-30/10',1,'S9-30/10',1select 变压器维修=xh, 维修数量=sum(case when lx=1 then 1 else 0 end), 发货数量=sum(case when lx=2 then 1 else 0 end) from ( select 变压器维修 as xh, 维修数量 as sl,1 as lx from BYQSAVE union all select 变压器发货,发货数量 ,2 from BYQSAVE ) t where xh is not null group by xh变压器维修 维修数量 发货数量 -------- ----------- ----------- S7-10/10 1 0 S7-30/10 2 0 S9-30/10 2 2 S9-50/10 0 2(4 行受影响)
select 变压器维修=xh, 维修数量=sum(case when lx=1 then 1 else 0 end), 发货数量=sum(case when lx=2 then 1 else 0 end) from ( select 变压器维修 as xh, 维修数量 as sl,1 as lx from BYQSAVE union all select 变压器发货,发货数量 ,2 from BYQSAVE ) t group by xh
维修数量=sum(case when lx=1 then 1 else 0 end),
发货数量=sum(case when lx=2 then 1 else 0 end)
from (
select 变压器维修 as xh, 维修数量 as sl,1 as lx from BYQSAVE
union all
select 变压器发货,发货数量 ,2 from BYQSAVE
) t
group by xh
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([变压器维修] varchar(8),[维修数量] int,[变压器发货] varchar(8),[发货数量] int)
insert [tb]
select 'S7-10/10',1,'S9-30/10',1 union all
select 'S9-30/10',1,'无',0 union all
select 'S9-30/10',1,'S9-50/10',1 union all
select 'S7-30/10',1,'S9-50/10',1 union all
select 'S7-30/10',1,'S9-30/10',1select [变压器维修],[发货数量]=sum([维修数量]),[发货数量]=sum([发货数量]) from
(
select [变压器维修],[维修数量],0 as [发货数量] from tb
union all
select [变压器发货],0,[发货数量] from tb where [变压器发货]<>'无'
) as t
group by[变压器维修]
/*
变压器维修 发货数量 发货数量
-------- ----------- -----------
S7-10/10 1 0
S7-30/10 2 0
S9-30/10 2 2
S9-50/10 0 2(4 行受影响)
*/
create table BYQSAVE(变压器维修 varchar(20),维修数量 int,变压器发货 varchar(20) ,发货数量 int)
insert into BYQSAVE
select 'S7-10/10', 1, 'S9-30/10', 1 union all
select 'S9-30/10', 1, '无', 0 union all
select 'S9-30/10', 1, 'S9-50/10', 1 union all
select 'S7-30/10', 1, 'S9-50/10', 1 union all
select 'S7-30/10', 1, 'S9-30/10', 1
;with t as
(
select 变压器维修 as 变压器 from BYQSAVE
union
select 变压器发货 from BYQSAVE
)
select 变压器,维修数量=sum(case when a.变压器=b.变压器维修 then 维修数量 else 0 end),
发货数量=sum(case when a.变压器=b.变压器发货 then 发货数量 else 0 end)
from t a,BYQSAVE b
where 变压器<>'无'
group by 变压器变压器 维修数量 发货数量
S7-10/10 1 0
S7-30/10 2 0
S9-30/10 2 2
S9-50/10 0 2
insert into BYQSUM(列1,列2,列3)
select 变压器维修,sum(维修数量),sum(发货数量)
from byqsave
group by 变压器维修
if object_id('[BYQSAVE]') is not null drop table [BYQSAVE]
go
create table [BYQSAVE] (变压器维修 varchar(8),维修数量 int,变压器发货 varchar(8),发货数量 int)
insert into [BYQSAVE]
select 'S7-10/10',1,'S9-30/10',1 union all
select 'S9-30/10',1,null ,0 union all
select 'S9-30/10',1,'S9-50/10',1 union all
select 'S7-30/10',1,'S9-50/10',1 union all
select 'S7-30/10',1,'S9-30/10',1select 变压器维修=xh,
维修数量=sum(case when lx=1 then 1 else 0 end),
发货数量=sum(case when lx=2 then 1 else 0 end)
from (
select 变压器维修 as xh, 维修数量 as sl,1 as lx from BYQSAVE
union all
select 变压器发货,发货数量 ,2 from BYQSAVE
) t
where xh is not null
group by xh变压器维修 维修数量 发货数量
-------- ----------- -----------
S7-10/10 1 0
S7-30/10 2 0
S9-30/10 2 2
S9-50/10 0 2(4 行受影响)
select 'S9-30/10',1,null ,0
select 'S9-30/10',1,'S9-50/10',1
select 'S7-30/10',1,'S9-50/10',1
这几个值,这个表里面的值很多 不只是这几个 能不能有一个sum的函数 可以自动查询到相应的字段然后在求和啊?
维修数量=sum(case when lx=1 then 1 else 0 end),
发货数量=sum(case when lx=2 then 1 else 0 end)
from (
select 变压器维修 as xh, 维修数量 as sl,1 as lx from BYQSAVE
union all
select 变压器发货,发货数量 ,2 from BYQSAVE
) t
group by xh