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.
![[Web front-end] Permulaan pantas Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
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.datedi mana "hasil" adalah pertanyaan anda
Penggabungan bersyarat
casemenggunakan 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