代码之家  ›  专栏  ›  技术社区  ›  K.Z

如何在T-SQL中应用多个合并条件,而不让这些条件单独影响结果

  •  -1
  • K.Z  · 技术社区  · 6 年前

    我正在尝试T-SQL,若答案有价值,那个么我只需要针对问题的答案。现在对于QuestionType,如果值为null或空,则意味着没有将被填充的答案,但对于QuestionType 2,即使有答案,它也将始终为null,因为有另一个表保存该信息。

    我在下面做了,但条件排除了我不想要的问题类型2

    FROM [dbo].[MyTable] AS sur
    INNER JOIN [dbo].[SurveyQuestions] AS surQus ON sur.Id = surQus.SurveyId
    INNER JOIN [dbo].[Questions] AS qus ON surQus.QuestionId = qus.Id
    LEFT JOIN [dbo].[Responses] AS res ON res.SurveyId = sur.Id
    LEFT JOIN [dbo].[Answers] AS ans ON res.Id = ans.ResponseId AND qus.Id = ans.QuestionId 
    LEFT JOIN [dbo].[AnswerOptions] AS ansOpt ON ans.Id = ansOpt.AnswerId
    LEFT JOIN [dbo].[QuestionOptions] AS qusOpt ON ansOpt.QuestionOptionId = qusOpt.Id
     WHERE con.Id = '00000011-0013-4D34-8888-7E7189CA348U'
        AND (qus.QuestionType ='1' AND ans.Value IS NOT NULL ) //???????? NEED HELP HERE
    
    1 回复  |  直到 6 年前
        1
  •  1
  •   Dave C    6 年前

    如果我理解你的问题,你需要一个OR语句,每一组条件都在它们自己的括号中,由一组主括号包围。

    FROM [dbo].[MyTable] AS sur
    INNER JOIN [dbo].[SurveyQuestions] AS surQus ON sur.Id = surQus.SurveyId
    INNER JOIN [dbo].[Questions] AS qus ON surQus.QuestionId = qus.Id
    LEFT JOIN [dbo].[Responses] AS res ON res.SurveyId = sur.Id
    LEFT JOIN [dbo].[Answers] AS ans ON res.Id = ans.ResponseId AND qus.Id = ans.QuestionId 
    LEFT JOIN [dbo].[AnswerOptions] AS ansOpt ON ans.Id = ansOpt.AnswerId
    LEFT JOIN [dbo].[QuestionOptions] AS qusOpt ON ansOpt.QuestionOptionId = qusOpt.Id
     WHERE con.Id = '00000011-0013-4D34-8888-7E7189CA348U'
        AND (
            (qus.QuestionType ='1' AND ans.Value IS NOT NULL )
            OR
            (qus.QuestionType ='2')
            )