database - Laravel - 查询生成器选择具有唯一列值的多行(具有来自另一列的最大值)

Question

我有一张这样的 table

data | usage_date | usage_hour
x    | 03/03/2016 | 05:30:30
y    | 03/02/2016 | 11:30:30
z    | 03/03/2016 | 07:30:30
p    | 03/02/2016 | 05:30:30

当我运行 Laravel 查询时，我希望看到以下行被选中

y    | 03/02/2016 | 11:30:30
z    | 03/03/2016 | 07:30:30

所以基本上我想构建一个查询，它将为“usage_date”提供唯一值，最大为“usage_hour”。如何构建此查询？

Answer 1

首先，您需要知道如何在纯 SQL 中执行此操作，因为 Laravel 的 QueryBuilder 只是用于构建 SQL 查询的工具。

您描述的任务有点棘手，不幸的是没有简短易用的 SQL 查询。

可以用window functions来完成:

SELECT DISTINCT usage_date, 
    first_value(usage_hour) OVER (PARTITION BY usage_date ORDER BY usage_hour DESC) as usage_hour, 
    first_value(data) OVER (PARTITION BY usage_date ORDER BY usage_date DESC, usage_hour DESC) as data
FROM t

( sqlfiddle example )

使用 QueryBuilder 它将如下所示:

DB::table('t')
    ->distinct()
    ->select([
        'usage_date',
        DB::raw('first_value(usage_hour) OVER (PARTITION BY usage_date ORDER BY usage_hour DESC) as usage_hour'),
        DB::raw('first_value(data) OVER (PARTITION BY usage_date ORDER BY usage_date DESC, usage_hour DESC) as data')
    ])
    ->get()

---

或者可以用子查询来完成(效率低下而且不酷):

SELECT usage_date, max(usage_hour) as usage_hour,
  (SELECT data FROM t AS t2 
   WHERE t2.usage_date = t.usage_date AND t2.usage_hour = max(t.usage_hour) 
  LIMIT 1) AS data
FROM t
GROUP BY usage_date

---

如果有人知道没有子查询和窗口函数的方法，请告诉我。