求一SQL语句

Question

表T有以下数据：
受订单号 产品编号 缴库数量 外销单价 缴库金额 销货数量 销货金额

SOB90489 1CS082719 1008 0.81 816.48 1008 816.48
SOB90489 1HN094619 600 2.98 1788 600 1788
SOB90529 1CC081301 176 6.3 1108.8 176 1108.8
SOB90591 1CM070626 2016 1.38 2782.08 0 0
SOB90591 1CM070644 1800 1.65 2970 0 0
SOB90591 1CM070652 2040 1.16 2366.4 0 0
SOB90591 1CM070656 2412 0.88 2122.56 0 0
SOB90591 1CM070660 1800 0.82 1476 0 0
SOB90591 1CM070661 960 1.2 1152 0 0
SOB90591 1CM070665 2016 0.82 1653.12 0 0我想通过查询把受订单号相同的归类合计，得到这样的结果，如何写这个语句：
受订单号 产品编号 缴库数量 外销单价 缴库金额 销货数量 销货金额

SOB90489 1CS082719 1008 0.81 816.48 1008 816.48
SOB90489 1HN094619 600 2.98 1788 600 1788
合计 1608 2604.48 1608 2604.48
SOB90529 1CC081301 176 6.3 1108.8 176 1108.8
合计 176 1108.8 176 1108.8
SOB90591 1CM070626 2016 1.38 2782.08 0 0
SOB90591 1CM070644 1800 1.65 2970 0 0
SOB90591 1CM070652 2040 1.16 2366.4 0 0
SOB90591 1CM070656 2412 0.88 2122.56 0 0
SOB90591 1CM070660 1800 0.82 1476 0 0
SOB90591 1CM070661 960 1.2 1152 0 0
SOB90591 1CM070665 2016 0.82 1653.12 0 0
合计 13044 14522.16 0 0
如何写？

Answer 1

GROUP BY+ WITH ROLLUP 

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-12-06 20:17:47
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([受订单号] varchar(8),[产品编号] varchar(9),[缴库数量] int,[外销单价] numeric(3,2),[缴库金额] numeric(6,2),[销货数量] int,[销货金额] numeric(6,2))
insert [tb]
select 'SOB90489','1CS082719',1008,0.81,816.48,1008,816.48 union all
select 'SOB90489','1HN094619',600,2.98,1788,600,1788 union all
select 'SOB90529','1CC081301',176,6.3,1108.8,176,1108.8 union all
select 'SOB90591','1CM070626',2016,1.38,2782.08,0,0 union all
select 'SOB90591','1CM070644',1800,1.65,2970,0,0 union all
select 'SOB90591','1CM070652',2040,1.16,2366.4,0,0 union all
select 'SOB90591','1CM070656',2412,0.88,2122.56,0,0 union all
select 'SOB90591','1CM070660',1800,0.82,1476,0,0 union all
select 'SOB90591','1CM070661',960,1.2,1152,0,0 union all
select 'SOB90591','1CM070665',2016,0.82,1653.12,0,0
--------------开始查询--------------------------
select 
     
    case when  grouping(受订单号)=0 then 受订单号 else '合计'  end 受订单号,
    case when  grouping(产品编号)=0 then 产品编号 else ''  end 产品编号,
    sum(缴库数量) as 缴库数量,sum(外销单价) as 外销单价,sum(缴库金额) as 缴库金额,sum(销货数量) as 销货数量,sum(销货金额) as 销货金额
from 
   tb 
group by 
   受订单号,产品编号
with rollup 
----------------结果----------------------------
/* 受订单号     产品编号      缴库数量        外销单价                                    缴库金额                                    销货数量        销货金额
-------- --------- ----------- --------------------------------------- --------------------------------------- ----------- ---------------------------------------
SOB90489 1CS082719 1008        0.81                                    816.48                                  1008        816.48
SOB90489 1HN094619 600         2.98                                    1788.00                                 600         1788.00
SOB90489           1608        3.79                                    2604.48                                 1608        2604.48
SOB90529 1CC081301 176         6.30                                    1108.80                                 176         1108.80
SOB90529           176         6.30                                    1108.80                                 176         1108.80
SOB90591 1CM070626 2016        1.38                                    2782.08                                 0           0.00
SOB90591 1CM070644 1800        1.65                                    2970.00                                 0           0.00
SOB90591 1CM070652 2040        1.16                                    2366.40                                 0           0.00
SOB90591 1CM070656 2412        0.88                                    2122.56                                 0           0.00
SOB90591 1CM070660 1800        0.82                                    1476.00                                 0           0.00
SOB90591 1CM070661 960         1.20                                    1152.00                                 0           0.00
SOB90591 1CM070665 2016        0.82                                    1653.12                                 0           0.00
SOB90591           13044       7.91                                    14522.16                                0           0.00
合计                 14828       18.00                                   18235.44                                1784        3713.28(14 行受影响)*/

