SQL Server查询连接多个表

I have a query that I don't think should be that hard to make, however, I've spent a lot of time on it now and still can't get it the way I want, so I hope someone here can help me.

我有一个查询,我认为不应该很难,但是,我现在花了很多时间在它上面仍然无法按照我想要的方式得到它,所以我希望有人在这里可以帮助我。

Basically, I need to create a report that will give a value for each month, for each area. However, not all areas deliver data each month; in that case the view should return NULL for that month and area. So, the view need to look something like this:

基本上,我需要创建一个报告,为每个区域提供每个月的值。但是,并非所有地区每个月都会提供数据;在这种情况下,视图应该为该月份和区域返回NULL。所以,视图需要看起来像这样:

Month      Area    Value
2012-08-01 Area1   2
2012-08-01 Area2   3
2012-09-01 Area1   3
2012-09-01 Area2   NULL

My data table looks something like this

我的数据表看起来像这样

Date       Area    Value
2012-08-01 Area1   2
2012-08-01 Area2   3
2012-09-01 Area1   3 -- Notice that Area2 is not present for September here

I have a table with all the available areas Furthermore, I have created a table-valued function that returns all dates from a given date until now.

我有一个包含所有可用区域的表格此外,我创建了一个表值函数,它返回从给定日期到现在的所有日期。

For example this statement

例如这句话

SELECT * FROM Periods_Months('2012-01-01')

would return 8 records like:

将返回8条记录,如:

DateValue           Year    Month   YearMonth
2012-01-01 00:00:00.000 2012    1   20121
2012-02-01 00:00:00.000 2012    2   20122
2012-03-01 00:00:00.000 2012    3   20123
2012-04-01 00:00:00.000 2012    4   20124
2012-05-01 00:00:00.000 2012    5   20125
2012-06-01 00:00:00.000 2012    6   20126
2012-07-01 00:00:00.000 2012    7   20127
2012-08-01 00:00:00.000 2012    8   20128

Based on the suggestions, my query now looks like this:

基于这些建议,我的查询现在看起来像这样:

WITH months AS (
    SELECT DateValue, YearMonth FROM Periods_Months('2011-01-01')
)
select m.DateValue
       ,CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,m.DateValue)+1,0)) AS Date) AS DateReported -- Get last day in month
       ,ResponseTime AS Value
       ,g.ExternalId 
from GISDB.dbo.GisObjects g
CROSS JOIN months m
LEFT OUTER JOIN
( -- SELECT data from data table, grouped by area and month
SELECT dbo.YearMonth(CloseDate) AS YearMonth
       ,MAX(CloseDate) AS LastDate
       ,GisObjectId
       ,SUM(DATEDIFF(HH,RegDate,CloseDate)) AS ResponseTime -- calculate response time between start and end data (the value we need)
FROM DataTable 
WHERE   CloseDate IS NOT NULL
AND     GisObjectId IS NOT NULL
GROUP BY GisObjectId, dbo.YearMonth(CloseDate)  -- group by area and month
) c
ON g.ObjectId = c.GisObjectId AND c.YearMonth = m.YearMonth 
WHERE g.CompanyId = 3 AND g.ObjectTypeId = 1 -- reduce the GIS objects that we compare to
ORDER BY m.DateValue, g.ObjectId

But the result is this (Value is always NULL):

但结果是这样(值总是为NULL):

DateValue                   DateReported    Value   ExternalId
2011-01-01 00:00:00.000 31-01-2011  NULL    9994
2011-01-01 00:00:00.000 31-01-2011  NULL    9993
2011-01-01 00:00:00.000 31-01-2011  NULL    9992
2011-01-01 00:00:00.000 31-01-2011  NULL    9991
2011-01-01 00:00:00.000 31-01-2011  NULL    2339
2011-01-01 00:00:00.000 31-01-2011  NULL    2338
2011-01-01 00:00:00.000 31-01-2011  NULL    2337
2011-01-01 00:00:00.000 31-01-2011  NULL    2336
2011-01-01 00:00:00.000 31-01-2011  NULL    2335
2011-01-01 00:00:00.000 31-01-2011  NULL    2334
2011-01-01 00:00:00.000 31-01-2011  NULL    2327
2011-01-01 00:00:00.000 31-01-2011  NULL    2326
2011-01-01 00:00:00.000 31-01-2011  NULL    2325
2011-01-01 00:00:00.000 31-01-2011  NULL    2324
2011-01-01 00:00:00.000 31-01-2011  NULL    2323
2011-01-01 00:00:00.000 31-01-2011  NULL    2322

etc.

2 个解决方案

#1

I suppose you have a table with all your areas, which I call area_table.

我想你有一张桌子上有你所有的区域,我称之为area_table。

WITH month_table AS (
    SELECT dateValue FROM Periods_Months('2012-01-01')
)
select * from area_table 
CROSS JOIN month_table
LEFT OUTER JOIN myValueTable 
ON area_table.name = myValueTable.area 
AND myValueTable.date = left(convert(varchar(30),month_table.dateValue,120),10) 
ORDER BY myValueTable.Month, myValueTable.area

#2

Suppose Areas is your table for all available areas, t - is your data table:

假设区域是所有可用区域的表格,t - 是您的数据表:

SELECT pm.dateValue,Ar.Area, t.value 
FROM Periods_Months('2012-01-01') pm, Areas ar
  left join t on (pm.dateValue=t.Date) and (ar.Area=t.Area)
order by pm.DateValue,ar.Area

#1