[MariaDB 10.4,PHP8.0]我如何重写它,使它从具有MAX(total_weight)的行中获取weight_date,并将其列为highest_weight_date?我读到过不能在WHERE中使用MAX()?我已经测试过重写几个示例,但我放弃了,并尴尬地展示了我的最新尝试:
weight_date HAVING total_weight=MAX(total_weight) AS highest_weight_date
我尝试将其添加到该文件中,但出现错误。https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9c057570bd60cdf20a7148189a77fdc4
SELECT *
, LEAD(total_weight, 1) OVER(
ORDER BY weight_date DESC
) AS prev_total_weight
, LEAD(weight_date, 1) OVER(
ORDER BY weight_date DESC
) AS prev_total_weight_date
, MIN(total_weight) OVER() AS lowest_weight
, MAX(total_weight) OVER() AS highest_weight
, FROM_UNIXTIME(weight_date, '%u') AS weight_week
, ROW_NUMBER() OVER(
ORDER BY weight_date DESC
) AS RowNum
FROM (
SELECT *, weight_start_week + weight_end_week AS total_weight
FROM YourTable
) t
ORDER BY RowNum
谢谢
2条答案
按热度按时间x8goxv8g1#
要获取highest_weight_date(total_weight最高的行中的weight_date),只需将以下内容添加到select中:
但是我建议使用
ORDER BY total_weight DESC, weight_date
或ORDER BY total_weight DESC, weight_date DESC
,这样当权重出现在多个日期时,您就可以确定地获得具有该权重的第一个或最后一个日期,而不是任意的一个。fiddle
fwzugrvs2#
我读到过我们不能在WHERE中使用MAX()。
也许这会有所帮助:使用子查询在WHERE子句中查找MAX()权重。使用示例数据...
DBfiddle