嵌套SQL AND OR语句失败.我错在哪里?

Question

我想要得到纬度在x和y值之间或者地址等于xyz的数据集。但这失败了:

... 
Running query: 
SELECT 
title,description,adress,likesAmount,price,hasVideo,thumbnail,colorId, colorId2, colorId3,conditionId,materialId, materialId2, materialId3 ,verified,classic,subcategoryId,categoryId,dateUpdatedInt,brandId,userId,tblAddID,timeCreated,timeUpdated,isSold, soldToUserID            
FROM 10299_12188_tblAdds 
Where soldToUserID=0  
AND (price >= null AND price <= null) AND (latitude >= 0 AND latitude <= 0) OR (adress = null) AND (longitude >= 0 AND longitude <= 0) OR (adress = null) AND (dateUpdatedInt >= null) AND (classic = null) AND (conditionId = null) AND (verified = null) AND (categoryId = null) AND (subcategoryId = null OR subcategoryId = null OR subcategoryId = null)  AND (materialId = null OR materialId2 = null OR materialId3 = null OR materialId = null OR materialId2 = null OR materialId3 = null OR materialId = null OR materialId2 = null OR materialId3 = null)  AND (brandId = null OR brandId = null OR brandId = null)  ORDER BY price ASC LIMIT null, null

我错在哪里?

谢谢!

Answer 1

你需要改变这部分，因为现在你更新了你的完整查询，也许还有其他部分:

AND (
latitude >= 0
AND latitude <= 0
)
OR (adress = null)
AND (
longitude >= 0
AND longitude <= 0
)
OR (adress = null)

to this:

AND (
((latitude >= 0  AND latitude <= 0)    
AND (longitude >= 0 AND longitude <= 0))      
OR (adress is null)
)

Answer 2

这是我的尝试，我不确定这些语句在没有更多上下文的情况下为您做了什么。(latitude >= 0 AND latitude <= 0)，我的困惑是如果这个数小于等于0，也大于等于0，它是所有的数。你是说这个不为空吗?

SELECT title,
description,
adress,
likesAmount,
price,
hasVideo,
thumbnail,
colorId,
colorId2,
colorId3,
conditionId,
materialId,
materialId2,
materialId3,
verified,
classic,
subcategoryId,
categoryId,
dateUpdatedInt,
brandId,
userId,
tblAddID,
timeCreated,
timeUpdated,
isSold,
soldToUserID
FROM 10299 _12188_tblAdds
WHERE 1=1
AND soldToUserID = 0
AND PRICE IS NOT NULL
AND dateUpdatedInt IS NOT NULL
AND classic IS NULL
AND conditionId IS NULL
AND verified IS NULL
AND categoryId IS NULL
AND (
subcategoryId IS NULL
OR subcategoryId IS NULL
OR subcategoryId IS NULL
)
AND (
materialId IS NULL
OR materialId2 IS NULL
OR materialId3 IS NULL
OR materialId IS NULL
OR materialId2 IS NULL
OR materialId3 IS NULL
OR materialId IS NULL
OR materialId2 IS NULL
OR materialId3 IS NULL
)
AND (
brandId IS NULL
OR brandId IS NULL
OR brandId IS NULL
)
ORDER BY price ASC LIMIT NULL,
NULL

Answer 3

根据您在这里询问的内容，用于纬度和地址检查的WHERE子句将看起来像@x和@y是数值:

AND 
(
(latitude BETWEEN @x AND @y) 
OR 
(adress = 'xyz') 
)

WHERE子句上的其他东西也需要修复。and和or没有正确分组。例如，空检查将如下所示

AND Price IS NOT NULL

不是

AND (price >= null AND price <= null)