我正在使用Pyodbc访问Microsoft SQL Server数据库。我在代码中构建了以下查询:
SELECT created, transactionID, balanceEffect
FROM dbo.table
WHERE (SELECT YEAR(created) = 2020)
AND (SELECT MONTH(created) = 11)
AND (SELECT DAY(created) = 17)
AND balanceEffect > 0
AND TransactionTypeID = '1321837129837129213'
ORDER BY created
其中,created
是DATE
列。
当它开始执行查询时,我看到以下错误:
编程错误:('42000',"[42000][Microsoft][SQL Server Native Client 11.0][SQL Server]'='附近的语法不正确。(102((SQLExecDirectW(;[42000][Microsoft][SQL Server Native Client 11.0][SQL Server]'='附近的语法不正确。(102(;[42000][Microsoft][SQL Server Native Client 11.0][SQL Server]'='附近的语法不正确。(102("(
子查询的内容似乎有问题。我还没能在网上找到类似的问题,我相信这段代码(或一段非常相似的代码(以前在这个项目中也工作过。有人知道是什么原因造成的吗?
SELECT
s不正确。你只想:
WHERE YEAR(created) = 2020 AND
MONTH(created) = 11
DAY(created) = 17 AND
balanceEffect > 0 AND
TransactionTypeID = '1321837129837129213'
或者更简单地说:
WHERE CAST(created as DATE) = '2020-11-17' AND
balanceEffect > 0 AND
TransactionTypeID = '1321837129837129213'
子查询没有意义,也不需要。只需对照文字日期检查created
,如下所示:
SELECT created, transactionID, balanceEffect
FROM dbo.table
WHERE
created >= '20201107' and created < '20201108'
AND balanceEffect > 0
AND TransactionTypeID = '1321837129837129213'
ORDER BY created
您可能希望将日期筛选条件简化为:
CONVERT(date, created) = '20201107'
然而,这效率较低:整个created
列需要转换为日期才能进行筛选;这不能利用索引。这就是为什么我建议使用半开放间隔筛选,如第一个查询所示。
当然,如果created
没有时间分量,那么你只需要做:
created = '20201107'