MySQL以列方式连接相同的表格显示行（复杂） [英] MySQL join same table display rows in columns way (complicated)

问题描述

我想加入同一张表4次以获得列方式显示，我不确定是否可以在1个单独的SQL语句中执行。

tbl_username

  id用户名
 1 Adam 
 2 Bob 
 3 Chris 
  code> 

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 N CONCAT（'RM'，FORMAT（SUM（`cw`.`amount`），2））AS cw_rm_amount，
 COUNT（`cl`.name）AS`cl总数报价，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` （'nego`.date）='2012'）
 LEFT JOIN`tbl_opportunity` as cw ON（cw.`user_id` = users.id AND cw。.date）='1'
 AND YEAR 'stage'='Win'AND cw.`deleted` = 0 AND MONTH（`cw`.date）='1'
 AND YEAR（`cw`.date）='2012'）
 （'cl'.date）='1'
 AND YEAR（'cl`.date）='2012'）
 
 GROUP BY users.`username` 
  

解决方案

我有点困惑，因为您提供的表和列与您尝试过的SQL并不完全匹配。无论如何，考虑到数据&你提供的表格以及你指定的结果应该可以帮助你。即使它不是它应该作为如何结合使用 SUM 与 CASE （非常方便的SQL组合来平整数据）。

给出这个结果：

  sum（case lower when（stage）='proposal'then 1 else 0 end）asProposal Count，
 sum（case lower（stage）='建议'然后将其他金额0结束）作为建议金额，
总和（case lower when（stage）='nego'then 1 else 0 end）asNego Count，
 sum作为Nego Amount，
 sum（lower（stage）='win'then 1 else 0 end）的情况下，作为胜利计数，$ b降低（阶段）='nego' $ b $ sum（case lower when（stage）='win'then then else else end）asWin Amount，
 sum（lower（stage）='lose'then 1 else 0 end）丢失计数，
总和（当lower（stage）='lose'时，则amount其他0 end）作为丢失数量
 from tbl_username u 
 inner join tbl_opportun ity o u.id = o.user_id 
 group by u.username; 
  

希望它有帮助。

I would like to join the same table 4 times to get columns way to display, I am not sure is it possible do in 1 single SQL statements.

tbl_username

id  username
1   Adam
2   Bob
3   Chris

tbl_opportunity

I got these two table, I would like the outcome like this

I tested with following sql, but the outcome is wrong

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`

解决方案

I'm a little confused since the tables and columns that you have provided don't quite match those in the SQL that you have tried. Anyway, given the data & tables you have provided and the outcome you have specified this should help you out. Even if it doesn't it should serve as an example of how to use SUM in conjunction with CASE (a very handy SQL combination for flattening out data).

Give this a go:

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;

Hope it helps.

这篇关于MySQL以列方式连接相同的表格显示行（复杂）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！