怎样在sqlserver2005中实现小计、合计

Question

用存储过程或其他方式实现小计和合计的报表统计,提供思路也行，前台展示我用的是GridView，在后台代码或控件中操作也行，谢了
例：表table1
id  orderNo  standardTime   amount  productNo 
1   20090101       8           20      001
2   20090101       6          30        002
3   20090101       10          50       003
  .
  .
  .
4  20090102       8           50        004
5   20090102      12          30        005
6   20090102       10          10       006
  .
  .
  .
先分别统计orderNo 为20090101、20090102... 的standardTime*amount  的小计
然后统计所有orderNo 的standardTime*amount 德合计
如下：id  orderNo  standardTime   amount  productNo  perTotal
1   20090101       8           20      001     160
2   20090101       6          30        002     180
3   20090101       10          50       003     500
小计                                            840  
  .
  .
4  20090102       8           50        004      400
5   20090102      12          30        005      360
6   20090102       10          10       006      100
小计                                             860
  .
  .
  .合计                                             1700

Answer 1

create table #t(a int,b int,c int,d int,e int) 
insert into #t values(1,2,3,4,5) 
insert into #t values(1,2,3,4,6) 
insert into #t values(1,2,3,4,7) 
insert into #t values(1,2,3,4,8) 
insert into #t values(1,3,3,4,5) 
insert into #t values(1,3,3,4,6) 
insert into #t values(1,3,3,4,8) 
insert into #t values(1,3,3,4,7) insert into #t values(2,2,2,4,5) 
insert into #t values(2,2,3,4,6) 
insert into #t values(2,2,4,4,7) 
insert into #t values(2,2,5,4,8) 
insert into #t values(2,3,6,4,5) 
insert into #t values(2,3,3,4,6) 
insert into #t values(2,3,3,4,8) 
insert into #t values(2,3,3,4,7)select 
  case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, sum(c) as c,sum(d) as d,sum(e) as e
from 
  #t 
group by 
  a,b 
with rollup 
  having grouping(b)=0 or grouping(a)=1select 
  case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, c, sum(d) as d,sum(e) as e
from 
  #t 
group by 
  a,b,c 
with rollup 
  having grouping(c)=0 or grouping(a)=1 select 
  case when grouping(a)=1 then '合计' else cast(a as varchar) end a, b, 
  case when grouping(c)=1 and grouping(b)=0 then '小计' else cast(c as varchar) end c, 
  sum(d) as d,sum(e) as e
from 
  #t 
group by 
  a,b,c 
with rollup 
  having grouping(a)=1 or grouping(b)=0 select 
   case when grouping(a)=1 then '合计' else cast(a as varchar) end a, 
   case when grouping(b)=1 and grouping(a)=0 then '小计' else cast(b as varchar) end b, sum(c) as c, sum(d) as d,sum(e) as e from #t 
group by 
   a,b,c 
with rollup 
   having grouping(a)=1 or grouping(b)=1 or grouping(c)=0drop table #t/*a                              b           c           d           e           
------------------------------ ----------- ----------- ----------- ----------- 
1                              2           12          16          26
1                              3           12          16          26
2                              2           14          16          26
2                              3           15          16          26
合计                             NULL        53          64          104（所影响的行数为 5 行）a                              b           c           d           e           
------------------------------ ----------- ----------- ----------- ----------- 
1                              2           3           16          26
1                              3           3           16          26
2                              2           2           4           5
2                              2           3           4           6
2                              2           4           4           7
2                              2           5           4           8
2                              3           3           12          21
2                              3           6           4           5
合计                             NULL        NULL        64          104（所影响的行数为 9 行）a                              b           c                              d           e           
------------------------------ ----------- ------------------------------ ----------- ----------- 
1                              2           3                              16          26
1                              2           小计                             16          26
1                              3           3                              16          26
1                              3           小计                             16          26
2                              2           2                              4           5
2                              2           3                              4           6
2                              2           4                              4           7
2                              2           5                              4           8
2                              2           小计                             16          26
2                              3           3                              12          21
2                              3           6                              4           5
2                              3           小计                             16          26
合计                             NULL        NULL                           64          104（所影响的行数为 13 行）a                              b                              c           d           e           
------------------------------ ------------------------------ ----------- ----------- ----------- 
1                              2                              12          16          26
1                              3                              12          16          26
1                              小计                             24          32          52
2                              2                              2           4           5
2                              2                              3           4           6
2                              2                              4           4           7
2                              2                              5           4           8
2                              3                              9           12          21
2                              3                              6           4           5
2                              小计                             29          32          52
合计                             NULL                           53          64          104（所影响的行数为 11 行）*/

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-09-03 19:50:10
-- Verstion:
--      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(4),[学期] int,[语文] int,[数学] int,[英语] int,[政治] int)
insert [tb]
select '张三',1,70,60,80,30 union all
select '张三',2,80,90,75,40 union all
select '张三',3,50,70,85,60 union all
select '李四',1,66,80,90,55 union all
select '李四',2,75,70,85,65
--------------开始查询--------------------------
select 
    case when grouping(姓名)=1 then '合计' else cast(姓名 as varchar) end 姓名, 
    case when grouping(学期)=1 and grouping(姓名)=0 then '小计' else cast(学期 as varchar) end 学期, 
    sum(语文) as 语文,sum(数学) as 数学,sum(英语) as 英语,sum(政治) as 政治
