行统计问题
车次 Total Qtr1 Qtr2 Qtr3 Qtr4
------------------------------------- ----------- ----------- -------------------------------
1 50 0 50 0 0
1 55 25 30 0 0
2 1 1 20 30 0
3 85 0 85 0 0
3 60 35 0 0 25
希望得到结果
车次 Total Qtr1 Qtr2 Qtr3 Qtr4
------------------------------------- ----------- ----------- -------------------------------
1 50 0 50 0 0
1 55 25 30 0 0
合计 105 25 80 0 0
2 1 1 20 30 0
合计 1 1 20 30 0
3 85 0 85 0 0
3 60 35 0 0 25
合计 145 35 85 0 25
[解决办法]
------------------------------ Author :fredrickhu(小F，向高手学习)-- Date :2012-06-29 14:08:04-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([车次] int,[Total] int,[Qtr1] int,[Qtr2] int,[Qtr3] int,[Qtr4] int)insert [tb]select 1,50,0,50,0,0 union allselect 1,55,25,30,0,0 union allselect 2,1,1,20,30,0 union allselect 3,85,0,85,0,0 union allselect 3,60,35,0,0,25--------------开始查询--------------------------select case when grouping(车次)=0 and grouping(total)=1 then '合计' else ltrim(车次) end as 车次, sum(total) as total,sum(qtr1) as qtr1,sum(qtr2) qtr2,sum(qtr3) qtr3,sum(qtr4) as qtr4from tbgroup by 车次,Total with rolluphaving grouping(车次)=0----------------结果----------------------------/*车次 total qtr1 qtr2 qtr3 qtr4------------ ----------- ----------- ----------- ----------- -----------1 50 0 50 0 01 55 25 30 0 0合计 105 25 80 0 02 1 1 20 30 0合计 1 1 20 30 03 60 35 0 0 253 85 0 85 0 0合计 145 35 85 0 25(8 行受影响)*/