php - MySQL 查询 - 如何根据范围值选择行

Question

这个问题有点问题。有没有办法检查一个值是否在 mysql 查询结果提供的范围内。

如何查看邮政编码 85012 是否在返回的 ZipCodes 字段内，而无需先进行查询、将其分解为多个部分并使用 BETWEEN？

+---------+-------------+--------------------+------------+-------------+
| StyleID | HistStyleID | AutobuilderStyleID | FilterRule | ZipCodes    |
+---------+-------------+--------------------+------------+-------------+
|  355755 |  2013490103 | w2013k49m1t3       | includes   | 27000-36999 |
+---------+-------------+--------------------+------------+-------------+

::更新::

该表还可以包含以下各种范围..

+---------+-------------+--------------------+------------+-------------------------------------+
| StyleID | HistStyleID | AutobuilderStyleID | FilterRule | ZipCodes                            |
+---------+-------------+--------------------+------------+-------------------------------------+
|  332492 |  2012493107 | w2012k49m31t7      | excludes   | 38600-39799,70000-71499,71600-79999 |
+---------+-------------+--------------------+------------+-------------------------------------+

Answer 1

就像评论中建议的那样，您应该重新设计数据库:

CREATE TABLE Styles (
     StyleID INT PRIMARY KEY,
     HistStyleID INT, 
     AutobuilderStyleID VARCHAR(40),
     FilterRule VARCHAR(40)
)


CREATE TABLE ZipCodes (
     StyleID INT,
     RangeBegin INT,
     RangeEnd INT,

     FOREIGN KEY StyleID REFERENCES Styles(StyleID)
)

然后您可以进行如下查询:

SELECT DISTINCT StyleID FROM ZipCodes WHERE RangeBegin >= 85012 AND RangeEnd <= 85012