mysql生成特别复杂的表格,MySQL以列方式连接相同的表格显示行（复杂）

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.