select*from(selectsum(kills)as 孙悟空 from user1 a join user_kills b on a.id = b.`user_id`and a.`user_name`='孙悟空') a crossjoin(selectsum(kills)as 猪八戒 from user1 a join user_kills b on a.id = b.`user_id`and a.`user_name`='猪八戒') b crossjoin(selectsum(kills)as 沙僧 from user1 a join user_kills b on a.id = b.`user_id`and a.`user_name`='沙僧') c
使用case-when
selectsum(casewhen user_name ='孙悟空'then kills end)as'孙悟空',sum(casewhen user_name ='猪八戒'then kills end)as'猪八戒',sum(casewhen user_name ='沙僧'then kills end)as'沙僧'from user1 a join user_kills b on a.id = b.user_id
需要进行行转列的场景:姓名打怪数猪八戒10猪八戒2猪八戒12沙僧3沙僧5沙僧1熏悟空20熏悟空10熏悟空17熏悟空猪八戒沙僧47249使用cross join效率不高,随着人数增加sql越来越复杂。 select * from(select sum(kills) as 孙悟空 from user1 a join user_kills b on a.id = b.`use