cari

Rumah  >  Soal Jawab  >  teks badan

Gunakan UNION dalam SQL untuk memutar data pada dua hasil pertanyaan

Saya cuba mendapatkan jadual pangsi dua hasil pertanyaan daripada pangkalan data MySql

Ini adalah pertanyaan SQL saya:

SELECT * FROM (
SELECT 'NotSpot' as NotSpot,SUM(builds.Duration) as Duration , DATE_FORMAT(builds.date, "%Y-%c-%d") as date
FROM builds JOIN labels ON builds.Labels_label_id = labels.label_id
JOIN CITools ON CITools.tool_id=labels.CITools_tool_id
WHERE labels.label_name NOT LIKE '%SPOT%' AND CITools.tool_name='jenkins' AND builds.date AND builds.date >= '2022-03-01' AND builds.date <= '2022-03-10' 
GROUP BY DAY(date)

UNION ALL

SELECT 'Spot' as Spot,SUM(builds.Duration) as Duration , DATE_FORMAT(builds.date, "%Y-%c-%d") as date
FROM builds JOIN labels ON builds.Labels_label_id = labels.label_id
JOIN CITools ON CITools.tool_id=labels.CITools_tool_id
WHERE labels.label_name LIKE '%SPOT%' AND CITools.tool_name='jenkins' AND builds.date AND builds.date >= '2022-03-01' AND builds.date <= '2022-03-10' 
GROUP BY DAY(date)
) result

Inilah hasilnya:

__________________________________
NotSpot  |  Duration  |  date
________  __________   ______
NotSpot  1756343919   2022-03-01
NotSpot  1710800867   2022-03-02
NotSpot  1672806894   2022-03-03
NotSpot  859574350    2022-03-04
.         .            .
.         .            .
.         .            .
Spot      693071042     2022-03-01
Spot      728884095     2022-03-02
Spot      872995684     2022-03-03
.         .             .
.         .             .
.         .             .
__________________________________

Hasil yang saya mahukan:

_______________________________________________
NotSpot Duration  |  Spot Duration  |  date     
__________________  ________________  _________
    1756343919       1756343919       2022-03-01
    1710800867       1672806894       2022-03-02
    1672806894       859574350        2022-03-03
    859574350        4267822656       2022-03-04
       .                 .                  .
       .                 .                  .
       .                 .                  .
________________________________________________

Bolehkah anda membantu saya mendapatkan keputusan ini? Terima kasih terlebih dahulu.

P粉642436282P粉642436282263 hari yang lalu521

membalas semua(2)saya akan balas

  • P粉615886660

    P粉6158866602024-04-04 11:27:33

    Anda sentiasa boleh menggunakan fungsi kes untuk mensimulasikan jadual pangsi

    select
        sum(case result.NotSpot when 'NotSpot' then result.Duration end) as NotSpot_Duration,
        sum(case result.NotSpot when 'Spot' then result.Duration end) as Spot_Duration,
        result.date
    from result
    group by result.date

    di mana "hasil" adalah pertanyaan anda

    balas
    0
  • P粉785957729

    P粉7859577292024-04-04 11:03:26

    Penggabungan bersyaratcase menggunakan ungkapan:

    SELECT SUM(case when labels.label_name NOT LIKE '%SPOT%' then builds.Duration else 0 end) as notspot,
           SUM(case when labels.label_name LIKE '%SPOT%' then builds.Duration else 0 end) as spot, 
           DATE_FORMAT(builds.date, "%Y-%c-%d") as date
    FROM builds JOIN labels ON builds.Labels_label_id = labels.label_id
    JOIN CITools ON CITools.tool_id=labels.CITools_tool_id
    WHERE CITools.tool_name='jenkins' AND builds.date AND builds.date >= '2022-03-01' AND builds.date <= '2022-03-10' 
    GROUP BY DAY(date)

    balas
    0
  • Batalbalas