查询每个账号添加的男女旅客数量

SELECT user.username, SUM(CASE WHEN passenger.sex = 'male' THEN 1 ELSE 0 END) AS male_passengers, SUM(CASE WHEN passenger.sex = 'female' THEN 1 ELSE 0 END) AS female_passengers FROM passenger JOIN user ON passenger.user_id = user.user_id GROUP BY user.username