检查SQL查询中是否满足指定的所有条件

Emptypeace 发表于 Dev

空荡荡的和平

假设我有以下派生表：

Comment | Condition_Lower_Score | Condition_Higher_Score | Question_Score
=========================================================================
text1   |          1            |            3           |       2
text1   |          3            |            5           |       4
text2   |          5            |            6           |       1
text2   |          3            |            6           |       4

我的桌子上有一条注释，该注释与条件具有一对多的关系。每个条件可以指定多个问题（在此表中，问题分数与不同问题相关）。我需要创建一个仅在满足所有条件的情况下才选择注释的查询。

派生表是从以下表创建的：

评论：

Comment_ID | Comment_Text 
===========================
     1     |     text1
     2     |     text2

健康）状况：

 Condition_ID | Condition_Lower_Score | Condition_Higher_Score |  Comment_ID | Question_ID
 =========================================================================================
      10      |          1            |             3          |      1      |     100
      11      |          3            |             5          |      1      |     101
      12      |          5            |             6          |      2      |     102
      13      |          3            |             6          |      2      |     103

问题：

Question_ID | Question_Score
============================
     100    |       2
     101    |       4
     102    |       1
     103    |       4

因此，在这种情况下，我希望仅从派生表中选择“ text1”，而不希望从“ text2”中选择，因为不满足其所有条件。

如何创建仅在满足所有条件时才选择的查询？

詹尼斯·帕拉斯科沃波洛斯

WITH TestsCTE AS
(
SELECT M.Comment_Text AS Comment, 
       C.Condition_Lower_Score, 
       C.Condition_Higher_Score, 
       Q.Question_Score,
       CASE 
           WHEN Q.Question_Score BETWEEN C.Condition_Lower_Score AND C.Condition_Higher_Score
           THEN 1
       ELSE 0
       END AS Pass
FROM   [Condition] C
       JOIN Comment M
           ON C.Comment_ID = M.Comment_ID
       JOIN Question Q
           ON C.Question_ID = Q.Question_ID
)
SELECT COMMENT
FROM TestsCTE
GROUP BY COMMENT
HAVING MIN(Pass) = 1