Subquery result in the WHERE Clause-CodePudding

I'd like to run a sql like this:

SELECT *,
       (SELECT COUNT(*)FROM ExpandQa WHERE ExpandQa.questionId = id) AS qaCount
FROM Question
WHERE (actId = 9)
  AND (flowState = 40)
  AND (((type = 0
     AND qaCount BETWEEN 1 AND 29)
     OR (type <> 0
     AND qaCount BETWEEN 1 AND 19)))
ORDER BY id DESC;

The result is --> invalid column name 'qaCount'。 My db is SQL Server.

I can fix the problem like this

SELECT *,
       (SELECT COUNT(*)FROM ExpandQa WHERE ExpandQa.questionId = id) AS qaCount
FROM Question
WHERE (actId = 9)
  AND (flowState = 40)
  AND (((type = 0
     AND (SELECT COUNT(*)FROM ExpandQa WHERE ExpandQa.questionId = id) BETWEEN 1 AND 29)
     OR (type <> 0
     AND (SELECT COUNT(*)FROM ExpandQa WHERE ExpandQa.questionId = id) BETWEEN 1 AND 19)))
ORDER BY id DESC;

But I'm afraid that it will be slower. "qaCount" is used three times. I don't hope "(SELECT COUNT(*) FROM ExpandQa WHERE ExpandQa.questionId=id)" is written three times.

Is there a good way to do that?

CodePudding user response：

One possible way to introduce computed data is a lateral join (APPLY)

SELECT *, cnt.qaCount
FROM Question q
CROSS APPLY (
  SELECT COUNT(*) AS qaCount
  FROM ExpandQa e
  WHERE e.questionId = q.id
) cnt
WHERE (actId = 9)
  AND (flowState = 40)
  AND (((type = 0
     AND cnt.qaCount BETWEEN 1 AND 29)
     OR (type <> 0
     AND cnt.qaCount BETWEEN 1 AND 19)))
ORDER BY id DESC;

CodePudding user response：

Simplify as :

WITH
T AS
(
SELECT *, COUNT(ExpandQa.questionId) OVER(PARTITION BY id) AS qaCount
FROM   Question
)
SELECT *
FROM   T
WHERE  actId = 9
  AND flowState = 40
  AND (   (type =  0 AND qaCount BETWEEN 1 AND 29)
       OR (type <> 0 AND qaCount BETWEEN 1 AND 19))
ORDER BY id DESC;

Also, this predicate

AND (   (type =  0 AND qaCount BETWEEN 1 AND 29)
       OR (type <> 0 AND qaCount BETWEEN 1 AND 19))

can be optimized with :

AND qaCount BETWEEN 1 AND CASE type WHEN 0 THEN 29 ELSE 19 END