从没见过这样的问题，在线等。

Question

declare @a table (line int, linename varchar(20),decd int,dename varchar(20),sel1 money,sel2 money,sel3 money)
insert @a
select 1,'生　活',10,'aa',20.000,30.000,50.000
union all
select 1,'生　活',12,'bb',20.000,30.000,50.000
union all
select 1,'生　活',13,'cc',20.000,30.000,50.000
union all
select 1,'生　活',14,'dd',20.000,30.000,50.000
union all
select 1,'生　活',16,'ee',20.000,30.000,50.000
union all
select 2,'家　電',20,'mm',20.000,30.000,50.000
union all
select 2,'家　電',23,'nn',20.000,30.000,50.000
union all
select 2,'家　電',50,'kk',20.000,30.000,50.000
select case when (decd is null) and (dename is null) then '' else rtrim(line) end as line,isnull(rtrim(decd),'Total'),isnull(dename,''),sum(sel1) as sel1,sum(sel2) as sel2,sum(sel3) as sel3
from @a 
group by line,decd,dename with rollup
having ((dename is not null) or (decd is null and dename is null)) and line is not null
/*
line                                           sel1                  sel2                  sel3                  
------------ ------------ -------------------- --------------------- --------------------- --------------------- 
1            10           aa                   20.0000               30.0000               50.0000
1            12           bb                   20.0000               30.0000               50.0000
1            13           cc                   20.0000               30.0000               50.0000
1            14           dd                   20.0000               30.0000               50.0000
1            16           ee                   20.0000               30.0000               50.0000
             Total                             100.0000              150.0000              250.0000
2            20           mm                   20.0000               30.0000               50.0000
2            23           nn                   20.0000               30.0000               50.0000
2            50           kk                   20.0000               30.0000               50.0000
             Total                             60.0000               90.0000               150.0000(10 row(s) affected)
*/

Answer 1

declare @a table (line int, linename Nvarchar(20),decd int,dename varchar(20),sel1 money,sel2 money,sel3 money)
insert @a
select 1,N'生　活',10,'aa',20.000,30.000,50.000
union all
select 1,N'生　活',12,'bb',20.000,30.000,50.000
union all
select 1,N'生　活',13,'cc',20.000,30.000,50.000
union all
select 1,N'生　活',14,'dd',20.000,30.000,50.000
union all
select 1,N'生　活',16,'ee',20.000,30.000,50.000
union all
select 2,N'家　電',20,'mm',20.000,30.000,50.000
union all
select 2,N'家　電',23,'nn',20.000,30.000,50.000
union all
select 2,N'家　電',50,'kk',20.000,30.000,50.000
SELECT CAST(LINE AS VARCHAR(5)),CAST(DECD AS VARCHAR(5)),DENAME,SEL1,SEL2,SEL3 FROM @A WHERE linename=N'生　活' 
UNION ALL
SELECT '',N'生　活','',SUM(SEL1),SUM(SEL2),SUM(SEL3) FROM @A WHERE linename=N'生　活' GROUP BY LINENAME
UNION ALL
SELECT CAST(LINE AS VARCHAR(5)),CAST(DECD AS VARCHAR(5)),DENAME,SEL1,SEL2,SEL3 FROM @A WHERE linename=N'家　電' 
UNION ALL
SELECT '',N'家　電','',SUM(SEL1),SUM(SEL2),SUM(SEL3) FROM @A WHERE linename=N'家　電' GROUP BY LINENAME
/*            DENAME               SEL1                  SEL2                  SEL3                  
----- ----- -------------------- --------------------- --------------------- --------------------- 
1     10    aa                   20.0000               30.0000               50.0000
1     12    bb                   20.0000               30.0000               50.0000
1     13    cc                   20.0000               30.0000               50.0000
1     14    dd                   20.0000               30.0000               50.0000
1     16    ee                   20.0000               30.0000               50.0000
      生　活                        100.0000              150.0000              250.0000
2     20    mm                   20.0000               30.0000               50.0000
2     23    nn                   20.0000               30.0000               50.0000
2     50    kk                   20.0000               30.0000               50.0000
      家　電                        60.0000               90.0000               150.0000*/

