汇总问题，昨晚的还没解决

[binhe521]

表 number name model period unit quantity
A.01 AA 国内01 120 pcs 2
A.01 AA 国内01 120 pcs 3
A.02 AB 国内02 100 pcs 7
B.01 BA 国外01 90 pcs 12
B.01 BA 国外01 90 pcs 13
B.01 BA 国外01 90 pcs 9

如何得到
表 number name model period unit quantity
A.01 AA 国内01 120 pcs 2
合计 5
A.02 AB 国内02 100 pcs 7
合计 7
B.01 BA 国外01 90 pcs 12
合计 34

[binhe521]

表 number name model period unit quantity
A.01 AA 国内01 120 pcs 2
A.01 AA 国内01 120 pcs 3
A.02 AB 国内02 100 pcs 7
B.01 BA 国外01 90 pcs 12
B.01 BA 国外01 90 pcs 13
B.01 BA 国外01 90 pcs 9

如何得到
表 number name model period unit sum
A.01 AA 国内01 120 pcs
合计 5
A.02 AB 国内02 100 pcs
合计 7
B.01 BA 国外01 90 pcs
合计 34

[binhe521]

晕 排版还真难排

[LouisXIV]

需要的没贴错吧-_-，怎么结果中A.01就一行？

[binhe521]

第二个表格 求出汇总就好了

[LouisXIV]

有点奇怪

declare @a table
(
number varchar(10),
name varchar(10),
model varchar(10),
period varchar(10),
unit varchar(10),
quantity int
)
insert into @a
select 'A.01','AA','国内01','120','pcs',2 union all
select 'A.01','AA','国内01','120','pcs',3 union all
select 'A.02','AB','国内02','100','pcs',7 union all
select 'B.01','BA','国外01','90','pcs',12 union all
select 'B.01','BA','国外01','90','pcs',13 union all
select 'B.01','BA','国外01','90','pcs',9

select * from @a

select *
from
(
select case
when grouping(number)=1 then '总计'
when grouping(name)=1 then '小计'
else number end as Number,
name
,period,unit,sum(quantity) as quantity
from @a
group by number,name,model,period,unit with rollup
)a
where
(name is null and period is null and unit is null)
or
( name is not null
and Period is not null
and unit is not null
)
/*
number name model period unit quantity
---------- ---------- ---------- ---------- ---------- -----------
A.01 AA 国内01 120 pcs 2
A.01 AA 国内01 120 pcs 3
A.02 AB 国内02 100 pcs 7
B.01 BA 国外01 90 pcs 12
B.01 BA 国外01 90 pcs 13
B.01 BA 国外01 90 pcs 9

Number name period unit quantity
---------- ---------- ---------- ---------- -----------
A.01 AA 120 pcs 5
小计 NULL NULL NULL 5
A.02 AB 100 pcs 7
小计 NULL NULL NULL 7
B.01 BA 90 pcs 34
小计 NULL NULL NULL 34
总计 NULL NULL NULL 46
*/

[binhe521]

能不能把null去掉阿 太难看了

[binhe521]

model 没有啊

[binhe521]

基本可以了 就差把null去掉

[LouisXIV]

declare @a table
(
number varchar(10),
name varchar(10),
model varchar(10),
period varchar(10),
unit varchar(10),
quantity int
)
insert into @a
select 'A.01','AA','国内01','120','pcs',2 union all
select 'A.01','AA','国内01','120','pcs',3 union all
select 'A.02','AB','国内02','100','pcs',7 union all
select 'B.01','BA','国外01','90','pcs',12 union all
select 'B.01','BA','国外01','90','pcs',13 union all
select 'B.01','BA','国外01','90','pcs',9

select * from @a

select *
from
(
select case
when grouping(number)=1 then '总计'
when grouping(name)=1 then '小计'
else number end as Number,
isnull(name,'') as Name,
isnull(model,'') as Model,
isnull(period,'') as Period,
isnull(unit,'') as Unit,sum(quantity) as quantity
from @a
group by number,name,model,period,unit with rollup
)a
where
(name ='' and period ='' and unit ='')
or
unit <>''

/*
number name model period unit quantity
---------- ---------- ---------- ---------- ---------- -----------
A.01 AA 国内01 120 pcs 2
A.01 AA 国内01 120 pcs 3
A.02 AB 国内02 100 pcs 7
B.01 BA 国外01 90 pcs 12
B.01 BA 国外01 90 pcs 13
B.01 BA 国外01 90 pcs 9

Number Name Model Period Unit quantity
---------- ---------- ---------- ---------- ---------- -----------
A.01 AA 国内01 120 pcs 5
小计 5
A.02 AB 国内02 100 pcs 7
小计 7
B.01 BA 国外01 90 pcs 34
小计 34
总计 46


*/

--在前台里Null也不会显示

[losedxyz]

mark

[binhe521]

OK 谢谢！！