gpt4 book ai didi

MySQL join同表按列显示行(复杂)

转载 作者:行者123 更新时间:2023-11-29 02:04:36 28 4
gpt4 key购买 nike

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

tbl_用户名

id  username
1 Adam
2 Bob
3 Chris

tbl_机会

tbl_opportunity

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

enter image description here

我用下面的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`

最佳答案

我有点困惑,因为您提供的表和列与您尝试过的 SQL 中的表和列不完全匹配。无论如何,鉴于您提供的数据和表格以及您指定的结果,这应该可以帮助您。即使没有,它也应该作为一个示例,说明如何将 SUMCASE(一种非常方便的 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;

希望对您有所帮助。

关于MySQL join同表按列显示行(复杂),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8739141/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com