php 折行,php-如何将MySQL行折叠为列结果

我要在其中查询：

>附加到当前机会记录的用户的用户名

>与每个机会记录相关的销售阶段

>与机会记录相关的美元金额

我想要：

>获取当前的IF STATEMENT结果,并将其折叠

当前查询：

$sql = "SELECT u.user_name as USER,

if(o.sales_stage='Prospecting', o.amount, '') as PROSPECTING,

if(o.sales_stage='Needs Analysis', o.amount, '') as NEEDS_ANALYSIS,

if(o.sales_stage='Closed Won', o.amount, '') as CLOSED_WON

FROM opportunities o,

users u

WHERE o.assigned_user_id = u.id

GROUP BY u.user_name ";

当前结果：

USER PROSPECTING NEEDS_ANALYSIS CLOSED_WON

---------------------------------------------

chris 10000 0 0

chris 0 15000 0

chris 0 0 10000

sara 5000 0 0

sara 0 0 10000

我想要做的是折叠仅获得1个用户的结果,以及每个SalesStage各自的金额

USER PROSPECTING NEEDS_ANALYSIS CLOSED_WON

---------------------------------------------

chris 10000 15000 10000

sara 5000 0 10000

解决方法:

“崩溃”通常称为“透视”,因为您要将行转换为列状数据.采用：

SELECT u.user_name as USER,

MAX(CASE WHEN o.sales_stage = 'Prospecting' THEN o.amount END) AS PROSPECTING,

MAX(CASE WHEN o.sales_stage = 'Needs Analysis' THEN o.amount END) AS NEEDS_ANALYSIS,

MAX(CASE WHEN o.sales_stage = 'Closed Won' THEN o.amount END) AS CLOSED_WON

FROM OPPORTUNITIES o

JOIN USERS u ON u.id = o.assigned_user_id

GROUP BY u.user_name

标签：pivot,sql,mysql,php

来源： https://codeday.me/bug/20191023/1915332.html