用mysql选择数据

798qvoo8 于 2021-06-17 发布在 Mysql

关注(0)|答案(1)|浏览(306)

我有一个小问题需要解决，我有以下问题：

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，并在该车实际销售的月份，其全部价值是正确的。
非常感谢你的帮助

sql mysql

来源：https://stackoverflow.com/questions/53958444/selecting-data-with-mysql

1条答案

按热度按时间

db2dz4w81#

使用 cross join 生成所有产品/日期组合。然后使用 left join 带来订单信息。您可以使用上的子查询获取日期 orders ，如果您没有其他方便的方法（例如日历表）：

SELECT ym.yyyymm, p.product, COALESCE(SUM(o.total), 0) AS total
FROM (SELECT DISTINCT DATE_FORMAT(TransDate, '%M') as yyyymm,
             YEAR(TransDate) as yyyy, MONTH(TransDate) as mm
      FROM orders o
      WHERE TransDate >= '2016-01-01' and TransDate < '2017-01-01'
     ) ym CROSS JOIN
     (SELECT DISTINCT o.product FROM orders o
     ) p LEFT JOIN
     orders o
     ON YEAR(o.TransDate) = ym.yyyy AND
        MONTH(o.TransDate) = ym.mm AND
        o.product = p.product AND
        o.Status = 'paid'
GROUP BY p.Product, ym.yyyymm
ORDER BY MIN(o.TransDate), p.Product;

赞(0）回复(0）举报 2021-06-17

我来回答

用mysql选择数据

1条答案

相关问题

热门标签

最新问答