mysql-比较date = n和date = n-1的字符串中的值 [英] mysql - Compare values from strings for date=n with date=n-1

问题描述

我有一个像这样的数据集:

I have a dataset like:

id | country | date       | rank   | sport
--------------------------------------------
1  | US      | 2000-01-30 | 1      | tennis
2  | BE      | 2000-01-30 | 2      | tennis
3  | UK      | 2000-01-30 | 3      | tennis
4  | UK      | 2000-01-30 | 1      | golf
5  | US      | 2000-01-30 | 2      | golf
6  | FR      | 2000-01-30 | 3      | golf
7  | UK      | 2000-01-31 | 1      | tennis
8  | US      | 2000-01-31 | 2      | tennis
9  | FR      | 2000-01-31 | 3      | tennis
10 | UK      | 2000-01-31 | 1      | golf
11 | US      | 2000-01-31 | 2      | golf
12 | FR      | 2000-01-31 | 3      | golf

我想要一个类似的结果

date          country   sport     rank    rank-1   rankDiff
2000-01-31    UK        TENNIS    1       3        2
2000-01-31    US        TENNIS    2       1        -1
....

我该如何安排?选择当前日期不是问题，但是将当前日期与以前的日期进行比较并不是那么容易...

How do I arrange this? Selecting the current date is not the problem of couse, but comparing the current date with the previous is not so easy...

SELECT * FROM table WHERE date = '2000-01-31'

希望你能帮助我！

推荐答案

SELECT x.date
     , x.country
     , x.sport
     , x.rank
     , y.rank prev
     , y.rank-x.rank delta 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.country = x.country 
   AND y.sport = x.sport 
   AND y.date = '2000-01-30' 
 WHERE x.date = '2000-01-31';

这篇关于mysql-比较date = n和date = n-1的字符串中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！