from 
   tb 
group by 
   姓名,学期 
with rollup ------------------------------------------------------结果-------------------------------------------------------
/*姓名                             学期                             语文          数学          英语          政治
------------------------------ ------------------------------ ----------- ----------- ----------- -----------
李四                             1                              66          80          90          55
李四                             2                              75          70          85          65
李四                             小计                             141         150         175         120
张三                             1                              70          60          80          30
张三                             2                              80          90          75          40
张三                             3                              50          70          85          60
张三                             小计                             200         220         240         130
合计                             NULL                           341         370         415         250(8 行受影响)
 */

Answer 3

if object_id('tb') is not null drop table tb
create table tb(id int, orderNo varchar(10), standardTime int,  amount int, productNo varchar(10))
go
insert tb  
select 
1,  '20090101',      8 ,         20,      001 union all select 
2,  '20090101',      6  ,        30 ,       002 union all select 
3,  '20090101',      10  ,        50 ,     003 union all select 
4,  '20090102',      8    ,      50   ,     004 union all select 
5,  '20090102',      12    ,      30   ,     005 union all select 
6,  '20090102',      10     ,     10    ,  006  select a.id,a.standardTime*a.amount as pertotal
  from tb a group by a.id,a.orderNo,a.productNo,a.standardTime,a.amount with rollup
  having(GROUPING(amount)=1 and grouping(productno)=1)select a.id,orderNo=case when productNo IS null then '合计' else orderno end,
       a.standardTime,a.amount,a.productNo,a.pertotal
from 
(
   select a.*,a.standardTime*a.amount as pertotal,s1=0,s2=0
    from tb a
      union all 
   select  '',orderNo,sum(standardTime) as standardTime,sum(amount) as amount,'',sum(standardTime*amount) as pertotal,
         s1=0,s2=1
    from tb group by orderNo
      union all 
   select '','总计',sum(standardTime) as standardTime,sum(amount) as amount,'',sum(standardTime*amount) as pertotal,
        s1=1,s2=0
      from tb
 )a
   order by s1,orderNo,s2
  /*
  id orderNo standardTime amount productNo pertotal
1 20090101 8 20 1 160
2 20090101 6 30 2 180
3 20090101 10 50 3 500
0 20090101 24 100 840
4 20090102 8 50 4 400
5 20090102 12 30 5 360
6 20090102 10 10 6 100
0 20090102 30 90 860
0 总计 54 190 1700
*/

Answer 4

-->Title:生成測試數據
-->Author:wufeng4552
-->Date :2009-09-10 16:26:59
 
declare @T table([id] int,[orderNo] Datetime,[standardTime] int,[amount] int,[productNo] nvarchar(3))
Insert @T
select 1,'20090101',8,20,N'001' union all
select 2,'20090101',6,30,N'002' union all
select 3,'20090101',10,50,N'003' union all
select 4,'20090102',8,50,N'004' union all
select 5,'20090102',12,30,N'005' union all
select 6,'20090102',10,10,N'006'
select * from (
select ltrim([id])[ID],
       [orderNo], 
       [standardTime],
       [amount],
       [productNo],  
       perTotal=standardTime*amount
from @t 
union all
select  
       '小計',[orderNo],null,null,null,sum(standardTime*amount)
from @t
group by [orderNo]
union all
select '總計',null,null,null,null,sum(standardTime*amount) from @t 
)t 
order by isnull([orderNo],99999),case when charindex('小計',id)>0 then 1 else 0 end
/*
ID           orderNo                 standardTime amount      productNo perTotal
------------ ----------------------- ------------ ----------- --------- -----------
1            2009-01-01 00:00:00.000 8            20          001       160
2            2009-01-01 00:00:00.000 6            30          002       180
3            2009-01-01 00:00:00.000 10           50          003       500
小計           2009-01-01 00:00:00.000 NULL         NULL        NULL      840
4            2009-01-02 00:00:00.000 8            50          004       400
5            2009-01-02 00:00:00.000 12           30          005       360
6            2009-01-02 00:00:00.000 10           10          006       100
小計           2009-01-02 00:00:00.000 NULL         NULL        NULL      860
總計           NULL                    NULL         NULL        NULL      1700(9 個資料列受到影響)
*/

Answer 5

