聚合查询

表test1：
ID Money Status SiteID
1 20.00 1 1
2 10.00 1 1
3 50.00 2 1
4 40.00 3 1
5 10.00 1 2
6 10.00 2 2
7 70.00 3 2
8 20.00 4 2
9 20.00 4 3
10 20.00 1 3
11 50.00 1 3
12 20.00 1 4
13 30.00 1 5要求结果
SiteID aAmount  bAmount  cAmount
1         30.00         50.00          0
2         10.00         10.00         20.00
3         70.00         0                20.00
4         20.00         0                0
5         30.00         0                0
根据SiteID分组 
aAmount是每组中Status为1的Money和
bAmount是每组中Status为2的Money和
cAmount是每组中Status为4的Money和
Proportion是本行中bAmount除以aAmount得到的值

解决方案 »

  1.   

    select SiteID
      ,sum(case when Status = 1 then [Money] else 0 end) as aAmount
      ,sum(case when Status = 2 then [Money] else 0 end) as bAmount
      ,sum(case when Status = 4 then [Money] else 0 end) as cAmount
      ,case when sum(case when Status = 1 then [Money] else 0 end) = 0 then 0 else 
        sum(case when Status = 2 then [Money] else 0 end)/sum(case when Status = 1 then [Money] else 0 end) end as Proportion
    from test1
    group by SiteID
      

  2.   

    select siteID,sum(case when status=1 then Money else 0 end) as aAmount,
    sum(case when status=2 then Money else 0 end) as bAmount,
    sum(case when status=3 then Money else 0 end) as cAmount 
    from test1 group by siteID
      

  3.   

    select
      id as SiteID,
      sum(case Status when 1 then [Money] else 0 end) as aAmount,
      sum(case Status when 2 then [Money] else 0 end) as bAmount,
      sum(case Status when 4 then [Money] else 0 end) as cAmount
    from
      tb
    group by
      id
      

  4.   

    select
      SiteID,
      sum(case Status when 1 then [Money] else 0 end) as aAmount,
      sum(case Status when 2 then [Money] else 0 end) as bAmount,
      sum(case Status when 4 then [Money] else 0 end) as cAmount,
      sum(case Status when 2 then [Money] else 0 end)/sum(case  Status when 1 then [Money] else 0 end) end as Proportion
    from
      tb
    group by
      SiteID
      

  5.   

    ----------------------------------------------------------------------------------
    -- Author : htl258(Tony)
    -- Date   : 2010-06-04 14:12:30
    -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
    --          Jul  9 2008 14:43:34 
    --          Copyright (c) 1988-2008 Microsoft Corporation
    --          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
    -- Blog   : http://blog.csdn.net/htl258
    ------------------------------------------------------------------------------------> 生成测试数据表: [tb]
    IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
    GO
    CREATE TABLE [tb] ([ID] [int],[Money] [numeric](4,2),[Status] [int],[SiteID] [int])
    INSERT INTO [tb]
    SELECT '1','20.00','1','1' UNION ALL
    SELECT '2','10.00','1','1' UNION ALL
    SELECT '3','50.00','2','1' UNION ALL
    SELECT '4','40.00','3','1' UNION ALL
    SELECT '5','10.00','1','2' UNION ALL
    SELECT '6','10.00','2','2' UNION ALL
    SELECT '7','70.00','3','2' UNION ALL
    SELECT '8','20.00','4','2' UNION ALL
    SELECT '9','20.00','4','3' UNION ALL
    SELECT '10','20.00','1','3' UNION ALL
    SELECT '11','50.00','1','3' UNION ALL
    SELECT '12','20.00','1','4' UNION ALL
    SELECT '13','30.00','1','5'
    -->SQL查询如下:
    SELECT SiteID, ISNULL(aAmount, 0) aAmount, ISNULL(bAmount, 0) bAmount, ISNULL(cAmount, 0) cAmount 
    FROM (
             SELECT CHAR(DENSE_RANK()OVER(ORDER BY [Status])+96)+'Amount' [Status], [Money], SiteID 
             FROM [tb]
             WHERE STATUS<>3
         ) a
         PIVOT(SUM([Money]) FOR [Status] IN (aAmount, bAmount, cAmount)) b
    /*
    SiteID      aAmount                                 bAmount                                 cAmount
    ----------- --------------------------------------- --------------------------------------- ---------------------------------------
    1           30.00                                   50.00                                   0.00
    2           10.00                                   10.00                                   20.00
    3           70.00                                   0.00                                    20.00
    4           20.00                                   0.00                                    0.00
    5           30.00                                   0.00                                    0.00(5 行受影响)
    */
      

  6.   

    ----------------------------------------------------------------------------------
    -- Author : htl258(Tony)
    -- Date   : 2010-06-04 14:12:30
    -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
    --          Jul  9 2008 14:43:34 
    --          Copyright (c) 1988-2008 Microsoft Corporation
    --          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
    -- Blog   : http://blog.csdn.net/htl258
    ------------------------------------------------------------------------------------> 生成测试数据表: [tb]
    IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
    GO
    CREATE TABLE [tb] ([ID] [int],[Money] [numeric](4,2),[Status] [int],[SiteID] [int])
    INSERT INTO [tb]
    SELECT '1','20.00','1','1' UNION ALL
    SELECT '2','10.00','1','1' UNION ALL
    SELECT '3','50.00','2','1' UNION ALL
    SELECT '4','40.00','3','1' UNION ALL
    SELECT '5','10.00','1','2' UNION ALL
    SELECT '6','10.00','2','2' UNION ALL
    SELECT '7','70.00','3','2' UNION ALL
    SELECT '8','20.00','4','2' UNION ALL
    SELECT '9','20.00','4','3' UNION ALL
    SELECT '10','20.00','1','3' UNION ALL
    SELECT '11','50.00','1','3' UNION ALL
    SELECT '12','20.00','1','4' UNION ALL
    SELECT '13','30.00','1','5'
    -->SQL查询如下:
    SELECT SiteID, ISNULL(aAmount, 0) aAmount, ISNULL(bAmount, 0) bAmount, ISNULL(cAmount, 0) cAmount 
    ,Proportion=CASE ISNULL(aAmount, 0) WHEN 0 THEN 0 ELSE ISNULL(bAmount, 0)/ISNULL(aAmount, 0) END
    FROM (
             SELECT CHAR(DENSE_RANK()OVER(ORDER BY [Status])+96)+'Amount' [Status], [Money], SiteID 
             FROM [tb]
             WHERE STATUS<>3
         ) a
         PIVOT(SUM([Money]) FOR [Status] IN (aAmount, bAmount, cAmount)) b
    /*
    SiteID      aAmount                                 bAmount                                 cAmount                                 Proportion
    ----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
    1           30.00                                   50.00                                   0.00                                    1.666666
    2           10.00                                   10.00                                   20.00                                   1.000000
    3           70.00                                   0.00                                    20.00                                   0.000000
    4           20.00                                   0.00                                    0.00                                    0.000000
    5           30.00                                   0.00                                    0.00                                    0.000000(5 行受影响)
    */补充一个字段