用特定条件表示MYSQL结果

Question

我需要帮助来表示我的查询结果到特定的报告与条件Here's my query result

在得到结果后，我需要将其表示为带有某些条件的报告，它将如下所示：

City A - Company A

​

​

City A - Company B

​

​

City A - Company C - Service A

​

​

我不知道该怎么做，因为我是MySQL的新手。我想把查询放到PHP中。

提前感谢

Answer 1

你可以这样做：

城市A-公司A：

select pick_date, sum(Weight), 
sum(case when site = 'SiteA' then 1 else 0 end) as SiteA, 
sum(case when site = 'SiteB' then 1 else 0 end) as SiteB, 
sum(case when site = 'SiteC' then 1 else 0 end) as SiteC
From tbl
where company_name = 'CompanyA'
group by pick_date

对于城市A-公司B，在查询中将'CompanyA‘替换为CompanyB

对于公司C-服务A：

select pick_date, sum(Weight), 
sum(case when site = 'SiteA' then 1 else 0 end) as SiteA, 
sum(case when site = 'SiteB' then 1 else 0 end) as SiteB, 
sum(case when site = 'SiteC' then 1 else 0 end) as SiteC
From tbl
where company_name = 'CompanyC' and service_name = 'ServiceA'
group by pick_date

Answer 2

这应该会起作用。如果您的原始查询也来自SQL，请将其放入一个临时表中，然后使用以下查询将得到3个答案。

SELECT
pickup_date
,SUM( CASE WHEN [site] = 'SiteA' THEN 1 ELSE 0 END) as [SiteA]  
,SUM(CASE WHEN [site] = 'SiteB' THEN 1 ELSE 0 END) as [SiteB]  
,SUM(CASE WHEN [site] = 'SiteB' THEN 1 ELSE 0 END) as [SiteC]  

FROM
tbl

WHERE
[city] = 'CityA'
AND
[company_name] = 'CompanyA'

GROUP BY
pickup_date


SELECT
pickup_date
,SUM( CASE WHEN [site] = 'SiteA' THEN 1 ELSE 0 END) as [SiteA]  
,SUM(CASE WHEN [site] = 'SiteB' THEN 1 ELSE 0 END) as [SiteB]  
,SUM(CASE WHEN [site] = 'SiteB' THEN 1 ELSE 0 END) as [SiteC]  

FROM
tbl

WHERE
[city] = 'CityA'
AND
[company_name] = 'CompanyB'

GROUP BY
pickup_date


SELECT
pickup_date
,SUM( CASE WHEN [site] = 'SiteA' THEN 1 ELSE 0 END) as [SiteA]  
,SUM(CASE WHEN [site] = 'SiteB' THEN 1 ELSE 0 END) as [SiteB]  
,SUM(CASE WHEN [site] = 'SiteB' THEN 1 ELSE 0 END) as [SiteC]  

FROM
tbl

WHERE
[city] = 'CityA'
AND
[company_name] = 'CompanyC'
AND
[service_name] = 'ServiceA'

GROUP BY
pickup_date