MySQL连接同一个表以列方式显示行（复杂）

Question

我想连接同一个表 4 次以获取显示列的方式，我不确定是否可以在 1 个 SQL 语句中完成。

tbl_username

id  username
1   Adam
2   Bob
3   Chris

tbl_opportunity

我得到了这两个表，我想要这样的结果

我用以下sql进行了测试，但结果是错误的

SELECT users.`username`, COUNT(`proposal`.name) AS `Pro Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`proposal`.`amount`), 2)) AS pro_rm_amount, 
COUNT(`nego`.name) AS `nego Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`nego`.`amount`), 2)) AS nego_rm_amount, 
COUNT(`cw`.name) AS `cw Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`cw`.`amount`), 2)) AS cw_rm_amount,
COUNT(`cl`.name) AS `cl Total Number Quotation`, CONCAT('RM', FORMAT(SUM(`cl`.`amount`), 2)) AS cl_rm_amount

FROM tbl_username users
JOIN `tbl_opportunity` AS proposal ON (proposal.`user_id` = users.id AND proposal.`sales_stage` = 'Proposal' AND proposal.`deleted`=0 AND MONTH(`proposal`.date)= '1'
AND YEAR(`proposal`.date)= '2012')
LEFT JOIN `tbl_opportunity` AS nego ON (proposal.`user_id` = users.id AND proposal.`sales_stage` = 'Nego' AND nego.`deleted`=0 AND MONTH(`nego`.date)= '1'
AND YEAR(`nego`.date)= '2012')
LEFT JOIN `tbl_opportunity` AS cw ON (cw.`user_id` = users.id AND cw.`stage` = 'Win' AND cw.`deleted`=0 AND MONTH(`cw`.date)= '1'
AND YEAR(`cw`.date)= '2012')
LEFT JOIN `tbl_opportunity` AS cl ON (cl.`user_id` = users.id AND cl.`stage` = 'Lose' AND cl.`deleted`=0 AND MONTH(`cl`.date)= '1'
AND YEAR(`cl`.date)= '2012')

GROUP BY users.`username`

Answer 1

我有点困惑，因为您提供的表和列与您尝试过的 SQL 中的表和列不太匹配。无论如何，鉴于数据和您提供的表格以及您指定的结果应该对您有所帮助。即使没有，它也应该作为如何将 SUM 与 CASE（一种非常方便的用于展平数据的 SQL 组合）结合使用的示例。

尝试一下：

select u.username,
sum(case when lower(stage) = 'proposal' then 1 else 0 end) as "Proposal Count"  ,
sum(case when lower(stage) = 'proposal' then amount else 0 end) as "Proposal Amount",
sum(case when lower(stage) = 'nego' then 1 else 0 end) as "Nego Count"  ,
sum(case when lower(stage) = 'nego' then amount else 0 end) as "Nego Amount",
sum(case when lower(stage) = 'win' then 1 else 0 end) as "Win Count"  ,
sum(case when lower(stage) = 'win' then amount else 0 end) as "Win Amount",
sum(case when lower(stage) = 'lose' then 1 else 0 end) as "Lose Count"  ,
sum(case when lower(stage) = 'lose' then amount else 0 end) as "Lose Amount"
from tbl_username u
inner join tbl_opportunity o on u.id = o.user_id
group by u.username;

希望有帮助。