代码之家 › 专栏 › 技术社区 › Anthony

mysql获取上一个where条件的最大值

aggregate subquery mysql

Anthony · 技术社区 · 14 年前

我有一张桌子:

  id | score | date
 bob |    40 | 2010-1-1
 bob |    70 | 2010-1-15
 sue |    55 | 2010-1-1
 sue |    80 | 2010-2-1

我想查询某个用户在特定日期的得分,或者如果该用户在该日期没有得分,则返回该用户最近日期的得分。

有没有一种不用子查询就可以做到这一点的方法?

例如,如果我这样做:

SELECT score
FROM table
WHERE id = '$id'
AND IFNULL(
    date = DATE(FROM_UNIXTIME($date)),
    MAX(date)   
    )

我不会得到任何结果,因为ID在最近的日期不会出现。

更新

Felix提醒我不能在WHERE子句中使用聚合函数,所以现在我想知道在WHERE子句中是否有一个伪聚合日期函数来表示“最新日期”,如果有,在使用该函数时是否可以指定用户?

更新2

所以这就是我要做的,但我仍然不知道这是否是最好的方法(比如,我需要嵌套查询吗?):

SELECT score
FROM table
WHERE id = '$id'
AND date = IFNULL(
    (SELECT date FROM table
    WHERE id = '$id' AND
    date = DATE(FROM_UNIXTIME($date))
    ),
    (SELECT MAX(date) FROM table
    WHERE id = '$id'
    )
)

3 回复 | 直到 14 年前

Patrick 14 年前

不确定这一个的性能:

SELECT a.*
FROM table a
LEFT JOIN table b ON (
  b.date=DATE(FROM_UNIXTIME($date)) AND b.date=a.date AND b.id=a.id
)
WHERE a.id='$id'
ORDER BY b.date DESC, a.date DESC
LIMIT 1

aefxx 14 年前

SELECT score
  FROM table
 WHERE id = '$id'
   AND date > '2010-01-01'
 ORDER BY date DESC
 LIMIT 1

编辑

见我关于解释的第二条评论:

解释1:

SELECT score
  FROM table
 WHERE id = '$id'
   AND date >= '2010-01-01'
 ORDER BY date ASC
 LIMIT 1

解释2:

SELECT score,
       LEAST(ABS(DATEDIFF('2010-01-01', date)), 1) AS myorder
  FROM table
 WHERE id = '$id'
   AND date >= '2010-01-01'
 ORDER BY myorder ASC, date DESC
 LIMIT 1

Anthony 14 年前

使用此查询:

SELECT * FROM test
WHERE date = (
    SELECT MAX(date) FROM test
) ;

如果需要基于ID,则需要对ID列执行分组操作

推荐文章

Adrien Chapelet · MongoDB聚合与阵列内的外部模型

2 年前

In0cybe · 仅具有多行的组的和值

7 年前

Kathiravan Meeran · R-使用时间条件以及不同列上的其他条件计算平均值

7 年前

Oleg Melnikov · SQL Server:在单个查询中计算组大小和组数

7 年前

Rnovice · 根据[重复]列中的唯一值在R中聚合

7 年前

Sung min Yang · 如何创建均值+标准差数据。框架

7 年前

ajax2000 · 基于R中现有列中的组创建新列

7 年前

Collective Action · 如何按组聚合并将列添加到数据帧?[副本]

7 年前

Guidouil · MongoDB聚合,如何在组管道中添加设置数组的每个元素

7 年前

Spencer Trinh · 如何根据标签匹配来组织和移动数据行?

7 年前