mysql 与上一行比较,MySQL日期时间与上一行的比较

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.

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.

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

Thanks so much!

解决方案

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

;