日期:2014-05-18  浏览次数:20519 次

明细+合计 的SQL语句如何写?
下面的SQL如何实现:
(原数据:)
水表编号 月 取整金额
100100 1201 20
100101 1201 5
100101 1202 8
100102 1201 10
100103 1201 5
100103 1202 6
100103 1203 5

(通过用select语句变成:)
100100 1201 20
  合计 1月 20
100101 1201 5
  1202 8
  合计 2月 13
100102 1201 10
  合计 1月 10
100103 1201 5
  1202 6
  1203 5
  合计 3月 11

要用这种查询结果做到报表中。

------解决方案--------------------
SQL code

GO
IF OBJECT_ID('tb1') IS NOT null DROP TABLE tb1
go
CREATE TABLE tb1( 水表编号 VARCHAR(10), 月 VARCHAR(10),取整金额 int)
INSERT INTO tb1
 SELECT '100100', '1201', 20 UNION ALL
SELECT '100101', '1201', 5 UNION ALL
SELECT '100101', '1202', 8 UNION ALL
SELECT '100102', '1201', 10 UNION ALL
SELECT '100103', '1201', 5 UNION ALL
SELECT '100103', '1202', 6 UNION ALL
SELECT '100103', '1203', 5

;WITH c1 AS
(
    SELECT 水表编号,月,取整金额 FROM tb1
),c2 AS 
(
    SELECT 合计=水表编号+N'合计:',col2=RIGHT(max_month,2)+N'月', sum_money FROM 
    (
        SELECT 水表编号,MAX(月) AS max_month,SUM(取整金额) AS sum_money 
        FROM tb1 
        GROUP BY 水表编号
    ) AS t        
)
SELECT * FROM (
    SELECT 水表编号,月,取整金额 FROM c1 
    UNION ALL 
    SELECT 合计,col2,sum_money FROM c2
) AS t ORDER BY 水表编号,月,取整金额
/*
水表编号          月          取整金额
------------- ---------- -----------
100100        1201       20
100100合计:  01月       20
100101        1201       5
100101        1202       8
100101合计:  02月       13
100102        1201       10
100102合计:  01月       10
100103        1201       5
100103        1202       6
100103        1203       5
100103合计:  03月       16

(11 行受影响)
*/

------解决方案--------------------
SQL code
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([水表编号] int,[月] int,[取整金额] int)
insert [tb]
select 100100,1201,20 union all
select 100101,1201,5 union all
select 100101,1202,8 union all
select 100102,1201,10 union all
select 100103,1201,5 union all
select 100103,1202,6 union all
select 100103,1203,5
go

SELECT 水表编号,月,取整金额
FROM(
SELECT 水表编号 AS PX,CASE WHEN RN=1 THEN LTRIM(水表编号) ELSE '' END AS 水表编号,LTRIM(月)  AS 月,取整金额 
FROM (SELECT *,RN=ROW_NUMBER() OVER(PARTITION BY 水表编号 ORDER BY 月) FROM TB) A
UNION ALL
SELECT 水表编号,'合计',LTRIM(COUNT(1))+'月',SUM(取整金额) AS 取整金额
FROM TB
GROUP BY 水表编号) T
ORDER BY PX,LEN(月) desc,月

/**
水表编号         月              取整金额
------------ -------------- -----------
100100       1201           20
合计           1月             20
100101       1201           5
             1202           8
合计           2月             13
100102       1201           10
合计           1月             10
100103       1201           5
             1202           6
             1203           5
合计           3月             16

(11 行受影响)
**/

------解决方案--------------------
Select T.[水表编号] ,
Case When isNull(T.[月],'') = '' Then '合计' Else Cast(T.[月] As Varchar(10)) End As [月], 
T.[取整金额]
From (Select [水表编号] ,[月] ,Sum([取整金额]) [取整金额]
From @Tb
Group By [水表编号] ,[月] With Cube
) T
Where isNull([水表编号],'') <> '' Or (isNull([水表编号],'')+isNull([月],'') = '')
------解决方案--------------------
SQL code

Declare @Tb table ([水表编号] int,[月] int,[取整金额] int)
insert Into @Tb
select 100100,1201,20 union all
select 100101,1201,5 union all
select 100101,1202,8 union all
select 100102,1201,10 union all
select 100103,1201,5 union all
select 100103,1202,6 union all
select 100103,1203,5

Select T.[水表编号] ,
Case When isNull(T.[月],'') = '' Then '合计' Else  Cast(T.[月] As Varchar(10)) End As [月], 
T.[取整金额]
From (Select [水表编号] ,[月] ,Sum([取整金额]) [取整金额]
From @Tb
Group By [水表编号] ,[月] With Cube
) T
Where isNull