php - 如何从每个项目和mysql查询中获取一行

Question

我有以下 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

结果应该是最近日期的价格，该日期小于每个项目的用户输入日期。如何实现？

Answer 1

首先，您需要确定最近的日期，早于您的搜索查询。每件元素。

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

fiddle here显示所需的行为