>我在查询时遇到问题,我正在尝试根据输入的年份和月份显示数据。我在走廊上,它显示了输入的月份,但它显示了该特定月份的所有年份的数据。
PROCEDURE `date_passed`(in date date)
BEGIN
SELECT table1.name, table1.licence, table2.date
FROM table1
inner join table2
on
table2.table1_idtable1 = table1.idtable1
where MONTH (date)= MONTH(date)
and
result='passed'
使用YEAR
函数从日期中过滤年份部分
SELECT table1.NAME,
table1.licence,
table2.date
FROM table1
INNER JOIN table2
ON table2.table1_idtable1 = table1.idtable1
WHERE Month (date) = Month(input_date)
AND Year(date) = Year(input_date)
AND result = 'passed'
使用一些日期函数的更好方法。如果有的话,它将在日期列上使用索引。
SELECT table1.NAME,
table1.licence,
table2.date
FROM table1
INNER JOIN table2
ON table2.table1_idtable1 = table1.idtable1
WHERE date >= cast(Date_format(input_date, '%Y-%m-01') as date)
AND date < DATE_ADD(cast(Last_day(input_date) as date),INTERVAL 1 DAY)
AND result = 'passed'