An article for the wife

The wife company has one such demand:

Query the glory of the return of the user information, when the user landed two consecutive days, the decision for the return of 2nd, if the interval of one-day landing, it was decided to return 3rd, if the interval of 5 days, the decision for the return of 7th. User data has a short interval of 14 days (fixed). Preparing Data

Database syntax is consistent with MySQL.

To create a table:

CREATE TABLE User_login (
u_id int,
login_date timestamp default current_timestamp
);

Insert data:

DELIMITER//
CREATE PROCEDURE Loop_insert ()
begin

declare days int;
declare Usrs int;
declare MX int;
declare i int;

Set days = N;
Set Usrs =;
Set mx = m;
Set i = 1;

Repeat 
    INSERT into user_login (u_id,login_date) VALUES (Floor (rand () * Usrs)), Subdate (Sysdate (), (rand () * (days+1 ))));
    Set i = i + 1;
Until I >= MX end

repeat;

end//

Call Loop_insert ();

The first thing I thought about was using Group_concat,sql:

SELECT 
    u_id, Group_concat (Distinct date_format (login_date, '%y%m%d ') Order by Date_format (login_date, '%y%m%d ') Desc separator ') as YYYYMMDD from
    user_login GROUP by
u_id;

It was not accurate to think of a date being converted to an integer subtraction (such as a cross month), and the display did not solve the requirement.

Well, consider row-and-column. row to column # #

Row to column, you need the case when enumerated, but the date is only 14 days, you can do:

SELECT u_id, Case date_format (login_date, '%y%m%d ') when ' 20180413 ' THEN login_date ELSE ' End as d20180413, Case date_format (login_date, '%y%m%d ') WH EN ' 20180414 ' THEN login_date ELSE ' end as d20180414, Case Date_format (login_dat
            E, '%y%m%d ') when ' 20180415 ' THEN login_date ELSE ' End as d20180415,
            Case Date_format (login_date, '%y%m%d ') when ' 20180416 ' THEN login_date ELSE ' 
                End as d20180416, Case date_format (login_date, '%y%m%d ') when ' 20180417 ' THEN login_date
                ELSE ' End as d20180417, Case date_format (login_date, '%y%m%d ') When ' 20180418 ' THEN login_date ELSE ' end as d20180418, Case Date_format (Login_d
         Ate, '%y%m%d ')       When ' 20180419 ' THEN login_date ELSE ' end as d20180419, Case date_format ( Login_date, '%y%m%d ') when ' 20180420 ' THEN login_date ELSE ' End as d20180420 , Case Date_format (login_date, '%y%m%d ') when ' 20180421 ' THEN login_date ELSE ' End as d20180421, Case date_format (login_date, '%y%m%d ') when ' 20180422 ' THEN l
                Ogin_date ELSE ' End as d20180422, Case date_format (login_date, '%y%m%d ') When ' 20180423 ' THEN login_date ELSE ' end as d20180423, Case Date_forma T (login_date, '%y%m%d ') when ' 20180424 ' THEN login_date ELSE ' "End as d201804 Case Date_format (login_date, '%y%m%d ') when ' 20180425 ' THEN login_date E ' ' End asd20180425, Case date_format (login_date, '%y%m%d ') when ' 20180426 ' THEN login_date ELSE ' End as d20180426 from User_login

Query results:

Merge by User

By the date of the user consolidation, go to heavy, with Max can guarantee a single data, with data set to 1 No data set to 0:

SELECT u_id, case when MAX (d20180413) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180413, Case MAX (d20180414) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180414, Case MAX (d20180415) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180415, Case MAX (d20180416) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180416, Case MAX (d20180417) = ' THEN ' 0 ' ELSE ' 1 ' End as isZ20180417, case when MAX (d20180418) = ' THEN ' 0 ' ELSE ' 1 ' End as isZ20180418, Case MAX (d20180419) = ' THEN ' 0 ' ELSE ' 1 ' End as isZ20180419, Case MAX (d20180420) = ' THEN ' 0 ' ELS E ' 1 ' end as isZ20180420, Case MAX (d20180421) = ' THEN ' 0 ' ELSE ' 1 ' End As isZ20180421, Case MAX (d20180422) = ' THEN ' 0 ' ELSE ' 1 ' EN
            D as isZ20180422, Case MAX (d20180423) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180423, Case MAX (d20180424) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180424, Case MAX (d20180425) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180425, Case MAX (d20180426) = ' THEN ' 0 ' ELSE ' 1 '
                End as isZ20180426 from (SELECT u_id, Case date_format (login_date, '%y%m%d ') When ' 20180413 ' THEN login_date ELSE ' end as d20180413, Case date_form At (login_date, '%y%m%d ')
                When ' 20180414 ' THEN login_date ELSE ' end as d20180414, Case DA  Te_format (login_date, '%y%m%d ') when ' 20180415 ' THEN login_date ELSE ' "End as"
                d20180415, Case date_format (login_date, '%y%m%d ') when ' 20180416 ' THEN login_date ELSE ' End as d20180416, Case date_format (login_date, '%y%m%d ') when ' 201804 ' THEN login_date ELSE ' end as d20180417, Case date_format (login_date, '%y%m%d ' 20180418 ' THEN login_date ELSE ' end as d20180418, case Date_format (login_date, '%y%m%d ') when ' 20180419 ' THEN login_date ELSE ' "End
                As d20180419, Case date_format (login_date, '%y%m%d ') when ' 20180420 ' THEN login_date
         ELSE ' "   End as d20180420, Case date_format (login_date, '%y%m%d ') when ' 20180421 ' THEN login_date ELSE ' End as d20180421, Case date_format (login_date, '%y%m%d ') whe N ' 20180422 ' THEN login_date ELSE ' end as d20180422, Case Date_format (login_date
            , '%y%m%d ') when ' 20180423 ' THEN login_date ELSE ' End as d20180423,
            Case Date_format (login_date, '%y%m%d ') when ' 20180424 ' THEN login_date ELSE '
                End as d20180424, Case date_format (login_date, '%y%m%d ') when ' 20180425 ' THEN login_date ELSE ' End as d20180425, Case date_format (login_date, '%y%m%d ') W HEN ' 20180426 ' THEN login_date ELSE ' end as d20180426 from User_login) t0 GR OUP by u_id

Query results:

Column Careers

Use Group_concat:

SELECT u_id, CONCAT (isZ20180413, isZ20180414, isZ20180415, isZ20180416, isZ20180417, isZ20180418, Isz
        20180419, isZ20180420, isZ20180421, isZ20180422, isZ20180423, isZ20180424, isZ20180425, isZ20180426) as Sumisz from
            (SELECT u_id, Case MAX (d20180413) = ' THEN ' 0 ' ELSE ' 1 ' 
            End as isZ20180413, Case MAX (d20180414) = ' THEN ' 0 ' ELSE ' 1 ' End as isZ20180414, case when MAX (d20180415) = ' THEN ' 0 ' ELSE '  1 ' End as isZ20180415, Case MAX (d20180416) = ' THEN ' 0 ' ELSE ' 1 ' End as isZ20180416, Case MAX (d20180417) = ' THEN ' 0 ' EL
                SE ' 1 ' end as isZ20180417, Case MAX (d20180418) = ' THEN ' 0 ' ELSE ' 1 ' End As isZ20180418, Case MAX (d20180419) = ' THEN ' 0 ' ELSE ' 1 ' E
            ND as isZ20180419, Case MAX (d20180420) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180420, Case MAX (d20180421) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180421, Case MAX (d20180422) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180422, Case MAX (d20180423) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180423, Case MAX (d20180424) = ' THEN ' 0 ' ELSE ' 1 '
            End as isZ20180424, Case MAX (d20180425) = ' THEN ' 0 ' ELSE ' 1 ' 
            End as isZ20180425, Case MAX (d20180426) = ' THEN ' 0 ' ELSE ' 1 ' End as IsZ20180426 from (SELECT u_id, Case date_format (login_date, '%y%m%d ') when ' 2 0180413 ' THEN login_date ELSE ' end as d20180413, Case date_format (login_date, '%
            y%m%d ') when ' 20180414 ' THEN login_date ELSE ' End as d20180414,
            Case Date_format (login_date, '%y%m%d ') when ' 20180415 ' THEN login_date ELSE ' End as d20180415, Case date_format (login_date, '%y%m%d ') when ' 20180416 ' THEN login_d