表结构如下: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
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ ---------------------------------------- ----------------------------------------
所有分类 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
下面这个例子可以,但我的金额在同一个表内,如何做呢?
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
select objectTypeId,otName,
total=(select SUM(ISNULL([totalPay],0))
from vwContractOtTypeSummary
where objectTypeId like t.objectTypeId + '%')
from vwContractOtTypeSummary t
ORDER by objectTypeId???
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
似乎这个t.objectTypeId表示的是T这个表当前行的objectTypeId的sum计算,这样理解我就清楚了,thx