我有以下mysql表:
---+-------+------------+----------
id | price | e_date | item_name
---+-------+------------+----------
1 | 1000 | 2015-01-01 | pen
2 | 1050 | 2015-02-01 | pen
3 | 850 | 2015-03-01 | pen
4 | 800 | 2015-03-20 | pen
5 | 1150 | 2015-04-01 | pen
6 | 750 | 2015-02-01 | pencil
7 | 900 | 2015-03-01 | pencil
8 | 950 | 2015-03-15 | pencil
---+-------+------------+----------
如果我查询表:
"SELECT item_name,price as p,e_date FROM test_table WHERE (item_name='pen' or item_name='pencil') AND e_date<='$e_date'"
//(*Here $e_date gets from user input)
获取的结果如下:
Price of pen is 1000 on 2015-01-01 against user input: 2015-03-01
Price of pen is 1050 on 2015-02-01 against user input: 2015-03-01
Price of pen is 850 on 2015-03-01 against user input: 2015-03-01
Price of pencil is 750 on 2015-02-01 against user input: 2015-03-01
Price of pencil is 900 on 2015-03-01 against user input: 2015-03-01
但我希望结果将只包含一行每个item_name，如:
Price of pen is 850 on 2015-03-01 against user input: 2015-03-01
Price of pencil is 900 on 2015-03-01 against user input: 2015-03-01
结果应该是每个商品的最近日期的价格，该日期小于用户输入日期。如何做到这一点?
首先，您需要确定最近的日期，早于您的搜索查询。每项。
select item_name, max(e_date) e_date
from test_table
where e_date < '$e_date'
group by item_name
然后我们连接这个查询来检索剩下的结果:
select t.*
from test_table t
inner join (
select item_name, max(e_date) e_date
from test_table
where e_date < '$e_date'
group by item_name
) q
on t.item_name = q.item_name
and t.e_date = q.e_date
此处显示期望的行为