select ObjID,ObjID, MainDataKind0 = max(case type=1 and DataKind=0 then DataValue end), MainDataKind1 = max(case type=1 and DataKind=1 then DataValue end), MainDataKind2 = max(case type=1 and DataKind=2 then DataValue end), MainDataKind3 = max(case type=1 and DataKind=3 then DataValue end), MainDataKind4 = max(case type=1 and DataKind=4 then DataValue end), MainDataKind5 = sum(case type=1 then DataValue end), OtherDataKind0 = max(case type=2 and DataKind=0 then DataValue end), OtherDataKind1 = max(case type=2 and DataKind=1 then DataValue end) from (select *,type=1 from tData1 union select *,type=2 from tData2) a group by ObjID,ObjID order by ObjID,ObjID
select a.ObjID, a.[DataTime], MainDataKind0 = max(case a.type=1 and a.DataKind=0 then a.DataValue end), MainDataKind1 = max(case a.type=1 and a.DataKind=1 then a.DataValue end), MainDataKind2 = max(case a.type=1 and a.DataKind=2 then a.DataValue end), MainDataKind3 = max(case a.type=1 and a.DataKind=3 then a.DataValue end), MainDataKind4 = max(case a.type=1 and a.DataKind=4 then a.DataValue end), MainDataKind5 = sum(case a.type=1 then a.DataValue end), OtherDataKind0 = max(case a.type=2 and a.DataKind=0 then a.DataValue end), OtherDataKind1 = max(case a.type=2 and a.DataKind=1 then a.DataValue end) from (select *,type=1 from tData1 union select *,type=2 from tData2) a group by a.ObjID,a.[DataTime] order by a.ObjID,a.[DataTime]
create table tDate1 (ObjID int, DataValue int, DataTime datetime, DataKind int)insert tDate1 select 2000,120,'2005-12-2 2:34',0 union all select 2000,110,'2005-12-2 2:34',1 union all select 2000,140,'2005-12-2 2:34',2 union all select 2000,1140,'2005-12-2 2:34',3 union all select 2000,35,'2005-12-2 2:34',4 union all select 2000,125,'2005-12-4 2:34',0 union all select 2000,115,'2005-12-4 2:34',1 union all select 2000,145,'2005-12-4 2:34',2 union all select 2000,1145,'2005-12-4 2:34',3 union all select 2000,37,'2005-12-4 2:34',4create table tDate2 (ObjID int, DataValue int, DataTime datetime, DataKind int)insert tDate2 select 2000,5,'2005-12-2 2:34',0 union all select 2000,20,'2005-12-2 2:34',1 union all select 2000,8,'2005-12-4 2:34',0 union all select 2000,34,'2005-12-4 2:34',1declare @sql varchar(2000) set @sql='select ObjID,DataTime' select @sql=@sql+',[MainData'+cast(DataKind as varchar)+']=isnull(sum(case when DataKind='+cast(DataKind as varchar)+' and DataType='+cast(DataType as varchar)+' then DataValue end),0)' from (select *,1 as DataType from tDate1) a group by DataKind,DataType select @sql=@sql+',[MainData'+cast((select max(DataKind) from tDate1)+1 as varchar)+'] =isnull(sum(case when DataType='+cast(DataType as varchar)+' then DataValue end),0)' from (select *,1 as DataType from tDate1) a group by DataType select @sql=@sql+',[OtherData'+cast(DataKind as varchar)+']=isnull(sum(case when DataKind='+cast(DataKind as varchar)+' and DataType='+cast(DataType as varchar)+' then DataValue end),0)' from (select *,2 as DataType from tDate2) a group by DataKind,DataTypeselect @sql=@sql+' from (select *,1 as DataType from tDate1 union all select *,2 as DataType from tDate2) a group by ObjID,DataTime'exec(@sql)drop table tDate1,tDate2 ObjID DataTime MainData0 MainData1 MainData2 MainData3 MainData4 MainData5 OtherData0 OtherData1 ----------- ------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 2000 2005-12-02 02:34:00.000 120 110 140 1140 35 1545 5 20 2000 2005-12-04 02:34:00.000 125 115 145 1145 37 1567 8 34警告: 聚合或其它 SET 操作消除了空值。
--生成测试数据 create table tData1(ObjID int,DataValue int,[DataTime] datetime,DataKind int) insert into tData1 select 2000,120 ,'2005-12-2 2:34',0 insert into tData1 select 2000,110 ,'2005-12-2 2:34',1 insert into tData1 select 2000,140 ,'2005-12-2 2:34',2 insert into tData1 select 2000,1140,'2005-12-2 2:34',3 insert into tData1 select 2000,35 ,'2005-12-2 2:34',4 insert into tData1 select 2000,125 ,'2005-12-4 2:34',0 insert into tData1 select 2000,115 ,'2005-12-4 2:34',1 insert into tData1 select 2000,145 ,'2005-12-4 2:34',2 insert into tData1 select 2000,1145,'2005-12-4 2:34',3 insert into tData1 select 2000,37 ,'2005-12-4 2:34',4 create table tData2(ObjID int,DataValue int,[DataTime] datetime,DataKind int) insert into tData2 select 2000,5 ,'2005-12-2 2:34',0 insert into tData2 select 2000,20,'2005-12-2 2:34',1 insert into tData2 select 2000,8 ,'2005-12-4 2:34',0 insert into tData2 select 2000,34,'2005-12-4 2:34',1 --执行查询处理过程 select a.ObjID, a.[DataTime], MainDataKind0 = max(case when a.type=1 and a.DataKind=0 then a.DataValue end), MainDataKind1 = max(case when a.type=1 and a.DataKind=1 then a.DataValue end), MainDataKind2 = max(case when a.type=1 and a.DataKind=2 then a.DataValue end), MainDataKind3 = max(case when a.type=1 and a.DataKind=3 then a.DataValue end), MainDataKind4 = max(case when a.type=1 and a.DataKind=4 then a.DataValue end), MainDataKind5 = sum(case when a.type=1 then a.DataValue end), OtherDataKind0 = max(case when a.type=2 and a.DataKind=0 then a.DataValue end), OtherDataKind1 = max(case when a.type=2 and a.DataKind=1 then a.DataValue end) from (select *,type=1 from tData1 union select *,type=2 from tData2) a group by a.ObjID,a.[DataTime] order by a.ObjID,a.[DataTime]--输出结果 /* 2000 2005-12-02 02:34:00.000 120 110 140 1140 35 1545 5 20 2000 2005-12-04 02:34:00.000 125 115 145 1145 37 1567 8 34 */--删除测试数据 drop table tData1,tData2
declare @a table(ObjID int,DataValue int,DataTime datetime,DataKind int) insert @a select 2000,120,'2005-12-2 2:34',0 union all select 2000,110,'2005-12-2 2:34',1 union all select 2000,140,'2005-12-2 2:34',2 union all select 2000,1140,'2005-12-2 2:34',3 union all select 2000,35,'2005-12-2 2:34',4 union all select 2000,125,'2005-12-4 2:34',0 union all select 2000,115,'2005-12-4 2:34',1 union all select 2000,145,'2005-12-4 2:34',2 union all select 2000,1145,'2005-12-4 2:34',3 union all select 2000,37,'2005-12-4 2:34',4declare @b table(ObjID int,DataValue int,DataTime datetime,DataKind int) insert @b select 2000,5,'2005-12-2 2:34',0 union all select 2000,20,'2005-12-2 2:34',1 union all select 2000,8,'2005-12-4 2:34',0 union all select 2000,34,'2005-12-4 2:34',1select 'a' as id,* into #t from @a insert #t select 'b' as id,* from @bselect ObjID, DataTime, MainDataKind0=max((case when datakind=0 and id= 'a' then DataValue end)), MainDataKind1=max((case when datakind=1 and id= 'a' then DataValue end)), MainDataKind2=max((case when datakind=2 and id= 'a' then DataValue end)), MainDataKind3=max((case when datakind=3 and id= 'a' then DataValue end)), MainDataKind4=max((case when datakind=4 and id= 'a' then DataValue end)), OtherDataKind0=max((case when datakind=1 and id= 'b' then DataValue end)), OtherDataKind1=max((case when datakind=1 and id= 'b' then DataValue end)) from #t where DataTime between '2005-12-1' and '2005-12-5' group by ObjID,DataTime------------结果 2000 2005-12-02 02:34:00.000 120 110 140 1140 35 20 20 2000 2005-12-04 02:34:00.000 125 115 145 1145 37 34 34
create table tDate1 (ObjID int, DataValue1 int, DataTime1 datetime, DataKind1 int)insert tDate1 select 2000,120,'2005-12-2 2:34',0 union all select 2000,110,'2005-12-2 2:34',1 union all select 2000,140,'2005-12-2 2:34',2 union all select 2000,1140,'2005-12-2 2:34',3 union all select 2000,35,'2005-12-2 2:34',4 union all select 2000,125,'2005-12-4 2:34',0 union all select 2000,115,'2005-12-4 2:34',1 union all select 2000,145,'2005-12-4 2:34',2 union all select 2000,1145,'2005-12-4 2:34',3 union all select 2000,37,'2005-12-4 2:34',4create table tDate2 (ObjID int, DataValue2 int, DataTime2 datetime, DataKind2 int)insert tDate2 select 2000,5,'2005-12-2 2:34',0 union all select 2000,20,'2005-12-2 2:34',1 union all select 2000,8,'2005-12-4 2:34',0 union all select 2000,34,'2005-12-4 2:34',1declare @sql varchar(2000) set @sql='select ObjID,DataTime' select @sql=@sql+',[MainData'+cast(DataKind1 as varchar)+']=isnull(sum(case when DataKind='+cast(DataKind1 as varchar)+' and DataType='+cast(DataType as varchar)+' then DataValue end),0)' from (select *,1 as DataType from tDate1) a group by DataKind1,DataType select @sql=@sql+',[MainData'+cast((select max(DataKind1) from tDate1)+1 as varchar)+'] =isnull(sum(case when DataType='+cast(DataType as varchar)+' then DataValue end),0)' from (select *,1 as DataType from tDate1) a group by DataType select @sql=@sql+',[OtherData'+cast(DataKind2 as varchar)+']=isnull(sum(case when DataKind='+cast(DataKind2 as varchar)+' and DataType='+cast(DataType as varchar)+' then DataValue end),0)' from (select *,2 as DataType from tDate2) a group by DataKind2,DataTypeselect @sql=@sql+' from (select ObjID,DataValue1 as DataValue,DataTime1 as DataTime, DataKind1 as DataKind,1 as DataType from tDate1 union all select *,2 as DataType from tDate2) a group by ObjID,DataTime'exec(@sql)drop table tDate1,tDate2ObjID DataTime MainData0 MainData1 MainData2 MainData3 MainData4 MainData5 OtherData0 OtherData1 ----------- ------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 2000 2005-12-02 02:34:00.000 120 110 140 1140 35 1545 5 20 2000 2005-12-04 02:34:00.000 125 115 145 1145 37 1567 8 34警告: 聚合或其它 SET 操作消除了空值。
ObjID,ObjID,
MainDataKind0 = max(case type=1 and DataKind=0 then DataValue end),
MainDataKind1 = max(case type=1 and DataKind=1 then DataValue end),
MainDataKind2 = max(case type=1 and DataKind=2 then DataValue end),
MainDataKind3 = max(case type=1 and DataKind=3 then DataValue end),
MainDataKind4 = max(case type=1 and DataKind=4 then DataValue end),
MainDataKind5 = sum(case type=1 then DataValue end),
OtherDataKind0 = max(case type=2 and DataKind=0 then DataValue end),
OtherDataKind1 = max(case type=2 and DataKind=1 then DataValue end)
from
(select *,type=1 from tData1
union
select *,type=2 from tData2) a
group by
ObjID,ObjID
order by
ObjID,ObjID
a.ObjID,
a.[DataTime],
MainDataKind0 = max(case a.type=1 and a.DataKind=0 then a.DataValue end),
MainDataKind1 = max(case a.type=1 and a.DataKind=1 then a.DataValue end),
MainDataKind2 = max(case a.type=1 and a.DataKind=2 then a.DataValue end),
MainDataKind3 = max(case a.type=1 and a.DataKind=3 then a.DataValue end),
MainDataKind4 = max(case a.type=1 and a.DataKind=4 then a.DataValue end),
MainDataKind5 = sum(case a.type=1 then a.DataValue end),
OtherDataKind0 = max(case a.type=2 and a.DataKind=0 then a.DataValue end),
OtherDataKind1 = max(case a.type=2 and a.DataKind=1 then a.DataValue end)
from
(select *,type=1 from tData1
union
select *,type=2 from tData2) a
group by
a.ObjID,a.[DataTime]
order by
a.ObjID,a.[DataTime]
(ObjID int, DataValue int, DataTime datetime, DataKind int)insert tDate1
select 2000,120,'2005-12-2 2:34',0 union all
select 2000,110,'2005-12-2 2:34',1 union all
select 2000,140,'2005-12-2 2:34',2 union all
select 2000,1140,'2005-12-2 2:34',3 union all
select 2000,35,'2005-12-2 2:34',4 union all
select 2000,125,'2005-12-4 2:34',0 union all
select 2000,115,'2005-12-4 2:34',1 union all
select 2000,145,'2005-12-4 2:34',2 union all
select 2000,1145,'2005-12-4 2:34',3 union all
select 2000,37,'2005-12-4 2:34',4create table tDate2
(ObjID int, DataValue int, DataTime datetime, DataKind int)insert tDate2
select 2000,5,'2005-12-2 2:34',0 union all
select 2000,20,'2005-12-2 2:34',1 union all
select 2000,8,'2005-12-4 2:34',0 union all
select 2000,34,'2005-12-4 2:34',1declare @sql varchar(2000)
set @sql='select ObjID,DataTime'
select @sql=@sql+',[MainData'+cast(DataKind as varchar)+']=isnull(sum(case when DataKind='+cast(DataKind as varchar)+'
and DataType='+cast(DataType as varchar)+' then DataValue end),0)'
from (select *,1 as DataType from tDate1) a group by DataKind,DataType
select @sql=@sql+',[MainData'+cast((select max(DataKind) from tDate1)+1 as varchar)+']
=isnull(sum(case when DataType='+cast(DataType as varchar)+' then DataValue end),0)'
from (select *,1 as DataType from tDate1) a
group by DataType
select @sql=@sql+',[OtherData'+cast(DataKind as varchar)+']=isnull(sum(case when DataKind='+cast(DataKind as varchar)+'
and DataType='+cast(DataType as varchar)+' then DataValue end),0)'
from (select *,2 as DataType from tDate2) a group by DataKind,DataTypeselect @sql=@sql+' from (select *,1 as DataType from tDate1 union all select *,2 as DataType from tDate2) a
group by ObjID,DataTime'exec(@sql)drop table tDate1,tDate2
ObjID DataTime MainData0 MainData1 MainData2 MainData3 MainData4 MainData5 OtherData0 OtherData1
----------- ------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2000 2005-12-02 02:34:00.000 120 110 140 1140 35 1545 5 20
2000 2005-12-04 02:34:00.000 125 115 145 1145 37 1567 8 34警告: 聚合或其它 SET 操作消除了空值。
create table tData1(ObjID int,DataValue int,[DataTime] datetime,DataKind int)
insert into tData1 select 2000,120 ,'2005-12-2 2:34',0
insert into tData1 select 2000,110 ,'2005-12-2 2:34',1
insert into tData1 select 2000,140 ,'2005-12-2 2:34',2
insert into tData1 select 2000,1140,'2005-12-2 2:34',3
insert into tData1 select 2000,35 ,'2005-12-2 2:34',4
insert into tData1 select 2000,125 ,'2005-12-4 2:34',0
insert into tData1 select 2000,115 ,'2005-12-4 2:34',1
insert into tData1 select 2000,145 ,'2005-12-4 2:34',2
insert into tData1 select 2000,1145,'2005-12-4 2:34',3
insert into tData1 select 2000,37 ,'2005-12-4 2:34',4
create table tData2(ObjID int,DataValue int,[DataTime] datetime,DataKind int)
insert into tData2 select 2000,5 ,'2005-12-2 2:34',0
insert into tData2 select 2000,20,'2005-12-2 2:34',1
insert into tData2 select 2000,8 ,'2005-12-4 2:34',0
insert into tData2 select 2000,34,'2005-12-4 2:34',1
--执行查询处理过程
select
a.ObjID,
a.[DataTime],
MainDataKind0 = max(case when a.type=1 and a.DataKind=0 then a.DataValue end),
MainDataKind1 = max(case when a.type=1 and a.DataKind=1 then a.DataValue end),
MainDataKind2 = max(case when a.type=1 and a.DataKind=2 then a.DataValue end),
MainDataKind3 = max(case when a.type=1 and a.DataKind=3 then a.DataValue end),
MainDataKind4 = max(case when a.type=1 and a.DataKind=4 then a.DataValue end),
MainDataKind5 = sum(case when a.type=1 then a.DataValue end),
OtherDataKind0 = max(case when a.type=2 and a.DataKind=0 then a.DataValue end),
OtherDataKind1 = max(case when a.type=2 and a.DataKind=1 then a.DataValue end)
from
(select *,type=1 from tData1
union
select *,type=2 from tData2) a
group by
a.ObjID,a.[DataTime]
order by
a.ObjID,a.[DataTime]--输出结果
/*
2000 2005-12-02 02:34:00.000 120 110 140 1140 35 1545 5 20
2000 2005-12-04 02:34:00.000 125 115 145 1145 37 1567 8 34
*/--删除测试数据
drop table tData1,tData2
insert @a
select 2000,120,'2005-12-2 2:34',0 union all
select 2000,110,'2005-12-2 2:34',1 union all
select 2000,140,'2005-12-2 2:34',2 union all
select 2000,1140,'2005-12-2 2:34',3 union all
select 2000,35,'2005-12-2 2:34',4 union all
select 2000,125,'2005-12-4 2:34',0 union all
select 2000,115,'2005-12-4 2:34',1 union all
select 2000,145,'2005-12-4 2:34',2 union all
select 2000,1145,'2005-12-4 2:34',3 union all
select 2000,37,'2005-12-4 2:34',4declare @b table(ObjID int,DataValue int,DataTime datetime,DataKind int)
insert @b
select 2000,5,'2005-12-2 2:34',0 union all
select 2000,20,'2005-12-2 2:34',1 union all
select 2000,8,'2005-12-4 2:34',0 union all
select 2000,34,'2005-12-4 2:34',1select 'a' as id,* into #t from @a
insert #t select 'b' as id,* from @bselect
ObjID,
DataTime,
MainDataKind0=max((case when datakind=0 and id= 'a' then DataValue end)),
MainDataKind1=max((case when datakind=1 and id= 'a' then DataValue end)),
MainDataKind2=max((case when datakind=2 and id= 'a' then DataValue end)),
MainDataKind3=max((case when datakind=3 and id= 'a' then DataValue end)),
MainDataKind4=max((case when datakind=4 and id= 'a' then DataValue end)),
OtherDataKind0=max((case when datakind=1 and id= 'b' then DataValue end)),
OtherDataKind1=max((case when datakind=1 and id= 'b' then DataValue end))
from #t
where DataTime between '2005-12-1' and '2005-12-5'
group by ObjID,DataTime------------结果
2000 2005-12-02 02:34:00.000 120 110 140 1140 35 20 20
2000 2005-12-04 02:34:00.000 125 115 145 1145 37 34 34
tData2是:ObjID, DataValue2, DataTime2, DataKind2不知这个该如何是好啊,另外,我这个是依据tData1的时间来查找tData2中是否存在对应时间的两种附加数据,如果不存在能否设置为NULL?检索时能否检索一个特定时间或一个时间段的数据麻烦了,多谢,分不够的话可以给我发短信,我另外开贴给分,拜托了。
(ObjID int, DataValue1 int, DataTime1 datetime, DataKind1 int)insert tDate1
select 2000,120,'2005-12-2 2:34',0 union all
select 2000,110,'2005-12-2 2:34',1 union all
select 2000,140,'2005-12-2 2:34',2 union all
select 2000,1140,'2005-12-2 2:34',3 union all
select 2000,35,'2005-12-2 2:34',4 union all
select 2000,125,'2005-12-4 2:34',0 union all
select 2000,115,'2005-12-4 2:34',1 union all
select 2000,145,'2005-12-4 2:34',2 union all
select 2000,1145,'2005-12-4 2:34',3 union all
select 2000,37,'2005-12-4 2:34',4create table tDate2
(ObjID int, DataValue2 int, DataTime2 datetime, DataKind2 int)insert tDate2
select 2000,5,'2005-12-2 2:34',0 union all
select 2000,20,'2005-12-2 2:34',1 union all
select 2000,8,'2005-12-4 2:34',0 union all
select 2000,34,'2005-12-4 2:34',1declare @sql varchar(2000)
set @sql='select ObjID,DataTime'
select @sql=@sql+',[MainData'+cast(DataKind1 as varchar)+']=isnull(sum(case when DataKind='+cast(DataKind1 as varchar)+'
and DataType='+cast(DataType as varchar)+' then DataValue end),0)'
from (select *,1 as DataType from tDate1) a group by DataKind1,DataType
select @sql=@sql+',[MainData'+cast((select max(DataKind1) from tDate1)+1 as varchar)+']
=isnull(sum(case when DataType='+cast(DataType as varchar)+' then DataValue end),0)'
from (select *,1 as DataType from tDate1) a
group by DataType
select @sql=@sql+',[OtherData'+cast(DataKind2 as varchar)+']=isnull(sum(case when DataKind='+cast(DataKind2 as varchar)+'
and DataType='+cast(DataType as varchar)+' then DataValue end),0)'
from (select *,2 as DataType from tDate2) a group by DataKind2,DataTypeselect @sql=@sql+' from (select ObjID,DataValue1 as DataValue,DataTime1 as DataTime,
DataKind1 as DataKind,1 as DataType from tDate1 union all select *,2 as DataType from tDate2) a
group by ObjID,DataTime'exec(@sql)drop table tDate1,tDate2ObjID DataTime MainData0 MainData1 MainData2 MainData3 MainData4 MainData5 OtherData0 OtherData1
----------- ------------------------------------------------------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2000 2005-12-02 02:34:00.000 120 110 140 1140 35 1545 5 20
2000 2005-12-04 02:34:00.000 125 115 145 1145 37 1567 8 34警告: 聚合或其它 SET 操作消除了空值。