Answer 3

select ... compute sum(...) by 受订单号   也可以

Answer 4

select 受订单号,产品编号,sum(缴库数量),sum(外销单价),sum(缴库金额),sum(销货数量),sum(销货金额)
from t
group by 受订单号,产品编号
with ROLLUP 

Answer 5

1> select 受订单号,产品编号,sum(缴库数量),sum(外销单价),sum(缴库金额),sum(销货数量),sum(销货金额)
2> from t
3> group by 受订单号,产品编号
4> with ROLLUP
5> go
受订单号       产品编号---------- ---------- --------- ----------- ------------- -------------- ---------------
SOB90489   1CS082719       1008      0.8100      816.4800     1008.00        816.4800
SOB90489   1HN094619        600      2.9800     1788.0000      600.00       1788.0000
SOB90489   NULL            1608      3.7900     2604.4800     1608.00       2604.4800
SOB90529   1CC081301        176      6.3000     1108.8000      176.00       1108.8000
SOB90529   NULL             176      6.3000     1108.8000      176.00       1108.8000
SOB90591   1CM070626       2016      1.3800     2782.0800         .00          0.0000
SOB90591   1CM070644       1800      1.6500     2970.0000         .00          0.0000
SOB90591   1CM070652       2040      1.1600     2366.4000         .00          0.0000
SOB90591   1CM070656       2412      0.8800     2122.5600         .00          0.0000
SOB90591   1CM070660       1800      0.8200     1476.0000         .00          0.0000
SOB90591   1CM070661        960      1.2000     1152.0000         .00          0.0000
SOB90591   1CM070665       2016      0.8200     1653.1200         .00          0.0000
SOB90591   NULL           13044      7.9100    14522.1600         .00          0.0000
NULL       NULL           14828     18.0000    18235.4400     1784.00       3713.2800(14 rows affected)

Answer 6

1> select * from tb;
2> go
受订单号 产品编号  缴库数量 外销单价 缴库金额   销货数量  销货金额
-------- --------- ----------- ----- -------- ----------- --------
SOB90489 1CS082719        1008   .81   816.48        1008   816.48
SOB90489 1HN094619         600  2.98  1788.00         600  1788.00
SOB90529 1CC081301         176  6.30  1108.80         176  1108.80
SOB90591 1CM070626        2016  1.38  2782.08           0      .00
SOB90591 1CM070644        1800  1.65  2970.00           0      .00
SOB90591 1CM070652        2040  1.16  2366.40           0      .00
SOB90591 1CM070656        2412   .88  2122.56           0      .00
SOB90591 1CM070660        1800   .82  1476.00           0      .00
SOB90591 1CM070661         960  1.20  1152.00           0      .00
SOB90591 1CM070665        2016   .82  1653.12           0      .00(10 rows affected)
1>
1> select 受订单号,产品编号,缴库数量,外销单价,缴库金额,销货数量,销货金额
2> from (
3> select 受订单号,产品编号,缴库数量,外销单价,缴库金额,销货数量,销货金额,受订单号 as k1,0 as k2
4> from tb
5> union all
6> select null,null,sum(缴库数量),null,sum(缴库金额),sum(销货数量),sum(销货金额),受订单号 as k1,1 as k2
7> from tb
8> group by 受订单号
9> ) t
10> order by k1,k2;
11> go
受订单号     产品编号      缴库数量        外销单价  缴库金额  货数量        销货金额
-------- --------- ----------- ----- ------------------------ ----------- --------------
SOB90489 1CS082719        1008   .81                   816.48     1008         816.48
SOB90489 1HN094619         600  2.98                  1788.00      600        1788.00
NULL     NULL             1608  NULL                  2604.48     1608        2604.48
SOB90529 1CC081301         176  6.30                  1108.80      176        1108.80
NULL     NULL              176  NULL                  1108.80      176        1108.80
SOB90591 1CM070626        2016  1.38                  2782.08        0            .00
SOB90591 1CM070644        1800  1.65                  2970.00        0            .00
SOB90591 1CM070652        2040  1.16                  2366.40        0            .00
SOB90591 1CM070656        2412   .88                  2122.56        0            .00
SOB90591 1CM070660        1800   .82                  1476.00        0            .00
SOB90591 1CM070661         960  1.20                  1152.00        0            .00
SOB90591 1CM070665        2016   .82                  1653.12        0            .00
NULL     NULL            13044  NULL                 14522.16        0            .00(13 rows affected)
1>

