MySQL 选择单独的年份和月份列

Question

我无法理解这个问题......

创建表并转储一些数据到其中

CREATE TABLE `_xxx` (
  `x` DECIMAL(10,5) NULL DEFAULT NULL,
  `x_year` INT(4) NULL DEFAULT NULL,
  `x_month` INT(2) NULL DEFAULT NULL);

insert into  _xxx (x, x_year, x_month) values (10,2010,1);
insert into  _xxx (x, x_year, x_month) values (20,2010,2);
insert into  _xxx (x, x_year, x_month) values (30,2010,3);
insert into  _xxx (x, x_year, x_month) values (40,2010,4);
insert into  _xxx (x, x_year, x_month) values (50,2010,5);
insert into  _xxx (x, x_year, x_month) values (60,2010,6);
insert into  _xxx (x, x_year, x_month) values (70,2010,7);
insert into  _xxx (x, x_year, x_month) values (80,2010,8);
insert into  _xxx (x, x_year, x_month) values (90,2010,9);
insert into  _xxx (x, x_year, x_month) values (11,2011,1);
insert into  _xxx (x, x_year, x_month) values (21,2011,2);
insert into  _xxx (x, x_year, x_month) values (31,2011,3);
insert into  _xxx (x, x_year, x_month) values (41,2011,4);
insert into  _xxx (x, x_year, x_month) values (51,2011,5);
insert into  _xxx (x, x_year, x_month) values (61,2011,6);
insert into  _xxx (x, x_year, x_month) values (71,2011,7);
insert into  _xxx (x, x_year, x_month) values (81,2011,8);
insert into  _xxx (x, x_year, x_month) values (91,2011,9);
insert into  _xxx (x, x_year, x_month) values (12,2012,1);
insert into  _xxx (x, x_year, x_month) values (22,2012,2);
insert into  _xxx (x, x_year, x_month) values (32,2012,3);
insert into  _xxx (x, x_year, x_month) values (42,2012,4);
insert into  _xxx (x, x_year, x_month) values (52,2012,5);
insert into  _xxx (x, x_year, x_month) values (62,2012,6);
insert into  _xxx (x, x_year, x_month) values (72,2012,7);
insert into  _xxx (x, x_year, x_month) values (82,2012,8);
insert into  _xxx (x, x_year, x_month) values (92,2012,9);

现在尝试选择日期之前的所有内容和日期之后的所有内容(两个日期之间的所有内容)...

SELECT * FROM _xxx WHERE x_year >= 2011 AND x_month >= 7

当然这是不正确的......

有什么指示吗？

谢谢。

Answer 1

虽然很尴尬，但确实有效

SELECT *,DATE(CONCAT(x_year,'-',x_month,'-',01))dateColumn 
FROM _xxx 
HAVING dateColumn BETWEEN '2011-04-01' AND '2012-06-01'

Fiddle

您可以使用相同的公式创建 DATE 字段，以便对其建立索引。