如何将行限制为每天只有1个条目？

I'd like to limit my results to one row per day, that is the newest one for every day when i do:

我想将结果限制在每天一行,这是我每天最新的一行:

SELECT * FROM reports WHERE item = :item_id ORDER BY date DESC

SELECT * FROM报告WHERE item =:item_id ORDER BY日期DESC

Only 1 record per day, the records selected for each day needs to be the latest one at that day as well.

每天只有1条记录,每天选择的记录也需要是当天最新的记录。

I really have no idea what i should try. Search results gave me no directions. I am looking for a complete solution. Here is example data from my table, in JSON, selected for just a single item:

我真的不知道我应该尝试什么。搜索结果没有给我任何指示。我正在寻找一个完整的解决方案。以下是我的表中的示例数据,在JSON中,仅针对单个项目选择:

[{
        "id": "62",
        "user": "7",
        "item": "19333",
        "instant_buy": "798000",
        "instant_sell": "675000",
        "upvotes": "0",
        "downvotes": "0",
        "created": "2017-06-18 14:01:32"
    },
    {
        "id": "61",
        "user": "7",
        "item": "19333",
        "instant_buy": "899999",
        "instant_sell": "735647",
        "upvotes": "0",
        "downvotes": "0",
        "created": "2017-06-18 11:48:25"
    },
    {
        "id": "55",
        "user": "4",
        "item": "19333",
        "instant_buy": "1387166",
        "instant_sell": "1050000",
        "upvotes": "0",
        "downvotes": "0",
        "created": "2017-06-17 12:11:30"
    },
    {
        "id": "38",
        "user": "4",
        "item": "19333",
        "instant_buy": "1850000",
        "instant_sell": "900000",
        "upvotes": "0",
        "downvotes": "0",
        "created": "2017-06-16 15:48:02"
    },
    {
        "id": "36",
        "user": "1",
        "item": "19333",
        "instant_buy": "1529350",
        "instant_sell": "900000",
        "upvotes": "1",
        "downvotes": "0",
        "created": "2017-06-16 14:26:41"
    }]

3 个解决方案

#1

You coud use a join with the user and max(created) grouped by user and date()

您可以使用与用户的联接,并按用户和日期()分组max(已创建)

  SELECT * 
  FROM reports r
  INNER JOIN ( 
    select user, max(created) max_created
    from reports 
    group by user, date(created)
  )  t on t.user = r.user and t.max_created = r_created

#2

You can use GROUP BY on date column. Something similar to

您可以在日期列上使用GROUP BY。类似的东西

SELECT * FROM reports 
WHERE item = :item_id 
GROUP BY DATE_FORMAT(date,'%m-%d-%Y') 
ORDER BY date DESC

#3

try something like that:

尝试这样的事情:

select reports.* 
from reports inner join 
  (select distinct date(Date), (select ID from reports 
      where date(Date) = date(r1.Date) and item = :item_id
      order by Date desc 
      limit 1) ID 
    from Reports r1 where item = :item_id) s1 
 on reports.id = s1.id

depending if you want the first or the last entry of the date you should change the ordering the s1 subquery

根据您是否需要日期的第一个或最后一个条目,您应该更改s1子查询的顺序

#1