sql - SQL中如何根据分区求和行？

Question

如何根据它的分区在行内添加值，并且总和将放在每个分区下面(每个分区下面的新行将是总和的容器)？

使用下面的链接: http://sqlfiddle.com/#!3/4e3e80/4

列名:溢价、佣金、净溢价

    col1    col2   col3

数据

    124    213    producer
     -1     12    producer

数据2

    -1    312    producer
    444    -555  producer
    100    555   producer

---

    col1    col2   col3

数据

    124    213    producer
     -1     12
    123    225    null     <<<<'result'

数据2

    -1    312    producer
    444    -555  
    100    555
    543    312   null      <<<<'result'

Answer 1

使用 ROLLUP:

这是一个包含几列的集合(仅限 Premium)

SELECT 
    (CASE rno WHEN 1 THEN EndorsementId ELSE '' END )AS col1,
    rno,
    sum(PREMIUM)
FROM
(                   
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY EndorsementId ORDER BY PolicyNumber) AS rno,
        *
    FROM [endorsement]
) As temp1
GROUP by EndorsementId, rno
with rollup

结果:

|  COL1 |    RNO | COLUMN_2 |
|-------|--------|----------|
| 13519 |      1 |      750 |
|       |      2 |        0 |
|       |      3 |        0 |
|       | (null) |      750 |
| 13524 |      1 |      157 |
|       |      2 |        0 |
|       |      3 |      158 |
|       |      4 |       16 |
|       | (null) |      331 |
. . . . . . . 

SQL Fiddle

顺便说一句，使用 Compute BY 可以获得相同的结果(它在 SQL Fiddle 中不起作用):

SELECT 
    (CASE rno WHEN 1 THEN EndorsementId ELSE '' END )AS col1,
    rno,
    PREMIUM
FROM
(                   
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY EndorsementId ORDER BY PolicyNumber) AS rno,
        *
    FROM [endorsement]
) As temp1
ORDER by EndorsementId, rno
COMPUTE SUM(Premium) BY EndorsementId

如果忽略 ROLLUP 生成的最后一行对您来说太麻烦了，那么您可以使用以下语句来生成您想要的带组总计的结果。它有点长，但做得很好。 SQL Fiddle :

SELECT 
    (CASE rno WHEN 1 THEN EndorsementId  WHEN 9999999999 THEN 'Total'ELSE '' END )AS [Endorsement ID],
     (CASE rno WHEN 1 THEN PolicyNumber  ELSE ''  END )AS [Policy Number],
      (CASE rno WHEN 1 THEN InsuredName  ELSE ''  END )AS [Insured Name],
       (CASE rno WHEN 1 THEN [temp1].[EffectiveDate]  ELSE ''  END )AS [Effective Date], 
        (CASE rno WHEN 1 THEN [temp1].[ExpirationDate]  ELSE ''  END )AS [Expiration Date], 
         (CASE rno WHEN 1 THEN [temp1].[EndorsementNumber]  ELSE ''  END )AS [Endorsement Number],
          (CASE rno WHEN 1 THEN [temp1].[EffectFromDate]  ELSE ''  END )AS [EffectFrom Date],
           (CASE rno WHEN 1 THEN [temp1].[DueDate]  ELSE ''  END )AS [Due Date],
            (CASE rno WHEN 1 THEN [temp1].[Producer]  ELSE ''  END )AS [Producer],
             Premium,
              Commission,
               NetPremium AS [Net Premium]          
FROM
(                   
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY EndorsementId ORDER BY PolicyNumber) AS rno,
        *
    FROM [endorsement]
UNION ALL
    SELECT
             9999999999 AS rno,
             EndorsementId, 
             '', 
             '', 
             '', 
             '', 
             '',
             '',
             '',
             '',
             SUM(Premium) AS Premium,
             SUM(Commission) AS Commission,
             SUM(NetPremium) AS NetPremium
    FROM [endorsement]
    GROUP BY EndorsementId
) As temp1
ORDER BY EndorsementId, rno