Answer 7

SELECT  CASE WHEN GROUPING(产品编号) = 1 THEN '合计' ELSE 受订单号 END,
        CASE WHEN GROUPING(产品编号) = 1 THEN ''  ELSE 产品编号  END, 
        SUM(缴库数量), SUM(外销单价), SUM(缴库金额), SUM(销货数量), SUM(销货金额)
FROM TB
GROUP BY 受订单号,产品编号 WITH ROLLUP
HAVING GROUPING(受订单号) = 0

Answer 8

GROUP BY+ WITH ROLLUP 

Answer 9

   select isnull(受订单号,'合计') .... from tb
   group by 受订单号
   with rollup

Answer 10

修改小F的代码得到：select case when  grouping(受订单号)=0 AND grouping(产品编号)=0 then 受订单号 else '合计'  end 受订单号,
    case when  grouping(受订单号)=0 AND grouping(产品编号)=0 then 产品编号 else ''  end 产品编号,
sum(缴库数量),sum(外销单价),sum(缴库金额),sum(销货数量),sum(销货金额)
from tb
group by 受订单号,产品编号
with ROLLUP 受订单号     产品编号                                                                                                              
-------- --------- ----------- --------------------------------------- --------------------------------------- ----------- ---------------------------------------
SOB90489 1CS082719 1008        0.81                                    816.48                                  1008        816.48
SOB90489 1HN094619 600         2.98                                    1788.00                                 600         1788.00
合计                 1608        3.79                                    2604.48                                 1608        2604.48
SOB90529 1CC081301 176         6.30                                    1108.80                                 176         1108.80
合计                 176         6.30                                    1108.80                                 176         1108.80
SOB90591 1CM070626 2016        1.38                                    2782.08                                 0           0.00
SOB90591 1CM070644 1800        1.65                                    2970.00                                 0           0.00
SOB90591 1CM070652 2040        1.16                                    2366.40                                 0           0.00
SOB90591 1CM070656 2412        0.88                                    2122.56                                 0           0.00
SOB90591 1CM070660 1800        0.82                                    1476.00                                 0           0.00
SOB90591 1CM070661 960         1.20                                    1152.00                                 0           0.00
SOB90591 1CM070665 2016        0.82                                    1653.12                                 0           0.00
合计                 13044       7.91                                    14522.16                                0           0.00
合计                 14828       18.00                                   18235.44                                1784        3713.28(14 row(s) affected)

Answer 11

declare @tb table([受订单号] varchar(8),[产品编号] varchar(9),[缴库数量] int,
[外销单价] numeric(3,2),[缴库金额] numeric(6,2),
[销货数量] int,[销货金额] numeric(6,2))
insert @tb
select 'SOB90489','1CS082719',1008,0.81,816.48,1008,816.48 union all
select 'SOB90489','1HN094619',600,2.98,1788,600,1788 union all
select 'SOB90529','1CC081301',176,6.3,1108.8,176,1108.8 union all
select 'SOB90591','1CM070626',2016,1.38,2782.08,0,0 union all
select 'SOB90591','1CM070644',1800,1.65,2970,0,0 union all
select 'SOB90591','1CM070652',2040,1.16,2366.4,0,0 union all
select 'SOB90591','1CM070656',2412,0.88,2122.56,0,0 union all
select 'SOB90591','1CM070660',1800,0.82,1476,0,0 union all
select 'SOB90591','1CM070661',960,1.2,1152,0,0 union all
select 'SOB90591','1CM070665',2016,0.82,1653.12,0,0 SELECT case when grouping([产品编号])=0 then 受订单号 else '合计'  end 受订单号,
 case when  grouping([产品编号])=0 then 产品编号 else ''  end 产品编号,
 sum([缴库数量]),sum([外销单价]),sum([缴库金额])
,SUM([销货数量]),sum([销货金额])
FROM @tb
GROUP BY [受订单号],[产品编号] with rollup