存储过程中的临时表再分组输出

Question

 select typeid,outnum,innum,allnum from #tempBalance order by typeid
查询临时表出来的结果是:typeid outnum  innum  allnum 
0       2.1     0     2.1
0       4.1     1     3.1
0       1.2     0.2   1.0
0       2.6     0     2.6
1       1.1     0     1.1
1       2.1     0     2.1
1       3.1     0     3.1
2       2.1     1     1.1
2       2.1     1     1.1查询语句应该怎么改让结果是这样:typeid outnum  innum  allnum 
0       2.1     0     2.1
        4.1     1     3.1
        1.2     0.2   1.0
        2.6     0     2.6
        10.0    1.2   8.8   //分组对应列相加
1       1.1     0     1.1
        2.1     0     2.1
        3.1     0     3.1
        6.3     0     6.3  //分组对应列相加
2       2.1     1     1.1
        2.1     1     1.1
        4.2     2     2.2  //分组对应列相加

Answer 1

row_number()over
grouping ...

Answer 2

;with ach as
(
select typeid,outnum,innum,allnum,0 as flag from #tempBalance
union all
select typeid,sum(outnum),sum(innum),sum(allnum),1
from #tempBalance
),art as
(
select rid=row_number() over (partition by typeid order by flag),*
from ach
)select (case when rid = 1 then ltrim(typeid) else '' end) typeid,
outnum,innum,allnum
from art
order by typeid,rid

Answer 3

select typeid,outnum,innum,allnum from #tempBalance
group by
WITH ROLLUP 
order by typeid
--试试

Answer 4

select typeid,outnum,innum,allnum from #tempBalance
group by typeid
WITH ROLLUP 
order by typeid
--试试

Answer 5

typeid' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中

Answer 6

outnum' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中

Answer 7

;with ach as
(
    select typeid,outnum,innum,allnum,0 as flag from #tempBalance
    union all
    select typeid,sum(outnum),sum(innum),sum(allnum),1
    from #tempBalance
    group by typeid
),art as
(
    select rid=row_number() over (partition by typeid order by flag),*
    from ach
)select (case when rid = 1 then ltrim(typeid) else '' end) typeid,
    outnum,innum,allnum
from art
order by typeid,rid

Answer 8

create table tb(typeid int,outnum decimal(8,1),innum decimal(8,1), allnum decimal(8,1))
insert into tb select 0,2.1,  0,  2.1
insert into tb select 0,4.1,  1,  3.1
insert into tb select 0,1.2,  0.2,1.0
insert into tb select 0,2.6,  0,  2.6
insert into tb select 1,1.1,  0,  1.1
insert into tb select 1,2.1,  0,  2.1
insert into tb select 1,3.1,  0,  3.1
insert into tb select 2,2.1,  1,  1.1
insert into tb select 2,2.1,  1,  1.1
go
;with c1 as(
select row_number()over(partition by typeid order by (select 1))rn,* from tb
),c2 as(
select max(rn)+1 rn,typeid,sum(outnum)outnum,sum(innum)innum,sum(allnum)allnum from c1 group by typeid
)select (case when rn=1 then ltrim(typeid) else ' ' end)typeid1,outnum,innum,allnum from(
select * from c1
union all
select top 100 * from c2
)t order by typeid,rn
/*
typeid1      outnum                                  innum                                   allnum
------------ --------------------------------------- --------------------------------------- ---------------------------------------
0            2.1                                     0.0                                     2.1
             4.1                                     1.0                                     3.1
             1.2                                     0.2                                     1.0
             2.6                                     0.0                                     2.6
             10.0                                    1.2                                     8.8
1            1.1                                     0.0                                     1.1
             2.1                                     0.0                                     2.1
             3.1                                     0.0                                     3.1
             6.3                                     0.0                                     6.3
2            2.1                                     1.0                                     1.1
             2.1                                     1.0                                     1.1
             4.2                                     2.0                                     2.2(12 行受影响)*/
go
drop table tb

Answer 9

; with f as
(
select 
   case ltrim(px) when '1' then ltrim(typeid) else '' end as typeid,
   outnum,innum,allnum
 from
   (select px=ROW_NUMBER()over(partition by typeid order by GETDATE()),* from tb)t
 )
select
   typeid,
   SUM(outnum) as outnum ,SUM(innum) as innum ,SUM(allnum) as allnum
from
   f
group by
   typeid
with rollup
having
   GROUPING(typeid)=0 

Answer 10

这种不行
换一种
create table tb
(
typeid int, 
outnum decimal(12,1),
innum  decimal(12,1),
allnum decimal(12,1)
)
insert into tb values(0 ,2.1,0,2.1)
insert into tb values(0 ,4.1,1,3.1)
insert into tb values(0 ,1.2,0.2,1.0)
insert into tb values(0 ,2.0,0,2.6)
insert into tb values(1 ,1.0,0,1.1)
insert into tb values(1 ,2.1,0,2.1)
insert into tb values(1 ,3.1,0,3.1)
insert into tb values(2 ,2.1,1,1.1)
insert into tb values(2 ,2.1,1,1.1);with ach as
(
    select typeid,outnum,innum,allnum,0 as flag from tb
    union all
    select typeid,sum(outnum),sum(innum),sum(allnum),1
    from tb
    group by typeid
),art as
(
    select rid=row_number() over (partition by typeid order by flag),*
    from ach
)select (case when rid = 1 then ltrim(typeid) else '' end) typeid,
    outnum,innum,allnum
from art/*
typeid,outnum,innum,allnum
0,2.1,0.0,2.1
,4.1,1.0,3.1
,1.2,0.2,1.0
,2.0,0.0,2.6
,9.4,1.2,8.8
1,1.0,0.0,1.1
,2.1,0.0,2.1
,3.1,0.0,3.1
,6.2,0.0,6.3
2,2.1,1.0,1.1
,2.1,1.0,1.1
,4.2,2.0,2.2(12 行受影响)

Answer 11

把你这个放进去存储过程中出现这个错误
第 112 行
使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。