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

渴望私人航母 2012-09-06 11:08:04

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后面,应该怎么写呢

...全文

84 4 打赏收藏转发到动态举报

写回复

用AI写文章

4 条回复

切换为时间正序

请发表友善的回复…

发表回复

以学习为目的 2012-09-06

打赏
举报

where 条件后面加HAVING SUM()...

SQL77 2012-09-06

打赏
举报

SUM作为条件放HAVING后面

杰拉尔 2012-09-06

打赏
举报

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 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%'))

大体上这个意思

问苍天人生几何 2012-09-06

打赏
举报



-->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