如何在sql查询中正确添加多个and和between?

z4bn682m  于 2021-06-15  发布在  Mysql
关注(0)|答案(5)|浏览(869)

我正在尝试构建一个具有多个依赖项的筛选函数,但是当我向sql查询添加and时,似乎找不到结果,但是如果在and之前和之后使用查询进行搜索,则会找到正确的结果。
我尝试构建一个过滤函数,用户可以在不同的范围内进行过滤,例如:价格:在100美元到200美元之间,平方英尺在30到80之间。
我的方法是这样的:

SELECT * FROM 'tableXY' WHERE value BETWEEN 100 AND 200 AND key ='price' 
AND value BETWEEN 2 AND 5 AND key = 'rooms'
AND value BETWEEN 40 AND 80 AND key = 'sqft'......and so on.

该表包含id、foreign\u id、key和value列。行中的键是price、sqft、rooms、addres等。如果我筛选price x和price y之间的某个价格范围,以及n个rooms和m个rooms之间的rooms,它应该只返回应用设置的ervery filter的结果。
在我的方法中找不到结果,如果我使用or而不是and,它还会显示其他过滤器不适用的结果。

dced5bon

dced5bon1#

对于post所指出的键值对表,您的数据是长格式的,其中不同单位类型的数据值和指示符位于两列:key和value。因此,每个 AND 条件尝试选择键和值逻辑的一个条件对,并且不能同时满足所有条件对,除非price、rooms和sqft等值以宽格式存储在它们的特定列中。
长期解决方案
在关系数据库中使用像本文这样的实体属性值模型是一个长期争论的问题。有效过滤互斥条件的长期解决方案是将表重新设计为宽泛格式,其中值位于它们自己的列中,并且查询更加容易 AND 条件。

CREATE TABLE tableXY (
    `price` DOUBLE,
    `room` INT
    `sqlft` INT,
    ... OTHER indicators ...
);

SELECT * 
FROM tableXY 
WHERE `price` BETWEEN 100 AND 200
  AND `rooms` BETWEEN 2 AND 5
  AND `sqft` BETWEEN 40 AND 80

短期解决方案
但现有长格式的短期解决方案是运行条件聚合来创建“伪列”值:price、rooms、sqft。添加 GROUP BY 对于任何静态属性(名称、位置、时间等)。

CREATE TABLE tableXY (
    `key` VARCHAR(10),
    `value` INT
);

SELECT sub.* 
FROM 
   (SELECT CASE WHEN `key` = 'name' THEN `key` ELSE NULL END AS `name`,
           SUM(CASE 
                   WHEN `key` = 'price'
                   THEN `value`
                   ELSE NULL
               END) AS price,
           SUM(CASE 
                   WHEN `key` = 'rooms' 
                   THEN `value`
                   ELSE NULL
               END) AS rooms,
           SUM(CASE 
                   WHEN `key` = 'sqlft' 
                   THEN `value
                   ELSE NULL
               END) AS sqft,

           -- ...other values...

     FROM tableXY

     GROUP BY CASE WHEN `key` = 'name' THEN `key` ELSE NULL END
  ) AS sub

WHERE sub.price BETWEEN 100 AND 200
  AND sub.rooms BETWEEN 2 AND 5
  AND sub.sqft BETWEEN 40 AND 80
nhjlsmyf

nhjlsmyf2#

如果您以类似的方式使用union:

QueryWithRoomCostFilter UNION  QueryWithRoomSizeFilter

然后,它会得到一组元组,这些元组与您的成本过滤器100$-200$匹配,并将它们添加到结果集中,然后它会使用一个房间大小为40-80平方英尺的过滤器对第二个查询求值,并将这些元组添加到结果集中,从而有效地合并(取并集)两个结果。在这种情况下,你可以看到工会正在工作,就像你有一个或之间的条件。
我不经常使用value和between,但也许可以尝试一下这些方法,如果仍然不起作用,请告诉我们。

SELECT * 
FROM table_name
WHERE (price BETWEEN 100 AND 200)
AND (rooms BETWEEN 2 AND 5)
AND (sqft BETWEEN 40 AND 80)
hmtdttj4

hmtdttj43#

解决方案一:使用 or 不同键值的条件之间。

SELECT * FROM 'tableXY' WHERE (value BETWEEN 100 AND 200 AND key ='price') 
OR (value BETWEEN 2 AND 5 AND key = 'rooms')
OR (value BETWEEN 40 AND 80 AND key = 'sqft')......and so on.

解决方案2:你需要使用 case when ```
SELECT * FROM 'tableXY'
WHERE 1 = (
case
when key ='price' and value BETWEEN 100 AND 200
then 1
when key ='rooms' and value BETWEEN 2 AND 5
then 1
.....
else 0
end
)

这就是为什么你的第一个子句没有找到记录。你最初的方法是 `AND` 在条件集之间基本上选择 `WHERE ... value BETWEEN 2 AND 5 AND... value BETWEEN 40 AND 80` 显然没有结果
xmq68pz9

xmq68pz94#

我认为您需要聚合,因为值位于不同的行中:

SELECT something
FROM tableXY
GROUP BY something 
HAVING SUM( value BETWEEN 100 AND 200 AND key ='price' ) > 0 AND
       SUM( value BETWEEN 2 AND 5 AND key = 'rooms' ) > 0 AND
       SUM( value BETWEEN 40 AND 80 AND key = 'sqft' ) > 0 AND
       ......and so on.
1yjd4xko

1yjd4xko5#

看来你需要一个合乎逻辑的 or 上每对条件之间的操作 value 以及 key :

SELECT *
FROM   tableXY
WHERE  (value BETWEEN 100 AND 200 AND key ='price' ) OR 
       (value BETWEEN   2 AND   5 AND key = 'rooms') OR
       (value BETWEEN  40 AND  80 AND key = 'sqft' ) -- etc...

注:自 and 优先级高于 or 括号不是严格需要的,但是它们确实使查询更容易阅读。

相关问题