求一sql语句 100分

[imho888]

有三个表A,B,C相同结构如下:

Time，Data

如：

A B C

Time Data Time Data Time Data

08:59 12 07:59 1 08:59 23
09:00 13 09:00 2 11:00 25
10:01 15 11:00 3 12:00 26

以时间排列，组合数据如下：
Time A_Data B_Data C_Data
07:59 1
08:59 12 23
09:00 13 2
10:01 15
11:00 3 25
12:00 26


sql 最后精简一些，运行速度快一点，非常感谢，这贴限制最多只能给100分，分不够说一声，我另开贴加分。


非常感谢。

[AcHerat]

;with cte as
(
    select time from a
    union
    select time from b
    union
    select time from c
)

select t.time,a.data as A_data,b.data as B_data,c.data as C_data
from cte t left join a on t.time = a.time
           left join b on t.time = b.time
           left join c on t.time = c.time

[百年树人]

if object_id('[a]') is not null drop table [a]
go
create table [a]([Time] varchar(5),[Data] int)
insert [a]
select '08:59',12 union all
select '09:00',13 union all
select '10:01',15
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([Time] varchar(5),[Data] int)
insert [b]
select '07:59',1 union all
select '09:00',2 union all
select '11:00',3
go
if object_id('[c]') is not null drop table [c]
go
create table [c]([Time] varchar(5),[Data] int)
insert [c]
select '08:59',23 union all
select '11:00',25 union all
select '12:00',26
go

select isnull(isnull(a.time,b.time),c.time) as time,
a.data,b.data,c.data
from a
full join b on a.time=b.time
full join c on a.time=c.time or b.time=c.time

/**
time  data        data        data
----- ----------- ----------- -----------
08:59 12          NULL        23
09:00 13          2           NULL
10:01 15          NULL        NULL
07:59 NULL        1           NULL
11:00 NULL        3           25
12:00 NULL        NULL        26

(6 行受影响)
**/

[百年树人]

排一下序

select isnull(isnull(a.time,b.time),c.time) as time,
a.data,b.data,c.data
from a
full join b on a.time=b.time
full join c on a.time=c.time or b.time=c.time
order by 1

/**
time  data        data        data
----- ----------- ----------- -----------
07:59 NULL        1           NULL
08:59 12          NULL        23
09:00 13          2           NULL
10:01 15          NULL        NULL
11:00 NULL        3           25
12:00 NULL        NULL        26

(6 行受影响)
**/

[imho888]

[Quote=引用 1 楼 acherat 的回复:]

SQL code

;with cte as
(
select time from a
union
select time from b
union
select time from c
)

select t.time,a.data as A_data,b.data as B_data,c.data as C_data
from cte t left ……
[/Quote]



执行不了，with 附近有语法错误

[AcHerat]

那就看树哥的。。。

[imho888]

树哥的没问题，thank you

[勿勿]

--A B C

--Time Data Time Data Time Data

--08:59 12 07:59 1 08:59 23  
--09:00 13 09:00 2 11:00 25
--10:01 15 11:00 3 12:00 26


 IF OBJECT_ID('A') is not null 
drop table A
go 
create table  A
(  [Time]  varchar(50),
    Data int,)
insert into A values('08:59' ,12 )
insert into A values('09:00' ,13 )
insert into A values('10:01' ,15 )

 IF OBJECT_ID('B') is not null 
drop table B
go 
create table  B
(  [Time]  varchar(50),
    Data int,)
insert into B values('07:59' ,1 )
insert into B values('09:00' ,2 )
insert into B values('11:00' ,3 )


 IF OBJECT_ID('C') is not null 
drop table C
go 
create table  C
(  [Time]  varchar(50),
    Data int,)
insert into C values('08:59' ,23 )
insert into C values('11:00' ,25 )
insert into C values('12:00' ,26)
--以时间排列，组合数据如下：
--Time A_Data B_Data C_Data
--07:59 1   
--08:59 12 23
--09:00 13 2
--10:01 15
--11:00 3 25
--12:00 26
 
 ;with cte as
(
    select time from a
    union
    select time from b
    union
    select time from c
)

select t.time,a.data as A_data,b.data as B_data,c.data as C_data
from cte t left join a on t.time = a.time
           left join b on t.time = b.time
           left join c on t.time = c.time
 


time                                               A_data      B_data      C_data
-------------------------------------------------- ----------- ----------- -----------
07:59                                              NULL        1           NULL
08:59                                              12          NULL        23
09:00                                              13          2           NULL
10:01                                              15          NULL        NULL
11:00                                              NULL        3           25
12:00                                              NULL        NULL        26

(6 行受影响)