日期:2014-05-17  浏览次数:20496 次

求sql语句,进来看看吧,应该是不难。。。学的不好
有这样两个表,
表一:  
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

要生成这样的得让两个表做一次交叉之类的吧...有什么好解决方案吗?


------解决方案--------------------
SQL code

--> 测试数据:@表一
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',35

declare @表二 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
*/

------解决方案--------------------
SQL code
if object_id(N'table1') is not null drop table table1
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 ta1(d VARCHAR2(50),n NUMBER );
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