在sql查询中多次使用相同的参数

谢丽娜

我正在使用 pyodbc 和 Microsoft SQL Server

我正在尝试在 python 中复制一个存储过程，其中为每个 @currentSurveyId 执行此查询

SELECT *
                FROM
                (
                    SELECT
                        SurveyId,
                        QuestionId,
                        1 as InSurvey
                    FROM
                        SurveyStructure
                    WHERE
                        SurveyId = @currentSurveyId
                    UNION
                    SELECT 
                        @currentSurveyId as SurveyId,
                        Q.QuestionId,
                        0 as InSurvey
                    FROM
                        Question as Q
                    WHERE NOT EXISTS
                    (
                        SELECT *
                        FROM SurveyStructure as S
                        WHERE S.SurveyId = @currentSurveyId AND S.QuestionId = Q.QuestionId
                    )
                ) as t
                ORDER BY QuestionId

在 Python 中，我到目前为止有：

cursor.execute("""SELECT UserId FROM dbo.[User]""")
allSurveyID = cursor.fetchall()

for i in allSurveyID:
    p = i
    test = cursor.execute("""SELECT *
                FROM
                (
                    SELECT
                        SurveyId,
                        QuestionId,
                        1 as InSurvey
                    FROM
                        SurveyStructure
                    WHERE
                        SurveyId = (?)
                    UNION
                    SELECT 
                        (?) as SurveyId,
                        Q.QuestionId,
                        0 as InSurvey
                    FROM
                        Question as Q
                    WHERE NOT EXISTS
                    (
                        SELECT *
                        FROM SurveyStructure as S
                        WHERE S.SurveyId = (?)AND S.QuestionId = Q.QuestionId
                    )
                ) as t
                ORDER BY QuestionId""",p)

for i in test:
    print(i)

该参数在使用一次时有效（如果我从 UNION 开始删除所有内容）。当尝试在查询的其余部分使用相同的参数时，我收到以下错误：（'SQL 包含 3 个参数标记，但提供了 1 个参数'，'HY000'）

是否可以在同一个查询中多次使用同一个参数？

谢谢

完美的

如果重用相同的参数值，只需将参数的一项列表相乘：

cursor.execute(sql, [p]*3)

LEFT JOIN还考虑为（或）重构您的 SQL，FULL JOIN需要两个 qmark：

SELECT DISTINCT
       ISNULL(S.SurveyId, ?) AS SurveyId, 
       Q.QuestionId, 
       IIF(S.SurveyId IS NOT NULL, 1, 0) AS InSurvey 
FROM Question Q
LEFT JOIN SurveyStructure S
  ON S.QuestionId = Q.QuestionId 
  AND S.SurveyId = ?
ORDER BY Q.QuestionId

甚至可能对于一个参数：

SELECT MAX(S.SurveyId) AS SurveyId, 
       Q.QuestionId, 
       IIF(S.SurveyId IS NOT NULL, 1, 0) AS InSurvey 
FROM Question Q
LEFT JOIN SurveyStructure S
  ON S.QuestionId = Q.QuestionId 
  AND S.SurveyId = ?
GROUP BY Q.QuestionId, 
         IIF(S.SurveyId IS NOT NULL, 1, 0)
ORDER BY Q.QuestionId

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。