有这样两个表,
表一:
datetime temperature
12:21:00 23
12:21:02 25
12:21:04 36
12:21:05 35
12:21:08 35
表二:
datetime temperature
12:21:00 23
12:21:01 25
12:21:03 36
12:21:06 35
12:21:09 35
不过最终生成:
datetime value1 value2
12:21:00 23 23
12:21:01 (null) 25
12:21:02 25 (null)
12:21:03 (null) 36
12:21:04 36 (null)
12:21:05 35 (null)
12:21:06 (null) 35
要生成这样的得让两个表做一次交叉之类的吧...有什么好解决方案吗?
表一:
datetime temperature
12:21:00 23
12:21:02 25
12:21:04 36
12:21:05 35
12:21:08 35
表二:
datetime temperature
12:21:00 23
12:21:01 25
12:21:03 36
12:21:06 35
12:21:09 35
不过最终生成:
datetime value1 value2
12:21:00 23 23
12:21:01 (null) 25
12:21:02 25 (null)
12:21:03 (null) 36
12:21:04 36 (null)
12:21:05 35 (null)
12:21:06 (null) 35
要生成这样的得让两个表做一次交叉之类的吧...有什么好解决方案吗?
--> 测试数据:@表一
declare @表一 table([datetime] varchar(8),[temperature] int)
insert @表一
select '12:21:00',23 union all
select '12:21:02',25 union all
select '12:21:04',36 union all
select '12:21:05',35 union all
select '12:21:08',35declare @表二 table([datetime] varchar(8),[temperature] int)
insert @表二
select '12:21:00',23 union all
select '12:21:01',25 union all
select '12:21:03',36 union all
select '12:21:06',35 union all
select '12:21:09',35;with maco as
(
select * from @表一
union
select * from @表二
)
select a.datetime,b.temperature,c.temperature from maco a
left join @表一 b on a.[datetime]=b.[datetime]
left join @表二 c on a.[datetime]=c.[datetime]
/*
datetime temperature temperature
-------- ----------- -----------
12:21:00 23 23
12:21:01 NULL 25
12:21:02 25 NULL
12:21:03 NULL 36
12:21:04 36 NULL
12:21:05 35 NULL
12:21:06 NULL 35
12:21:08 35 NULL
12:21:09 NULL 35
*/
go
create table table1([datetime] varchar(10), [temperature] int)
go
insert into table1
select '12:21:00', 23 union all
select '12:21:02', 25 union all
select '12:21:04', 36 union all
select '12:21:05', 35 union all
select '12:21:08', 35
go
if object_id(N'table2') is not null drop table table2
go
create table table2([datetime] varchar(10), [temperature] int)
go
insert into table2
select '12:21:00', 23 union all
select '12:21:01', 25 union all
select '12:21:03', 36 union all
select '12:21:06', 35 union all
select '12:21:09', 35
go;with cte as
(
select *,1 as lb from table1
union all
select *,2 from table2
)select [datetime],[1] as value1,[2] as value2 from cte
pivot
(sum([temperature]) for [lb] in([1],[2])) t/*(5 row(s) affected)(5 row(s) affected)
datetime value1 value2
---------- ----------- -----------
12:21:00 23 23
12:21:01 NULL 25
12:21:02 25 NULL
12:21:03 NULL 36
12:21:04 36 NULL
12:21:05 35 NULL
12:21:06 NULL 35
12:21:08 35 NULL
12:21:09 NULL 35(9 row(s) affected)
*/
CREATE TABLE ta2(d VARCHAR2(50),n NUMBER );INSERT INTO Ta1 VALUES('12:21:00', 23);
INSERT INTO Ta1 VALUES('12:21:02', 25);
INSERT INTO Ta1 VALUES('12:21:04', 36);
INSERT INTO Ta1 VALUES('12:21:05', 35);
INSERT INTO Ta1 VALUES('12:21:08', 35);INSERT INTO Ta2 VALUES('12:21:00', 23);
INSERT INTO Ta2 VALUES('12:21:01', 25);
INSERT INTO Ta2 VALUES('12:21:03', 36);
INSERT INTO Ta2 VALUES('12:21:06', 35);
INSERT INTO Ta2 VALUES('12:21:09', 35);COMMIT;SELECT nvl(a.d,b.d),a.n,b.n FROM
ta1 a FULL JOIN ta2 b ON a.d=b.d
ORDER BY nvl(a.d,b.d)
表一:
datetime temperature
12:21:00 23
12:21:02 25
12:21:04 36
12:21:05 35
12:21:08 35
......
表二:
datetime temperature
12:21:00 23
12:21:01 25
12:21:03 36
12:21:06 35
12:21:09 35
......
不过最终生成:
C# code
datetime value1 value2
12:21:00 23 23
12:21:01 (null) 25
12:21:02 25 (null)
12:21:03 (null) 36
12:21:04 36 (null)
12:21:05 35 (null)
12:21:06 (null) 35
......
是好答案就加分
IF OBJECT_ID('[ta]') IS NOT NULL DROP TABLE [ta]
GO
CREATE TABLE [ta]([datetime] DATETIME,[temperature] INT)
INSERT [ta]
SELECT '12:21:00',23 UNION ALL
SELECT '12:21:02',25 UNION ALL
SELECT '12:21:04',36 UNION ALL
SELECT '12:21:05',35 UNION ALL
SELECT '12:21:08',35
--------------开始查询----------------------------> 测试数据:[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([datetime] DATETIME,[temperature] INT)
INSERT [tb]
SELECT '12:21:00',23 UNION ALL
SELECT '12:21:01',25 UNION ALL
SELECT '12:21:03',36 UNION ALL
SELECT '12:21:06',35 UNION ALL
SELECT '12:21:09',35
--------------开始查询--------------------------
SELECT * FROM
(
SELECT *,1 AS value FROM [ta]
UNION ALL
SELECT *,2 FROM [tb]
)a
PIVOT (MAX([temperature]) FOR value IN ([1],[2]))b
/*
datetime 1 2
----------------------- ----------- -----------
12:21:00 23 23
12:21:01 NULL 25
12:21:02 25 NULL
12:21:03 NULL 36
12:21:04 36 NULL
12:21:05 35 NULL
12:21:06 NULL 35
12:21:08 35 NULL
12:21:09 NULL 35(9 行受影响)*/
我的数据有很多,难道要这样
SELECT '12:21:00',23 UNION ALL
SELECT '12:21:02',25 UNION ALL
SELECT '12:21:04',36 UNION ALL
SELECT '12:21:05',35 UNION ALL
................一条一条写吗? 我不太懂