我有一个表IPAddressHistory,其中包含三列IPAddressID,DateFrom和DateTo,另一个表IPAddressTimeValue具有按特定时间的详细值。所以,从我需要选择和值IPAddressTimeValue相关的IPAddressID之间DateFrom和DateTo从IPAddressHistory。您可以从sqlfiddle中看到我想要的东西,例如,我使用了IPAddressHistory和UNION ALL中的简单静态日期。谢谢。
初始表:
CREATE TABLE IPAddressHistory(
[IPAddressHistoryID] int IDENTITY(1,1) NOT NULL,
[IPAddressID] int NOT NULL,
[DateFrom] datetime,
[DateTo] datetime,
CONSTRAINT [PK_IPAddressHistory] PRIMARY KEY CLUSTERED
(
[IPAddressHistoryID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
CREATE TABLE IPAddressTimeValue(
[IPAddressTimeValueID] int IDENTITY(1,1) NOT NULL,
[IPAddressID] int NOT NULL,
[Time] datetime,
[CCNI] int,
[TRNI] int,
CONSTRAINT [PK_IPAddressTimeValue] PRIMARY KEY CLUSTERED
(
[IPAddressTimeValueID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
初始数据:
您需要的查询是以下查询:
select a.[IPAddressID],
a.[DateFrom],
a.[DateTo],
SUM([CCNI]) [CCNI],
SUM([TRNI]) [TRNI]
from IPAddressHistory a
INNER JOIN IPAddressTimeValue b
ON (a.[IPAddressID] = b.[IPAddressID])
where b.[Time] > a.[DateFrom]
and b.[Time] <= a.[DateTo]
group by a.[IPAddressID], a.[DateFrom], a.[DateTo];
看到它在这里与您自己的选定联合一起工作:http : //sqlfiddle.com/#!6/abfe6/5
要根据需要格式化日期(datefrom和dateto),只需使用一些日期格式化功能。不要忘记在分组依据的选择字段上使用它。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句