Share a fast solution use 'CASE-WHEN' statement


  • 1
    I

    Join tables is too expensive, sort the table and use CASE-WHEN will be faster.

    I try to use CASE-WHEN but got Wrong Answer at first.
    After debug for an hour, I found that the :='s priority is lower than AND\OR.

    It's amazing, I don't know why MySQL designed as that.

    My CASE-WHEN solution (1056 ms):

    SELECT Id
    FROM (
        SELECT Id, (
            CASE
            WHEN (@prevDate + 1 = TO_DAYS(Date)) AND (@prevTemp < Temperature) THEN ((@prevTemp := Temperature) IS NOT NULL AND (@prevDate := TO_DAYS(Date)) IS NOT NULL)
            WHEN ((@prevTemp := Temperature) IS NOT NULL AND (@prevDate := TO_DAYS(Date)) IS NOT NULL) THEN FALSE
            END
        ) flag
        FROM Weather, (SELECT @prevTemp := NULL, @prevDate := NULL) a
        ORDER BY Date
    ) c
    WHERE flag = TRUE

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.