Answer 2

declare @a table (line int, linename varchar(20),decd int,dename varchar(20),sel1 money,sel2 money,sel3 money)
insert @a
select 1,'生　活',10,'aa',20.000,30.000,50.000
union all
select 1,'生　活',12,'bb',20.000,30.000,50.000
union all
select 1,'生　活',13,'cc',20.000,30.000,50.000
union all
select 1,'生　活',14,'dd',20.000,30.000,50.000
union all
select 1,'生　活',16,'ee',20.000,30.000,50.000
union all
select 2,'家　電',20,'mm',20.000,30.000,50.000
union all
select 2,'家　電',23,'nn',20.000,30.000,50.000
union all
select 2,'家　電',50,'kk',20.000,30.000,50.000select 
line=case when grouping(decd)=1 then '' else rtrim(line) end,
linename,
isnull(rtrim(decd),'')decd,sum(sel1)sel1,sum(sel2)sel2,sum(sel3)sel3
from 
@a
group by line,linename,decd with rollup
having not (grouping(linename)=1 and grouping(decd)=1)
(8 個資料列受到影響)
line         linename             decd         sel1                  sel2                  sel3
------------ -------------------- ------------ --------------------- --------------------- ---------------------
1            生　活                  10           20.00                 30.00                 50.00
1            生　活                  12           20.00                 30.00                 50.00
1            生　活                  13           20.00                 30.00                 50.00
1            生　活                  14           20.00                 30.00                 50.00
1            生　活                  16           20.00                 30.00                 50.00
             生　活                               100.00                150.00                250.00
2            家　電                  20           20.00                 30.00                 50.00
2            家　電                  23           20.00                 30.00                 50.00
2            家　電                  50           20.00                 30.00                 50.00
             家　電                               60.00                 90.00                 150.00(10 個資料列受到影響)

Answer 3

declare @a table (line int, linename varchar(20),decd int,dename varchar(20),sel1 money,sel2 money,sel3 money)
insert @a
select 1,'生　活',10,'aa',20.000,30.000,50.000
union all
select 1,'生　活',12,'bb',20.000,30.000,50.000
union all
select 1,'生　活',13,'cc',20.000,30.000,50.000
union all
select 1,'生　活',14,'dd',20.000,30.000,50.000
union all
select 1,'生　活',16,'ee',20.000,30.000,50.000
union all
select 2,'家　電',20,'mm',20.000,30.000,50.000
union all
select 2,'家　電',23,'nn',20.000,30.000,50.000
union all
select 2,'家　電',50,'kk',20.000,30.000,50.000select line,decd,dename,sel1,sel2,sel3
from @a
union all 
select line,null as decd,linename as dename,sum(sel1),sum(sel2),sum(sel3)
from @a
group by line,linename
order by line--结果
line        decd        dename               sel1                  sel2                  sel3
----------- ----------- -------------------- --------------------- --------------------- ---------------------
1           10          aa                   20.00                 30.00                 50.00
1           12          bb                   20.00                 30.00                 50.00
1           13          cc                   20.00                 30.00                 50.00
1           14          dd                   20.00                 30.00                 50.00
1           16          ee                   20.00                 30.00                 50.00
1           NULL        生　活                  100.00                150.00                250.00
2           20          mm                   20.00                 30.00                 50.00
2           23          nn                   20.00                 30.00                 50.00
2           50          kk                   20.00                 30.00                 50.00
2           NULL        家　電                  60.00                 90.00                 150.00(10 行受影响)

Answer 4

