时间戳

首页 > 解决方案 > Sql 查询计算性能

php - Sql 查询计算性能

问题描述

我有以下数据库架构..

-- Companies
id
name

--financial_report
id
company_id
quarter (Q1, Q2, Q3, Q4)
fiscal_year (2018, 2017)
revenue
employees

我想获得基于季度和财政年度的公司的以下结果..

2018 年第三季度的样本结果

+-------------+-----------+---------+
| CompanyName | Employees | Revenue |
+-------------+-----------+---------+
| Company1    |        12 |  50,000 |
+-------------+-----------+---------+

SQLFIDDLE：http ://sqlfiddle.com/#!9/9813bf

谢谢

标签： phpmysqllaravel

解决方案

您可以使用条件聚合Group By：

SELECT 
  fr.fiscal_year, 
  fr.quarter, 
  c.name AS CompanyName, 
  SUM(fr.employees) AS Employees, 
  SUM(fr.revenue) AS Revenue 
FROM companies AS c 
JOIN financial_reports AS fr ON c.id = fr.company_id
GROUP BY fr.fiscal_year, 
         fr.quarter, 
         c.id

DB 小提琴演示

推荐阅读