带有join，sum，group by等的SQL查询

I'm trying to build a report that will look like this:

我正在尝试构建一个如下所示的报告：

      jan feb mar apr may jun jul ago sep oct nov dec
food   0   1   1   2   0   0   3   1   0   0   1   1
car    1   0   0   0   1   2   1   0   1   2   3   4
home   0   0   1   2   2   2   5   1   2   4   0   0
other  0   0   0   0   0   0   0   0   0   0   0   0

I have two tables: t_item and t_value. t_item has 2 columns: itemID and itemName. t_value has 3 columns: itemID, value, date.

我有两个表：t_item和t_value。 t_item有2列：itemID和itemName。 t_value有3列：itemID，value，date。

With the following query I can generate a list with all the itens, even with the empty ones.

通过以下查询，我可以生成包含所有itens的列表，即使是空列表也是如此。

SELECT t_item.itemID, ISNULL(SUM(t_value.value), 0) AS value
FROM t_value RIGHT OUTER JOIN t_item ON t_value.itemID = t_item.itemID
GROUP BY t_item.itemID

But, if I try to include a MONTH column (as follows) the result will show only the items with values...

但是，如果我尝试包含MONTH列（如下所示），结果将仅显示具有值的项目...

SELECT t_item.itemID, ISNULL(SUM(t_value.value), 0) AS value, MONTH(date) AS date
FROM t_value RIGHT OUTER JOIN t_item ON t_value.itemID = t_item.itemID
GROUP BY t_item.itemID, MONTH(date)

Is it possible to do it? How do I include into the results the itens with no values and group then by month?

有可能吗？如何在结果中包含没有值和组的itens，然后按月包含？

TIA,

TIA，

Bob

短发

4 个解决方案

#1

WITH    calendar(mon) AS
        (
        SELECT  1
        UNION ALL
        SELECT  mon + 1
        FROM    calendar
        WHERE   mon < 12
        )
SELECT  itemID, mon, SUM(value)
FROM    calendar c, t_item i
LEFT OUTER JOIN
        t_value v
ON      v.itemID = i.itemID
        AND MONTH(date) = mon
GROUP BY
        i.itemID, mon

#2

For the "holes" in your data you need a filler table. Join this table with a full outer join to the fact table on month.

对于数据中的“漏洞”，您需要一个填充表。使用完整外部联接将此表连接到月份的事实表。

month
------
month --values jan through dec

For the formating you have a couple options.

对于格式化，您有几种选择。

In your reporting tool use the cross tab or matrix function.
在报告工具中使用交叉表或矩阵函数。
In SQL use the CASE function.
在SQL中使用CASE函数。
In SQL use the Pivot function.
在SQL中使用Pivot函数。

#3

Are you using a reporting tool with crosstab like ability?

您是否正在使用具有交叉表功能的报告工具？

If not, you can create a sum column for each month. so your resultset would actually look like that report sample.

如果没有，您可以为每个月创建一个总和列。所以你的结果集实际上看起来像那个报告样本。

SELECT t_item.itemID, 

--ISNULL(SUM(t_value.value), 0) AS value, 

sum(case when MONTH(date) = 1 then t_value.value else 0 end) AS m1_sum,
sum(case when MONTH(date) = 2 then t_value.value else 0 end) AS m2_sum,
sum(case when MONTH(date) = 3 then t_value.value else 0 end) AS m3_sum,
--etc

FROM t_value RIGHT OUTER JOIN t_item ON t_value.itemID = t_item.itemID
GROUP BY t_item.itemID

#4

Here's an example:

这是一个例子：

create table #months (value int, name varchar(12))
create table #items (value int, name varchar(24))
create table #sales (month int, item int, sales int)

insert into #months values (1, 'jan')
insert into #months values (2, 'feb')
insert into #months values (3, 'mar')

insert into #items values (1, 'apple')
insert into #items values (2, 'pear')
insert into #items values (3, 'nut')

insert into #sales values (1,1,12)
insert into #sales values (2,2,3)
insert into #sales values (2,2,5)
insert into #sales values (3,3,7)

You can query it using a PIVOT table, like:

您可以使用PIVOT表查询它，例如：

select *
from (
    select
        item = #items.name
    ,   month = #months.name
    ,   sales = isnull(sum(#sales.sales),0)
    from #months
    cross join #items
    left join #sales on #months.value = #sales.month 
         and #items.value = #sales.item
    group by #months.name, #items.name
) vw
pivot (sum(sales) for month in ([jan],[feb],[mar])) as PivotTable

Or as an alternative, a regular query:

或者作为替代方案，常规查询：

select
    item = #items.name
,   jan = sum(case when #sales.month = 1 then sales else 0 end)
,   feb = sum(case when #sales.month = 2 then sales else 0 end)
,   mar = sum(case when #sales.month = 3 then sales else 0 end)
from #items
left join #sales on #items.value = #sales.item
group by #items.name

Both result in:

两者都导致：

item        jan     feb     mar
apple       12      0       0
nut         0       0       7
pear        0       8       0

In the first example, the "cross join" ensures all months and values are present. They're then "left joined", so even the rows with no values are displayed.

在第一个示例中，“交叉连接”确保存在所有月份和值。然后它们“左连接”，因此即使没有值的行也会显示。

The IsNull() is just so that it displays 0 instead of NULL for a month in which that particular item was not sold.

IsNull（）只是在一个月内显示0而不是NULL，其中该特定项目未被出售。

#1

WITH    calendar(mon) AS
        (
        SELECT  1
        UNION ALL
        SELECT  mon + 1
        FROM    calendar
        WHERE   mon < 12
        )
SELECT  itemID, mon, SUM(value)
FROM    calendar c, t_item i
LEFT OUTER JOIN
        t_value v
ON      v.itemID = i.itemID
        AND MONTH(date) = mon
GROUP BY
        i.itemID, mon

#2