SumID Qty
'A-Total' 45 --A属于表2 从'10'到'20'的和
'B-Total' 35 --B就是等于单个'35'的合计 12?
'c-Total' 90(35+10+45) --合计就是'30'到'40'的合计加 '50'加 'A-Total'
10+12+13+(10)+(10+20) ?
'A-Total' 45 --A属于表2 从'10'到'20'的和
'B-Total' 35 --B就是等于单个'35'的合计 12?
'c-Total' 90(35+10+45) --合计就是'30'到'40'的合计加 '50'加 'A-Total'
10+12+13+(10)+(10+20) ?
'A-Total' 45 --A属于表2 从'10'到'20'的和
'B-Total' 35 --B就是等于单个'35'的合计 12?
'c-Total' 90(35+10+45) --合计就是'30'到'40'的合计加 '50'加 'A-Total'
10+12+13+(10)+(10+35)
按照楼主的意思,下列语句应该OK的。
--CTE
;with LI as
(
SELECT SUM(CASE WHEN SUM1 <>'' AND SUM2 <>''
THEN (SELECT SUM(ISNULL(QTY,0)) FROM @detailInfo
WHERE CAST([NAME] AS INT) BETWEEN CAST(SUM1 AS INT) AND CAST(SUM2 AS INT))
WHEN ISNUMERIC(SUM3) = 1
THEN (SELECT ISNULL(QTY,0) FROM @detailInfo WHERE [NAME] = SUM3)
ELSE 0 END) AS MARK,SUM3,SUMID
FROM @tableSumInfo) LO) --查询结果
SELECT SUM(CASE WHEN ISNUMERIC(T.SUM3) = 0
THEN (SELECT SUM(ISNULL(MARK,0)) FROM LI WHERE T.SUMID = SUMID)
ELSE MARK END) AS MARK,SUMID
FROM LI T
GROUP BY SUMID
declare @detailInfo table (fName NVARCHAR(10),QTY INT)
--汇总办法信息表.....
INSERT INTO @tableSumInfo
SELECT 'A-Total',1,'10','20','' UNION ALL
SELECT 'B-Total',1,'','','35' UNION ALL --存在单一的,前面的范围字段是为空的.
SELECT 'C-Total',1,'30','40','' UNION ALL
SELECT 'C-Total',2,'','','50' UNION ALL
SELECT 'C-Total',3,'','','A-Total' --可以去上面的合计字段..INSERT INTO @detailInfo
SELECT '10',10 UNION ALL
SELECT '20',35 UNION ALL
SELECT '30',10 UNION ALL
SELECT '35',12 UNION ALL
SELECT '40',13 UNION ALL
SELECT '50',10 select sumid, onesum ,
(select isnull(sum(qty),0) from @detailInfo where fname between t.sum1 and t.sum2 or fname=onesum) fsum into #t
from @tableSumInfo tselect sumid,sum(fonesum+fsum) from (
select sumid,isnull((select fsum from #t where sumid=t.onesum),0) fonesum,fsum from #t t) t
group by sumiddrop table #t/*
A-Total 45
B-Total 12
C-Total 90
*/