使用SQL显示零值月份

本文介绍了使用SQL显示零值月份的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

以下查询返回销售月份的值，但不返回销售值为零的月份的值:

The following query returns values for months with sales but not for months where sales are zero:

SELECT DATE_FORMAT(O.OrderDate, "%b, %Y") AS MonthYear, SUM(O.Total * C.Rate) AS Sales FROM (Orders O INNER JOIN OrdersStates OS ON O.OrderID = OS.OrderID) INNER JOIN Users U ON U.UserID = O.UserID INNER JOIN UsersDescription UD ON U.UserID = UD.UserID INNER JOIN States S ON S.StateID = OS.StateID INNER JOIN Currencies C ON O.Currency = C.Abb WHERE O.OrderDate >= '2009-10-01 00:00:00' AND O.OrderDate < '2010-04-19 23:59:59' GROUP BY MonthYear ORDER BY MonthYear

目标是在没有销售的月份中，使其返回零值.谁能看到遗失的东西吗?

The goal is to make it return a zero value in months where there are no sales. Can anyone see what is missing?

先谢谢您

奥维尔

推荐答案

虽然并非不可能，但在SQL中做到这一点并不容易.

While not impossible, it's not easy to do this in SQL.

由于这主要是一个演示问题，所以通过遍历结果并检查缺失的月份来执行问题中的查询后，可能会更容易在客户端中添加缺失的行.

Since it is mostly a presentation issue it would probably be easier to add the missing rows in the client after you have executed the query in your question by iterating over the results and checking for missing months.