我正在嘗試從 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
. . .
. . .
. . .
________________________________________________
你能幫我得到這個結果嗎？先感謝您。
uj5u.com熱心網友回復：
使用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 <= '2022-03-10'
GROUP BY DAY(date)
uj5u.com熱心網友回復：
您始終可以使用 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
“結果”正是您的查詢
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/455684.html