表结构如下:otname                                                                                                                                                                                                   objectTypeId                                                 totalpay                                 totalPayCheck                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ---------------------------------------- ---------------------------------------- 
所有分类                                                                                                                                                                                                     000001                                                       .00                                      .00
意向客户                                                                                                                                                                                                     000001000001                                                 49456.00                                 4796.00
成交客户                                                                                                                                                                                                     000001000002                                                 1358108.00                               33184831.00
供货商                                                                                                                                                                                                      000001000003                                                 .00                                      .00
其他                                                                                                                                                                                                       000001000004                                                 .00                                      .00
工程                                                                                                                                                                                                       000001000001000001                                           .00                                      .00
广告                                                                                                                                                                                                       000001000002000001                                           1768584.00                               677676.00使用如下语句未能得到汇总信息,如何才能正确汇总呢?
select objectTypeId,otName,total=ISNULL(SUM([totalPay]),0) from vwContractOtTypeSummary 
where objectTypeId like '000001%'
group by objectTypeId,otName
ORDER by objectTypeId

解决方案 »

  1.   

    每个子类逐级汇总到上一级别
    下面这个例子可以,但我的金额在同一个表内,如何做呢?
    DECLARE @a TABLE(No varchar(10),Name varchar(10))
    INSERT @a SELECT '101'  ,'现金'
    UNION ALL SELECT '102'  ,'银行存款'
    UNION ALL SELECT '10201','工行'
    UNION ALL SELECT '10202','建行'
    UNION ALL SELECT '10203','农行'DECLARE @b TABLE(No varchar(10),[Money] money )
    INSERT @b SELECT '101'  ,100
    UNION ALL SELECT '10201',20
    UNION ALL SELECT '10202',120--逐级汇总查询
    SELECT a.No,a.Name,
        [Money]=ISNULL(SUM([Money]),0)
    FROM @a a
        LEFT JOIN @b b ON b.No LIKE a.No+'%'
    GROUP BY a.No,a.Name
    ORDER BY a.No
      

  2.   


    select objectTypeId,otName,
       total=(select SUM(ISNULL([totalPay],0)) 
             from vwContractOtTypeSummary 
             where objectTypeId like t.objectTypeId + '%')
    from vwContractOtTypeSummary t
    ORDER by objectTypeId???
      

  3.   


    select a.objectTypeId,a.otName,total=ISNULL(SUM(b.[totalPay]),0) 
    from vwContractOtTypeSummary a left join vwContractOtTypeSummary b
          on a.objectTypeId like b.objectTypeId + '%'
    group by a.objectTypeId,a.otName
    ORDER by a.objectTypeId
      

  4.   

    把某一个objectTypeId下的所有totalpay放一起sum聚合就可以了。
      

  5.   

    where objectTypeId like t.objectTypeId + '%')
    似乎这个t.objectTypeId表示的是T这个表当前行的objectTypeId的sum计算,这样理解我就清楚了,thx