cari

Rumah  >  Soal Jawab  >  teks badan

Dapatkan tarikh mula dan tarikh tamat masa penciptaan

Perlukan bantuan, saya perlu mendapatkan tarikh_mula dan tarikh_akhir daripada masa_penciptaan berdasarkan status is_aktif. Saya mencuba beberapa pertanyaan tetapi tidak mendapat keputusan yang betul.

Contoh bentuk

id user_id Nama nama_pemimpin sedang_aktif Masa penciptaan
6 29 DF AS 0 2021-10-10
620 29 DF RB 0 2022-02-09
1088 29 DF AS 1 2022-06-30

Hasilnya akan kelihatan seperti ini:

id user_id Nama nama_pemimpin sedang_aktif Tarikh mula Tarikh tamat Masa penciptaan
6 29 DF AS 0 2021-10-10 2022-02-09 2021-10-10
620 29 DF RB 0 2022-02-09 2022-06-30 2022-02-09
1088 29 DF AS 1 2022-06-30 CURRENT_DATE() 2022-06-30

Tolong bantu kawan-kawan, terima kasih terlebih dahulu

P粉265724930P粉265724930308 hari yang lalu468

membalas semua(1)saya akan balas

  • P粉495955986

    P粉4959559862024-02-22 17:28:02

    Berdasarkan maklumat dalam bahagian soalan dan bahagian komen, saya percaya baris dengan is_active=1 mempunyai masa penciptaan kumpulan yang paling terkini (berdasarkan user_id). Ini adalah pertanyaan yang ditulis dan diuji dalam meja kerja.

    select id,user_id,name,leader_name,is_active,
    t1.creation_time as start_date, case is_active when 0 then t2.creation_time else current_date() end as end_date,t1.creation_time
    from (select id,user_id,name,leader_name,is_active,creation_time,@row_id:=@row_id+1 as row_id
        from test,(select @row_id:=0)t
        where user_id=29
        order by creation_time
        )t1
    left join
        (select creation_time,@row_num:=@row_num+1 as row_num
        from test,(select @row_num:=0)t
        where user_id=29
        order by creation_time
        )t2
    on t1.row_id+1=t2.row_num
    ;
    
    -- result set:
    # id, user_id, name, leader_name, is_active, start_date, end_date, creation_time
    6, 29, DF, AS, 0, 2021-10-10, 2022-02-09, 2021-10-10
    620, 29, DF, RB, 0, 2022-02-09, 2022-06-30, 2022-02-09
    1088, 29, DF, AS, 1, 2022-06-30, 2022-08-31, 2022-06-30

    Perkara belum selesai. Jika anda ingin memaparkan output berdasarkan setiap kumpulan user_id, gunakan kod berikut:

    -- first of all insert the following 4 lines on top of the original table data, which has the same user_id 50 
    61  50  DF  AS  0   2021-10-10
    630 50  DF  RB  0   2022-02-09
    1188    50  DF  TS  0   2022-06-30
    2288    50  DF  AS  1   2022-07-30
    
    select  id,t1.user_id,name,leader_name,is_active,
    t1.creation_time as start_date, case is_active when 0 then t2.creation_time else current_date() end as end_date,t1.creation_time
    from 
     (select id,user_id,name,leader_name,is_active,creation_time,@row_id:=@row_id+1 as row_id
        from test,(select @row_id:=0)t
        order by user_id,creation_time
        )t1
    left join
        (select user_id,creation_time,@row_num:=@row_num+1 as row_num
        from test,(select @row_num:=0)t
        order by user_id,creation_time
        )t2
    on t1.user_id=t2.user_id and t1.row_id+1=t2.row_num
    ;
    
    -- result set:
    # id, user_id, name, leader_name, is_active, start_date, end_date, creation_time
    6, 29, DF, AS, 0, 2021-10-10, 2022-02-09, 2021-10-10
    620, 29, DF, RB, 0, 2022-02-09, 2022-06-30, 2022-02-09
    1088, 29, DF, AS, 1, 2022-06-30, 2022-08-31, 2022-06-30
    61, 50, DF, AS, 0, 2021-10-10, 2022-02-09, 2021-10-10
    630, 50, DF, RB, 0, 2022-02-09, 2022-06-30, 2022-02-09
    1188, 50, DF, TS, 0, 2022-06-30, 2022-07-30, 2022-06-30
    2288, 50, DF, AS, 1, 2022-07-30, 2022-08-31, 2022-07-30

    balas
    0
  • Batalbalas