使用SQL显示零值月份 [英] Displaying zero valued months with 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.

这篇关于使用SQL显示零值月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！