我进行了一些研究,发现这个问题与我想要的相反(他们想创建范围为汽车的完整列表)。
mysql。选择年范围
在我的情况下,我想列出几年,但我的岁月范围为范围。
更新信息
查询
SELECT make, model, begin_year, end_year, motor
FROM general_information
LIMIT 2;
(我没有在表之间放置关系以减少空间并具体)
我的数据(仅两行为简单):
|做|模型|begin_year |end_year |电机|
|雪佛兰|骑士|2002 |2005 |4 CIL 2.2 LTS |
|福特|资源管理器|1995 |1996 |6 CIL 3.3 LTS |
...
我想获得看起来像这样的mysql查询
|雪佛兰|骑士|2002 |4 CIL 2.2 LTS |
|雪佛兰|骑士|2003 |4 CIL 2.2 LTS |
|雪佛兰|骑士|2004 |4 CIL 2.2 LTS |
|雪佛兰|骑士|2005 |4 CIL 2.2 LTS |
|福特|资源管理器|1995 |6 CIL 3.3 LTS |
|福特|资源管理器|1996 |6 CIL 3.3 LTS |
我尝试了一些MySQL查询,其中涉及案例,最小值,最大值,但这些都没有给我带来理想的结果。我认为答案使用子查询,所以我将继续尝试
预先感谢!
如果您确实必须在SQL层中解决此问题,并且您不能更改general_information
表格架构以使年度数据归一化,我的建议是介绍一个新的year_list
表,加入。该表非常简单:
year_value
1900
1901
1902
...
2020
显然,您将使用适合您的多年
然后您加入此表:
SELECT
gi.make AS make,
gi.model AS model,
y.year_value AS model_year,
gi.motor AS motor
FROM general_information AS gi
INNER JOIN year_list AS y ON y.year_value BETWEEN gi.begin_year AND gi.end_year
ORDER BY make ASC, model ASC, model_year ASC, motor ASC
话虽这么说,我认为您将为您提供简单地从主表中删除年份数据并将其放入单独的表格以加入的情况下会更好。这将产生这样的结构:
general_information
-------------------
id <= create a new autoincrementing primary key field
make
model
motor
在整个品牌,型号和电机场
上提供独特的索引general_information_to_years
----------------------------
gi_id <= foreign key to id field in general_information table
year_value
您将在这两个列上提供一个复合主键
这将代表更正确归一化的数据库结构。
查询将看起来像:
SELECT
gi.make AS make,
gi.model AS model,
giy.year_value AS model_year,
gi.motor AS motor
FROM general_information AS gi
INNER JOIN general_information_to_years AS giy ON gi.id = giy.gi_id
ORDER BY make ASC, model ASC, model_year ASC, motor ASC
您可以尝试此查询。
SELECT make,model,begin_year + years.incYears as year,motor
FROM
(SELECT (y1.value + y2.value + y3.value + y4.value + y5.value) as incYears
FROM
(SELECT 0 as value UNION SELECT 1)y1,
(SELECT 0 as value UNION SELECT 2)y2,
(SELECT 0 as value UNION SELECT 4)y3,
(SELECT 0 as value UNION SELECT 8)y4,
(SELECT 0 as value UNION SELECT 16)y5
)years
CROSS JOIN general_information
WHERE begin_year+years.incYears <= end_year
ORDER BY make,year
sqlfiddle