/*
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)   Jul  9 2008 14:43:34   Copyright (c) 
1988-2008 Microsoft Corporation  Enterprise Evaluation Edition on Windows NT 5.1 <X86> 
(Build 2600: Service Pack 3) 
 愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-10 16:54:42.327●●●●●
　★★★★★soft_wsx★★★★★
*/if object_id('tb') is not null drop table tb
create table tb(id int, orderNo varchar(10), standardTime int,  amount int, productNo varchar(10))
go
insert tb  
select 
1,  '20090101',      8 ,         20,      001 union all select 
2,  '20090101',      6  ,        30 ,       002 union all select 
3,  '20090101',      10  ,        50 ,     003 union all select 
4,  '20090102',      8    ,      50   ,     004 union all select 
5,  '20090102',      12    ,      30   ,     005 union all select 
6,  '20090102',      10     ,     10    ,  006  select a.id,a.standardTime*a.amount as pertotal
  from tb a group by a.id,a.orderNo,a.productNo,a.standardTime,a.amount with rollup
  having(GROUPING(amount)=1 and grouping(productno)=1)select a.id,日期=case when isnull(orderNo,'')=''  then '总计' 
                         when isnull(productno,'')='' and isnull(orderNo,'')<>'' then '小计'
                          else orderno end,
       a.standardTime,a.amount,a.productNo,a.pertotal
from 
(
   select a.*,a.standardTime*a.amount as pertotal,s1=0,s2=0
    from tb a
      union all 
   select  '',orderNo,sum(standardTime) as standardTime,sum(amount) as amount,'',sum(standardTime*amount) as pertotal,
         s1=0,s2=1
    from tb group by orderNo
      union all 
   select '','',sum(standardTime) as standardTime,sum(amount) as amount,'',sum(standardTime*amount) as pertotal,
        s1=1,s2=0
      from tb
 )a
   order by s1,orderNo,s2
  /*
id 日期 standardTime amount productNo pertotal
1 20090101 8 20 1 160
2 20090101 6 30 2 180
3 20090101 10 50 3 500
0 小计 24 100 840
4 20090102 8 50 4 400
5 20090102 12 30 5 360
6 20090102 10 10 6 100
0 小计 30 90 860
0 总计 54 190 1700
*/

Answer 6

--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] int,[orderNo] NVARCHAR(8),[standardTime] int,[amount] int,[productNo] nvarchar(4))
INSERT [tb]
SELECT 1,'20090101',8,20,N'001' UNION ALL
SELECT 2,'20090101',6,30,N'002' UNION ALL
SELECT 3,'20090101',10,50,N'003' UNION ALL
SELECT 4,'20090102',8,50,N'004' UNION ALL
SELECT 5,'20090102',12,30,N'005' UNION ALL
SELECT 6,'20090102',10,10,N'006'
GO
--SELECT * FROM [tb]-->SQL查询如下:
SELECT id,orderNo,standardTime,amount,productNo,perTotal 
FROM (
SELECT *,perTotal=[standardTime]*[amount],px1=orderNo,px2=0
FROM tb a
UNION ALL
SELECT NULL,'小计',NULL,NULL,NULL,perTotal=SUM([standardTime]*[amount]),[orderNo],1
FROM tb 
GROUP BY [orderNo]
UNION ALL
SELECT NULL,'合计',NULL,NULL,NULL,perTotal=SUM([standardTime]*[amount]),'99999999',2
FROM tb 
) AS t
ORDER BY px1,px2
/*
id          orderNo  standardTime amount      productNo perTotal
----------- -------- ------------ ----------- --------- -----------
1           20090101 8            20          001       160
2           20090101 6            30          002       180
3           20090101 10           50          003       500
NULL        小计       NULL         NULL        NULL      840
4           20090102 8            50          004       400
5           20090102 12           30          005       360
6           20090102 10           10          006       100
NULL        小计       NULL         NULL        NULL      860
NULL        合计       NULL         NULL        NULL      1700(9 行受影响)
*/

Answer 7

declare @tb table (id int,orderno datetime,standardtime int,amount int,productno int)
insert into @tb select 1,'20090101',8,20,1
    union all   select 2,'20090101',6,30,2
    union all   select 3,'20090101',10,50,3
    union all   select 4,'20090102',8,50,4
    union all   select 5,'20090102',12,30,5
    union all   select 6,'20090102',10,10,6
;with china as
(
 select *,pertotoal=standardtime*amount from @tb
)
select id= case when grouping(pertotoal)=0 then max(id) else '' end,
       orderno,pertotoal=sum(pertotoal),
       amout=case when grouping(pertotoal)=0 then MAX(amount) else '' end,
       productno=case when grouping(pertotoal)=0 then MAX(productno) else '' end from china
       group by  orderno,pertotoal 
       with rollup
 
(6 行受影响)
id          orderno                 pertotoal   amout       productno
----------- ----------------------- ----------- ----------- -----------
1           2009-01-01 00:00:00.000 160         20          1
2           2009-01-01 00:00:00.000 180         30          2
3           2009-01-01 00:00:00.000 500         50          3
0           2009-01-01 00:00:00.000 840         0           0
6           2009-01-02 00:00:00.000 100         10          6
5           2009-01-02 00:00:00.000 360         30          5
4           2009-01-02 00:00:00.000 400         50          4
0           2009-01-02 00:00:00.000 860         0           0
0           NULL                    1700        0           0(9 行受影响)

Answer 8

我记得2008里已经开始放弃ROLLUP了啊，还能用么？

Answer 9

这样查询出来了 jsp页面显示如何处理啊 我打算用jstl