我正在尝试从 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 . . . . . . . . . ________________________________________________
你能帮我得到这个结果吗? 预先感谢您。
P粉6158866602024-04-04 11:27:33
您始终可以使用 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
其中“结果”正是您的查询
P粉7859577292024-04-04 11:03:26
使用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)