关于SUM作为一个条件的查询有关问题-MSSQL教程-爱易网页

关于SUM作为一个条件的查询有关问题

日期：2014-05-17　浏览次数：20454 次

关于SUM作为一个条件的查询问题

SQL code

SELECT INVMB.MB001 as INVMBMB001,INVMB.MB002 as INVMBMB002,INVMB.MB003 
as INVMBMB003,INVMB.MB067 as INVMBMB067,INVMB.MB032 AS INVMBMB032,PURMA.MA002 
AS PURMAMA002,INVMB.UDF57 as INVMBUDF57,INVMB.UDF58 as INVMBUDF58,INVMC.MC007 
as INVMCMC007,INVMC.MC007+SUM(PURTD.TD008-PURTD.TD015) as PUR001  
FROM INVMB as INVMB  
Left JOIN INVMC as INVMC On INVMB.MB001=INVMC.MC001 and INVMB.MB017=INVMC.MC002 
Left JOIN PURMA as PURMA On INVMB.MB032=PURMA.MA001 
Left JOIN PURTD as PURTD On INVMC.MC001=PURTD.TD004  
WHERE (INVMC.MC007<INVMB.UDF57 AND INVMC.MC002=INVMB.MB017 
AND (MC001 LIKE'PF%' OR MC001 LIKE'BF%')) 
OR (PURTD.TD016 = 'N' AND INVMC.MC007+SUM(PURTD.TD008-PURTD.TD015)>INVMB.UDF58 AND INVMC.MC002=INVMB.MB017  
AND (MC001 LIKE'PF%' OR MC001 LIKE'BF%'))

我想实现这样的功能但是SUM又不能在WHERE后面,应该怎么写呢

------解决方案--------------------

SQL code


-->try
SELECT * FROM
(
    SELECT INVMB.MB001 as INVMBMB001,
    INVMB.MB002 as INVMBMB002,
    INVMB.MB003 as INVMBMB003,
    INVMB.MB067 as INVMBMB067,
    INVMB.MB032 AS INVMBMB032,
    PURMA.MA002 AS PURMAMA002,
    INVMB.UDF57 as INVMBUDF57,
    INVMB.UDF58 as INVMBUDF58,
    INVMC.MC007 as INVMCMC007,
    INVMC.MC007+SUM(PURTD.TD008-PURTD.TD015) as PUR001  
    FROM INVMB as INVMB  
    Left JOIN INVMC as INVMC On INVMB.MB001=INVMC.MC001 and INVMB.MB017=INVMC.MC002 
    Left JOIN PURMA as PURMA On INVMB.MB032=PURMA.MA001 
    Left JOIN PURTD as PURTD On INVMC.MC001=PURTD.TD004  
    WHERE (INVMC.MC007<INVMB.UDF57 AND INVMC.MC002=INVMB.MB017 AND (MC001 LIKE'PF%' OR MC001 LIKE'BF%')) 
    OR (PURTD.TD016 = 'N' AND INVMC.MC007+SUM(PURTD.TD008-PURTD.TD015)>INVMB.UDF58 
        AND INVMC.MC002=INVMB.MB017 AND (MC001 LIKE'PF%' OR MC001 LIKE'BF%'))
    group by INVMB.MB001,INVMB.MB002,INVMB.MB003,INVMB.MB067,INVMB.MB032,
    PURMA.MA002,INVMB.UDF57,INVMB.UDF58,INVMC.MC007
)t
where INVMCMC007<INVMBUDF57 or PUR001>INVMBUDF58

------解决方案--------------------
SQL codeWITH CTE AS(
SELECT INVMB.MB001 as INVMBMB001,INVMB.MB002 as INVMBMB002,INVMB.MB003 
as INVMBMB003,INVMB.MB067 as INVMBMB067,INVMB.MB032 AS INVMBMB032,PURMA.MA002 
AS PURMAMA002,INVMB.UDF57 as INVMBUDF57,INVMB.UDF58 as INVMBUDF58,INVMC.MC007 
as INVMCMC007,INVMC.MC007+SUM(PURTD.TD008-PURTD.TD015) as PUR001  ,
SUM(PURTD.TD008-PURTD.TD015) as SUMVAL
FROM INVMB as INVMB  
Left JOIN INVMC as INVMC On INVMB.MB001=INVMC.MC001 and INVMB.MB017=INVMC.MC002 
Left JOIN PURMA as PURMA On INVMB.MB032=PURMA.MA001 
Left JOIN PURTD as PURTD On INVMC.MC001=PURTD.TD004  

)
SELECT * FROM CTE
WHERE (INVMC.MC007<INVMB.UDF57 AND INVMC.MC002=INVMB.MB017 
AND (MC001 LIKE'PF%' OR MC001 LIKE'BF%')) 
OR (PURTD.TD016 = 'N' AND INVMC.MC007+SUMVAL>INVMB.UDF58 AND INVMC.MC002=INVMB.MB017  
AND (MC001 LIKE'PF%' OR MC001 LIKE'BF%'))

------解决方案--------------------
SUM作为条件放HAVING后面

------解决方案--------------------
where 条件后面加HAVING SUM()...

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

关于SUM作为一个条件的查询有关问题

相关资料更多>

推荐阅读更多>