我有一个小问题需要解决,我有以下问题:
SELECT SUM(total) AS total, products, DATE_FORMAT(TransDate, '%M') AS month
FROM orders
WHERE Status = 'paid'
AND YEAR(TransDate) = '2016'
GROUP BY Product, YEAR(TransDate), MONTH(TransDate)
ORDER BY TransDate ASC
我对这个问题的回答是:
<table border=1>
<tr>
<th>total</th>
<th>Product</th>
<th>Month</th>
</tr>
<tr>
<td>12000</td>
<td>Ford Mondeo</td>
<td>Jan</td>
</tr>
<tr>
<td>23000</td>
<td>Camaro</td>
<td>Jan</td>
</tr>
<tr>
<td>9000</td>
<td>Golf</td>
<td>Jan</td>
</tr>
<tr>
<td>42000</td>
<td>Audi A4</td>
<td>Jan</td>
</tr>
<tr>
<td>4566</td>
<td>Ford Mondeo</td>
<td>Feb</td>
</tr>
<tr>
<td>35435</td>
<td>Camaro</td>
<td>Feb</td>
</tr>
<tr>
<td>58456</td>
<td>Golf</td>
<td>Feb</td>
</tr>
<tr>
<td>56465</td>
<td>Audi A4</td>
<td>Feb</td>
</tr>
<tr>
<td>3522</td>
<td>Ford Mondeo</td>
<td>Mar</td>
</tr>
<tr>
<td>468132</td>
<td>Camaro</td>
<td>Mar</td>
</tr>
<tr>
<td>25225</td>
<td>Golf</td>
<td>Mar</td>
</tr>
<tr>
<td>54612</td>
<td>Audi A4</td>
<td>Mar</td>
</tr>
<tr>
<td>12000</td>
<td>Ford Mondeo</td>
<td>Apr</td>
</tr>
<tr>
<td>23000</td>
<td>Camaro</td>
<td>Apr</td>
</tr>
<tr>
<td>9000</td>
<td>Golf</td>
<td>Apr</td>
</tr>
<tr>
<td>42000</td>
<td>Audi A4</td>
<td>Apr</td>
</tr>
<tr>
<td>54554</td>
<td>Beatle</td>
<td>Apr</td>
</tr>
<tr>
<td>12000</td>
<td>Ford Mondeo</td>
<td>May</td>
</tr>
<tr>
<td>23000</td>
<td>Camaro</td>
<td>May</td>
</tr>
<tr>
<td>9000</td>
<td>Golf</td>
<td>May</td>
</tr>
<tr>
<td>42000</td>
<td>Audi A4</td>
<td>May</td>
</tr>
<tr>
<td>54554</td>
<td>Beatle</td>
<td>May</td>
</tr>
</table>
在我的例子中,披头士的车只有过去两个月的销售价值,在前几个月没有,我想知道是否有任何方式,它出现在其他月份的总价值为0,并在该车实际销售的月份,其全部价值是正确的。
非常感谢你的帮助
1条答案
按热度按时间envsm3lx1#
使用
cross join
生成所有产品/日期组合。然后使用left join
带来订单信息。您可以使用上的子查询获取日期orders
,如果您没有其他方便的方法(例如日历表):