我正在嘗試從 MySql 資料庫取得兩個查詢結果的資料透視表
這是我的 SQL 查詢：
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
這就是結果：
__________________________________ 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 . . . . . . . . . __________________________________
我想要的結果：
_______________________________________________
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
. . .
. . .
. . .
________________________________________________
你能幫我得到這個結果嗎？ 預先感謝您。
您始終可以使用 case 函數來模擬資料透視表
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其中「結果」正是您的查詢
使用
case表達式進行條件聚合：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