别名CASE中的子查询

I have a simple INSERT query which also contains the following code for one of the columns values.

VALUES( ..... ,
CASE WHEN hand < (SELECT AVG(ss.hand) AS r FROM sales ss WHERE ss.year=:year)
     THEN (r - hand)
     ELSE 0
     END)";

The goal here is to use the subquery result average (aliased as 'r') inside the THEN. Problem is, I'm getting an error saying the column r was not found when running the query. If I remove the 'r' from the THEN, it runs fine. I don't want to duplicate the sub query into the THEN since this would harm the performance.

I've also tried to alias the whole subquery, but that caused a syntax error.

Any hints on this? I'm new to SQL and have been struggling with this for hours now.

You are going to have to repeat the expression:

(CASE WHEN hand < (SELECT AVG(ss.hand) FROM sales ss WHERE ss.year=:year)
     THEN ((SELECT AVG(ss.hand) FROM sales ss WHERE ss.year=:year) - hand)
     ELSE 0
 END)

You could also express this as:

greatest(0, (SELECT AVG(ss.hand) FROM sales ss WHERE ss.year=:year) - hand) )

Another alternative is to use select . . . insert and define this expression in the from clause.