MySQL日期时间与上一行的比较 [英] MySQL datetime comparison with previous row

问题描述

我有一个带有两个Date列的表. DATE1有时为NULL，有时包含重复值. DATE2始终填充且唯一.我的表格是按DATE2的最新日期排序的.

I have a table with two Date columns. DATE1 is sometimes NULL and sometimes contains duplicate values. DATE2 is always populated and unique. My table is sorted by latest DATE2 date.

我想创建一个新的日期列，在该列中将选择DATE1，除非它的值与下一行重复或为NULL.在这种情况下，我想使用DATE2的值.我还需要两个布尔列来告诉我何时满足这些条件中的任何一个.让我用一个例子来演示，这样更清楚.

I'd like to create a new date column where DATE1 will be selected unless its value is duplicated from the next row or it's NULL. In this case, I want to take the value of DATE2. I also need two boolean columns that tell me when either of those conditions were met. Let me demonstrate using an example so it's clearer.

在下表中，第5行和第6行的值是 2011年7月27日，因此我想将第5行的新日期列设置为 2011年8月4日(即DATE2).在第3行中，DATE1的值为NULL，因此我想采用DATE2的值.

In the table below, row 5 and 6 have a value of Jul 27, 2011 so I'd like to set the new date column of row 5 to Aug 4, 2011 (which is DATE2). In row 3, the value of DATE1 is NULL so I want to take the value of DATE2.

我已经尝试了一些内部选择语句，但是无法使其正常工作.有什么想法吗?

I've tried a few inner select statements but can't get this to work. Any ideas?

我的表当前在数据库中的位置:

My table as it currently stands in the database:

    Row     DATE1           DATE2
    --------------------------------------
    1       Oct 10, 2011    Nov 13, 2011    
    2       Oct 10, 2011    Oct 10, 2011    
    3       NULL            Oct 8, 2011     
    4       Aug 12, 2011    Aug 12, 2011    
    5       Jul 27, 2011    Aug 4, 2011     
    6       Jul 27, 2011    Jul 28, 2011    
    7       Jul 1, 2011     Jul 26, 2011    
    8       May 24, 2011    Jun 13, 2011

我期望最终结果是什么样的:

What I expect the final result to look like:

    Row     FINAL_DATE      DATE1_DUPLICATE   DATE1_WAS_NULL
    ----------------------------------------------------------
    1       Nov 13, 2011    TRUE              FALSE   
    2       Oct 10, 2011    FALSE             FALSE
    3       Oct 8, 2011     FALSE             TRUE
    4       Aug 12, 2011    FALSE             FALSE
    5       Aug 4, 2011     TRUE              FALSE 
    6       Jul 27, 2011    FALSE             FALSE
    7       Jul 1, 2011     FALSE             FALSE
    8       May 24, 2011    FALSE             FALSE

非常感谢！

推荐答案

这可以通过顺序扫描表和使用MySQL变量来解决.您可以在(更新)中进行测试， SQL -fiddle :

This can be handled with sequential scanning of the table and using MySQL variables. You can test in (updated) SQL-fiddle:

SELECT date2
     , dd 
     , DATE_FORMAT(dd, '%b %e, %Y') AS final_date
     , date1_duplicate
     , date1_was_null
FROM
( SELECT date2
       , COALESCE( (date1 = @d OR date1 = @prev), FALSE)
           AS date1_duplicate
       , (date1 IS NULL)               AS date1_was_null
       , @d := CASE WHEN (date1 = @d OR date1 = @prev) 
                 THEN date2 
                 ELSE COALESCE(date1, date2) 
               END AS dd
       , @prev := date1 AS pre
  FROM tableX AS t
    CROSS JOIN
      ( SELECT @d := DATE('1000-01-01')
             , @prev := @d 
      ) AS dummy
  ORDER BY date2 ASC 
) AS tmp
ORDER BY date2 DESC
;

这篇关于MySQL日期时间与上一行的比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！