关于SUM作为一个条件的查询问题SQL codeSELECT INVMB.MB001 as INVMBMB001,INVMB.MB002 as INVMBMB002,INV
关于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
-->trySELECT * 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)twhere INVMCMC007<INVMBUDF57 or PUR001>INVMBUDF58
[解决办法]
- SQL code
WITH 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 SUMVALFROM 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 CTEWHERE (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()...