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

根据MySQL中最小和最大日期的记录选择差异

join sql mysql

pankaj · 技术社区 · 8 年前

下面是我的桌子,我们打个电话吧

**ID        accountID       score        tracking_date
  1             1              3      2014-09-25 00:01:05
  2             2              4      2014-09-26 01:05:18
  3             1              6      2014-09-27 09:23:05
  4             2              9      2014-09-28 20:01:05
  5             1              1      2014-09-28 23:21:34
  6             3              7      2014-09-21 00:01:00
  7             2              1      2014-09-22 01:45:24
  8             2              9      2014-09-27 14:01:43
  9             3              1      2014-09-24 22:01:27

我想选择日期最大的记录,以及与跟踪日期最小的记录之间的分数差。所以我想输出如下

  ID accountID score_with_maxdate diff_score_with_mindate  max_tracking_date
  1     1              1                     -2           2014-09-28 23:21:34
  2     2              9                      8           2014-09-28 20:01:05
  3     3              1                     -6           2014-09-24 22:01:27

有什么帮助吗?

3 回复 | 直到 8 年前

Tim Biegeleisen 8 年前

这里有一个选择。我们可以自行联接一个子查询,它可以为每个帐户查找两次原始表的最小和最大跟踪日期。这将为这些最大跟踪日期记录引入所有元数据,包括分数。

选择
T1.账户ID,
t2.得分为\u maxdate,
t2.得分-t3.得分与心态不同,
T1.最大跟踪日期
从
(
选择
账户ID,
最大(跟踪日期)作为最大跟踪日期,
最小(跟踪日期)作为最小跟踪日期
从你的表
按帐户ID分组
)T1级
内部连接表t2
在T1.accountID=T2.accountID和T2.tracking_date=T1.max_tracking_date上
内部连接表t3
在T1.accountID=T3.accountID和T3.tracking_date=T1.min_tracking_date上
排序依据
T1.账户ID;


演示

将为这些最大跟踪日期记录(包括分数)引入所有元数据。

SELECT
    t1.accountID,
    t2.score AS score_with_maxdate,
    t2.score - t3.score AS diff_score_with_mindate,
    t1.max_tracking_date
FROM
(
    SELECT
        accountID,
        MAX(tracking_date) AS max_tracking_date,
        MIN(tracking_date) AS min_tracking_date
    FROM yourTable
    GROUP BY accountID
) t1
INNER JOIN yourTable t2
    ON t1.accountId = t2.accountID AND t2.tracking_date = t1.max_tracking_date
INNER JOIN yourTable t3
    ON t1.accountId = t3.accountID AND t3.tracking_date = t1.min_tracking_date
ORDER BY
    t1.accountID;




Demo

Gordon Linoff 8 年前

这是一个有些棘手的问题。我认为条件聚合是解决问题的一种方便方法:

select min(t.id) as id, t.accountId,
       max(case when t.tracking_date = t2.max_td then t.score end) as score_with_maxdate,
       max(case when t.tracking_date = t2.max_td then t.score
                when t.tracking_date = t2.min_td then - t.score
                end) as diff_score_with_mindate,
       max(t.tracking_date) as max_tracking_date
from t join
     (select t2.accountId, min(t2.tracking_date) as min_td, max(t2.tracking_date) as max_td
      from t t2
      group by t2.accountId
     ) t2
     on t.accountId = t2.accountId
group by t.accountId;

M Khalid Junaid 8 年前

使用聚合和字符串功能获得相同结果的另一种黑客方法

select t.accountID,
  t.score_with_maxdate,
  t.score_with_maxdate - t.score_with_mindate score_with_maxdate,
  t.max_tracking_date
from(
  select accountID,
    substring_index(group_concat(score order by tracking_date desc),',', 1) + 0 score_with_maxdate,
    substring_index(group_concat(score order by tracking_date asc),',', 1) + 0 score_with_mindate,
    max(tracking_date) max_tracking_date
  from demo
  group by accountID
) t

Demo

但我建议你采用蒂姆·戈登提到的其他解决方案。