如何在MS-Sql server 中用数据库语句得到汇总行

create table CustomerDetail
(
CustomerID varchar(30) primary key,
CustomerName varchar(30)
)

create table OrderDetail
(
OrderID int identity,
CreatedTime Datetime,
OrderCost Decimal(20,2),
CustomerID varchar(30) foreign key references CustomerDetail(CustomerID),
)
 

--drop table OrderDetail
--drop table CustomerDetail
--select * from orderdetail,CustomerDetail where orderdetail.CustomerID = CustomerDetail.CustomerID
 
insert into CustomerDetail values ('A0000', 'Blake Ma')
insert into CustomerDetail values ('A0001', 'Eric Zhou')
insert into CustomerDetail values ('A0003', 'Candy Wang')
insert into CustomerDetail values ('A0004', 'Candy Wang2')
 
insert into orderdetail values ('2009-08-09', 10.20,'A0000')
insert into orderdetail values ('2009-08-09', 11.20,'A0000')
insert into orderdetail values ('2009-08-09', 12.20,'A0000')
insert into orderdetail values ('2009-08-09', 13.20,'A0000')
insert into orderdetail values ('2009-09-09', 14.20,'A0001')
insert into orderdetail values ('2009-09-09', 15.20,'A0001')
insert into orderdetail values ('2009-09-09', 16.20,'A0001')
insert into orderdetail values ('2009-09-09', 17.20,'A0001')
insert into orderdetail values ('2009-10-09', 18.20,'A0003')
insert into orderdetail values ('2009-10-09', 19.20,'A0003')
insert into orderdetail values ('2009-10-09', 20.20,'A0003')
insert into orderdetail values ('2009-10-09', 21.20,'A0003')
 
--注意下面语句中用到的grouping 和 end as ,以及 with rollup，所有的Customer ID 被分类，而且也包含单个用户统计和全部统计，这在很多报表里面是相当有用的，我们可以在gridview里面写对应的事件代码，可以对行进行相应的操作（加粗，颜色 等）
create procedure GetOrderDetail
 
as
--     set nocount on 会消除 row affected 的一些信息，这对于某些情况下的优化是有好处的
set nocount on
select
        case grouping(cd.CustomerID) when 0 then cd.CustomerID else '(Total)' end as MyCustID,
        case grouping(od.OrderID) when 0 then od.OrderID else -1 end as MyOrderID, --   

        sum(od.OrderCost) as Value
                from OrderDetail od ,CustomerDetail cd
                where od.CustomerID = cd.CustomerID
                group by cd.CustomerID, od.OrderID  with rollup

 

 

 

 

转自msdn 技术文章