mysql - 如何使用列表和之间选择查询

Question

我有一个 mysql 表来保存每月的故障排除票号范围。由于我将月票详细信息存储在单独的表中，如何获取我的列表票号位于其中的票范围记录？当然我可以通过PHP使用，但我必须通过mysql查询来完成。

我的示例表和数据如下:

CREATE TABLE `ticket_range` (
  `id`          int(11)              NOT NULL AUTO_INCREMENT,
  `start_range` bigint(20)  unsigned NOT NULL,
  `end_range`   bigint(20)  unsigned NOT NULL,
  `range_year`  smallint(5) unsigned NOT NULL,
  `range_month` tinyint(3)  unsigned NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ticket_range` 
(`id`, `start_range`, `end_range`, `range_year`, `range_month`) VALUES
( 1  ,          1   ,    522039  ,    2019     ,     1        ),
( 2  ,     522040   ,   1161530  ,    2019     ,     2        ),
( 3  ,    1161531   ,   1754174  ,    2019     ,     3        ),
( 4  ,    1754175   ,   2359084  ,    2019     ,     4        );

我期望查询如下:

SELECT * FROM `ticket_range` WHERE (1700000,1200000,500000) BETWEEN `start_range` AND `end_range`

给我如下结果:

id | start_range | end_range | range_year | range_month | ticket
---+-------------+-----------+------------+-------------+---------
 4 |   1754175   |  2359084  |    2019    |      3      | 1700000
 4 |   1754175   |  2359084  |    2019    |      3      | 1200000
 2 |    522040   |  1161530  |    2019    |      1      |  500000

Answer 1

检查一下你的where条件是否错误。括号中应该有一个操作数而不是3个值。所以尝试下面的查询，我已经测试过 -

SELECT id, 
       start_range, 
       end_range, 
       range_year, 
       range_month, 
       1700000 AS ticket 
FROM   `ticket_range` 
WHERE  1700000 BETWEEN `start_range` AND `end_range` 
UNION ALL 
SELECT id, 
       start_range, 
       end_range, 
       range_year, 
       range_month, 
       1200000 
FROM   `ticket_range` 
WHERE  1200000 BETWEEN `start_range` AND `end_range` 
UNION ALL 
SELECT id, 
       start_range, 
       end_range, 
       range_year, 
       range_month, 
       500000 
FROM   `ticket_range` 
WHERE  500000 BETWEEN `start_range` AND `end_range`