php - 寄存器 0x​​104567911

Question

预期输出:需要打印第4季度的wage_amt

+--------------+--------------+--------------+--------------+
| wages_amt_q1 | wages_amt_q2 | wages_amt_q3 | wages_amt_q4 |
+--------------+--------------+--------------+--------------+
| 108          | 100          | 18           | 128          |
+--------------+--------------+--------------+--------------+

当前输出:从作业表中提取所有作业 ID，创建循环传递以下查询中的每个作业 ID，执行 4 次以获取 4 个季度值。

select avg(wages) as wages_amt_q1 from wages where job_id = 73 and date_id in (select date_id from wages_date where quarter = 1 and year = 2015) group by job_id;

+--------------+
| wages_amt_q1 |
+--------------+
| 108          |
+--------------+
1 row in set (0.01 sec)

是否需要全面支持以最大限度地减少查询命中次数。

Answer 1

您可以使用以下查询获取每季度的工资:

SELECT `wages_date`.`quarter`,`wages_date`.`year`,avg(`wages`.`wages`)
FROM `wages`
LEFT JOIN `wages_date` ON `wages_date`.`date_id`=`wages`.`date_id`
WHERE (`job_id` = 73)
GROUP BY `wages_date`.`quartner`,`wages_date`.`year`;