<p>基于前面的答案,我宁愿使用<code>INNER JOIN</code>。我确实同意<code>CASE WHEN</code>构造,但是由于每个值要考虑的行数不同,我们需要自己计算平均值(<code>AVG</code>不起作用)。这是一个相当沉重的建筑,但我看不到任何简单的东西。在</p>
<pre class="lang-sql prettyprint-override"><code>SELECT
P.name
,SUM(CASE WHEN PA.record_date = <todays date> THEN PA.score ELSE 0 END)
/ SUM(CASE WHEN PA.record_date = <todays date> THEN 1 ELSE 0 END) AS today
,SUM(CASE WHEN PA.record_date BETWEEN <start date> AND <end date> THEN PA.score ELSE 0 END)
/ SUM(CASE WHEN PA.record_date BETWEEN <start date> AND <end date> THEN 1 ELSE 0 END) AS wtd
,SUM(CASE when PA.record_date BETWEEN <start date> AND <end date> THEN PA.score ELSE 0 END)
/ SUM(CASE WHEN PA.record_date BETWEEN <start date> AND <end date> THEN 1 ELSE 0 END) AS mtd
,SUM(CASE WHEN PA.record_date BETWEEN <start date> AND <end date> THEN PA.score ELSE 0 END)
/ SUM(CASE WHEN PA.record_date BETWEEN <start date> AND <end date> THEN 1 ELSE 0 END) AS ytd
FROM product P
INNER JOIN product_assessment PA
ON (P.id = PA.product_id)
GROUP BY P.name;
</code></pre>