我正在使用 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] 删除。
我来说两句