SQL获取所有用户的上一次搜索 [英] SQL get all of the users last search

问题描述

我可以轻松地获得用户搜索，但是我想让所有用户都进行LAST搜索.例如，下面的SQL代码将使此特定用户最后一次搜索.

I can easily get the users searches, but I would like to get all of the users LAST search. For example, the SQL code below will get this specific users last search.

select uid, itemDescription, date from SEARCH 
    WHERE date BETWEEN '2020-03-01' AND '2020-03-30' 
        AND uid = "000-000-000-000-000"
    ORDER BY date DESC
    LIMIT 1

如果我尝试对其进行编辑并删除 uid =" ，则此方法将无效，因为它将限制为一次搜索.如果我也删除了 LIMIT 1 ，并添加了 GROUP BY uid ，则它不会选择最后一个条目.

If I try to edit this and remove the uid = "", this wont work since it will limit to one search. If I remove the LIMIT 1 also and add a GROUP BY uid, its not picking up the last entry.

任何想法都将不胜感激.

Any idea would be greatly appreciated.

推荐答案

您可以使用相关子查询:

You can use a correlated subquery:

select uid, itemDescription, date
from SEARCH s
where s.date BETWEEN '2020-03-01' AND '2020-03-30' and
      s.date = (select max(s2.date)
                from search s2
                where s2.uid = s.uid and
                      s2.date = s.date
               );

为了提高性能，您需要在 search(uid，date)上建立索引.

For performance, you want an index on search(uid, date).

您还可以尝试使用窗口功能:

You can also try window functions:

select s.*
from (select s.*,
             row_number() over (partition by uid order by date desc) as seqnum
      from search s
      where .date between '2020-03-01' and '2020-03-30' 
     ) s
where seqnum = 1;

这篇关于SQL获取所有用户的上一次搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！