declare @a table (line int, linename varchar(20),decd int,dename varchar(20),sel1 money,sel2 money,sel3 money)
insert @a
select 1,'生　活',10,'aa',20.000,30.000,50.000
union all
select 1,'生　活',12,'bb',20.000,30.000,50.000
union all
select 1,'生　活',13,'cc',20.000,30.000,50.000
union all
select 1,'生　活',14,'dd',20.000,30.000,50.000
union all
select 1,'生　活',16,'ee',20.000,30.000,50.000
union all
select 2,'家　電',20,'mm',20.000,30.000,50.000
union all
select 2,'家　電',23,'nn',20.000,30.000,50.000
union all
select 2,'家　電',50,'kk',20.000,30.000,50.000select 
line=case when grouping(decd)=1 then '' else rtrim(line) end,
linename,isnull(dename,'')dename,
isnull(rtrim(decd),'')decd,
sum(sel1)sel1,sum(sel2)sel2,sum(sel3)sel3
from 
@a
group by line,linename,decd,dename with rollup
having not( (grouping(linename)=1 and grouping(decd)=1) or 
(grouping(dename)=1  and grouping(line)=0 and grouping(linename)=0 and grouping(decd)=0))
(8 個資料列受到影響)
line         linename             dename               decd         sel1                  sel2                  sel3
------------ -------------------- -------------------- ------------ --------------------- --------------------- ---------------------
1            生　活                  aa                   10           20.00                 30.00                 50.00
1            生　活                  bb                   12           20.00                 30.00                 50.00
1            生　活                  cc                   13           20.00                 30.00                 50.00
1            生　活                  dd                   14           20.00                 30.00                 50.00
1            生　活                  ee                   16           20.00                 30.00                 50.00
             生　活                                                    100.00                150.00                250.00
2            家　電                  mm                   20           20.00                 30.00                 50.00
2            家　電                  nn                   23           20.00                 30.00                 50.00
2            家　電                  kk                   50           20.00                 30.00                 50.00
             家　電                                                    60.00                 90.00                 150.00(10 個資料列受到影響)

Answer 5

select line,decd,dename,sel1,sel2,sel3
from @a
union all 
select line,null as decd,linename as dename,sum(sel1),sum(sel2),sum(sel3)
from @a
group by line,linename
order by line实在

Answer 6

declare @a table (line int, linename varchar(20),decd int,dename varchar(20),sel1 money,sel2 money,sel3 money)
insert @a
select 1,'生　活',10,'aa',20.000,30.000,50.000
union all
select 1,'生　活',12,'bb',20.000,30.000,50.000
union all
select 1,'生　活',13,'cc',20.000,30.000,50.000
union all
select 1,'生　活',14,'dd',20.000,30.000,50.000
union all
select 1,'生　活',16,'ee',20.000,30.000,50.000
union all
select 2,'家　電',20,'mm',20.000,30.000,50.000
union all
select 2,'家　電',23,'nn',20.000,30.000,50.000
union all
select 2,'家　電',50,'kk',20.000,30.000,50.000
select case when (decd is null) and (dename is null) then '' else rtrim(line) end as line,
          isnull(rtrim(decd),(select max(linename) from @a where line=a.line)) as decd,
          isnull(dename,'') as dename,
         sum(sel1) as sel1,
         sum(sel2) as sel2,
        sum(sel3) as sel3
from @a  as a
group by line,decd,dename with rollup
having ((dename is not null) or (decd is null and dename is null)) and line is not null/*
line         decd         dename               sel1                  sel2                  sel3                  
------------ ------------ -------------------- --------------------- --------------------- --------------------- 
1            10           aa                   20.0000               30.0000               50.0000
1            12           bb                   20.0000               30.0000               50.0000
1            13           cc                   20.0000               30.0000               50.0000
1            14           dd                   20.0000               30.0000               50.0000
1            16           ee                   20.0000               30.0000               50.0000
             生　活                               100.0000              150.0000              250.0000
2            20           mm                   20.0000               30.0000               50.0000
2            23           nn                   20.0000               30.0000               50.0000
2            50           kk                   20.0000               30.0000               50.0000
             家　電                               60.0000               90.0000               150.0000
*/