MySQL查询返回等于或大于某个日期的行，其中日期在年、月和日列中分隔 [英] MySQL query to return rows that are equal to or greater than a certain date, where the date is separated in year, month and day columns

问题描述

MySQL 表 EMPLOYEE 具有所有类型为 int 的列(beginyear、beginmonth、beginday、empid).

A MySQL table EMPLOYEE has columns (beginyear, beginmonth, beginday, empid) all of type int.

返回所有等于或大于日期 2009/8/13 的行的正确查询是什么?那是年、月、日.

What's a correct query to return all rows that are equal to or greater than the date 2009/8/13? That's year, month, day.

这样的查询是不正确的，因为它不会返回包含日期的行，例如 2009/9/1(在下面的 where 子句中被 beginday >=13 过滤掉)或 2010/1/14.

A query such as this is incorrect because it wouldn't return rows that contained dates such as 2009/9/1 (filtered out by beginday >=13 in where clause below) or 2010/1/14.

SELECT *
FROM EMPLOYEE
where beginyear >= 2009
  and beginmonth >= 8
  and beginday >=13

假设我无法对架构进行任何更改，并且我必须从 JDBC 创建某种查询才能获得结果.

Assume I can't make any changes to the schema and that I have to create some sort of query from JDBC to get the results.

推荐答案

对于你在三个不同领域的糟糕情况，我能做的最好的事情:

The best I could do with your bad situation of three different fields:

select *, concat(beginyear, '-',beginmonth,'-',beingday) as full_date 
  FROM TABLE 
   WHERE CONCAT(beginyear, '-',beginmonth,'-',beingday) >= '2009-08-13'

MySql 的日期时间表达式的概念有点奇怪，您可能想用 date() 函数包装 concat 以对其进行规范化.

MySql's notion of a datetime expression is sort of peculiar, you might want to wrap the concat with a date() function to normalize it.

这篇关于MySQL查询返回等于或大于某个日期的行，其中日期在年、月和日列中分隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！