在SQL查询方面仍然是个初学者。我需要从带有日期字段的表中选择字段,例如从去年10月1日到今天。
类似的东西
SELECT field FROM table WHERE table.date BETWEEN ***last october*** and CURDATE()
那么,我能在去年10月的中加入什么来代替它,让它在一年后不再接触它?
我会检查当前月份是否为十月->十二月在这种情况下,我可以得到当年的十月,否则,我会得到去年的十月:
-- returns <current year>-10-01 if we are in octobre, november or december, else <current year - 1>-10-01
SELECT CONCAT(IF(MONTH(CURDATE()) >= 10, YEAR(CURDATE()), YEAR(CURDATE()) - 1),'-10-01');
SELECT field
FROM table
WHERE table.date BETWEEN
CONCAT(IF(MONTH(CURDATE()) >= 10, YEAR(CURDATE()), YEAR(CURDATE()) - 1),'-10-01')
and
CURDATE()
您需要运行两个子查询:
a( 当月:
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 11 |
+--------------+
b( 获取比较年份,例如,如果今年10月1日过去,则返回当前年份,否则为前一年:
mysql> select case when month(now()) >= 10 then year(now()) else year(now()) - 1 end;
+------------------------------------------------------------------------+
| case when month(now()) >= 10 then year(now()) else year(now()) - 1 end |
+------------------------------------------------------------------------+
| 2020 |
+------------------------------------------------------------------------+
c( 最后10月1日的施工日期:
mysql> select concat(case when month(now()) >= 10 then year(now()) else year(now()) - 1 end, '-10-01');
+------------------------------------------------------------------------------------------+
| concat(case when month(now()) >= 10 then year(now()) else year(now()) - 1 end, '-10-01') |
+------------------------------------------------------------------------------------------+
| 2020-10-01 |
+------------------------------------------------------------------------------------------+
最后一个查询是去年10月的替换
因此,最后的查询是:
SELECT field FROM table WHERE table.date BETWEEN CONCAT(CASE WHEN MONTH(NOW()) >= 10 THEN YEAR(NOW()) ELSE YEAR(NOW()) - 1 END, '-10-01') AND DATE(NOW())