求sql语句

[Xiao_Ai_Mei]

--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([date] datetime)
insert [t1]
select '2010-06-24' union all
select '2010-06-25' union all
select '2010-06-26' union all
select '2010-06-27'


--> 测试数据：[t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([date] datetime)
insert [t2]
select '2010-06-01' union all
select '2010-06-02' union all
select '2010-06-03'

--> 测试数据：[t3]
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([date] datetime)
insert [t3]
select '2010-06-10'


--怎么变成下面的格式
/*
t1             t2           t3 
2010-06-24  2010-06-01  2010-06-10
2010-06-25  2010-06-02
2010-06-26  2010-06-03
2010-06-27
*/

[rmljoe]

加序号，用序号关联，sql2005 ，2000就有点麻烦了

[Sharon_liu]

2000还是2005

[noteasytoregister]

加个Identity列，然后FULL JOIN

[sql_sf]

--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([date] datetime)
insert [t1]
select '2010-06-24' union all
select '2010-06-25' union all
select '2010-06-26' union all
select '2010-06-27'
--> 测试数据：[t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([date] datetime)
insert [t2]
select '2010-06-01' union all
select '2010-06-02' union all
select '2010-06-03'
--> 测试数据：[t3]
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([date] datetime)
insert [t3]
select '2010-06-10'
select a.[date],
       b.[date],
       c.[date]
from
(select px=row_number()over(order by (select 1)),*
 from [t1])a
left join
(select px=row_number()over(order by (select 1)),*
 from [t2])b on a.px=b.px
left join 
(select px=row_number()over(order by (select 1)),*
 from [t3])c on b.px=c.px
/*
date                    date                    date
----------------------- ----------------------- -----------------------
2010-06-24 00:00:00.000 2010-06-01 00:00:00.000 2010-06-10 00:00:00.000
2010-06-25 00:00:00.000 2010-06-02 00:00:00.000 NULL
2010-06-26 00:00:00.000 2010-06-03 00:00:00.000 NULL
2010-06-27 00:00:00.000 NULL                    NULL

(4 個資料列受到影響)


*/

[sql_sf]

如果不确定记录多少
改为full join

[Sharon_liu]

2005用ROW_NUMBER(),2000要用临时表
2005的写法

--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([date] datetime)
insert [t1]
select '2010-06-24' union all
select '2010-06-25' union all
select '2010-06-26' union all
select '2010-06-27'


--> 测试数据：[t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([date] datetime)
insert [t2]
select '2010-06-01' union all
select '2010-06-02' union all
select '2010-06-03'

--> 测试数据：[t3]
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([date] datetime)
insert [t3]
select '2010-06-10'

SELECT 
T1=ISNULL(CONVERT(VARCHAR(10),T1.date,120),'') 
,T2=ISNULL(CONVERT(VARCHAR(10),T2.date,120),'') 
,T3=ISNULL(CONVERT(VARCHAR(10),T3.date,120),'') 
FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) 'ID',[DATE] FROM T1) T1
LEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) 'ID',[DATE] FROM T2) T2 ON T1.ID=T2.ID
LEFT JOIN (SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) 'ID',[DATE] FROM T3) T3 ON T1.ID=T3.ID
/*
2010-06-24	2010-06-01	2010-06-10
2010-06-25	2010-06-02	
2010-06-26	2010-06-03	
2010-06-27		
*/

--怎么变成下面的格式
/*
t1             t2           t3 
2010-06-24  2010-06-01  2010-06-10
2010-06-25  2010-06-02
2010-06-26  2010-06-03
2010-06-27
*/

[sql_sf]

--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([date] datetime)
insert [t1]
select '2010-06-24' union all
select '2010-06-25' union all
select '2010-06-26' union all
select '2010-06-27'
--> 测试数据：[t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([date] datetime)
insert [t2]
select '2010-06-01' union all
select '2010-06-02' union all
select '2010-06-03'
--> 测试数据：[t3]
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([date] datetime)
insert [t3]
select '2010-06-10'
select a.[date],
       b.[date],
       c.[date]
from
(select px=row_number()over(order by (select 1)),*
 from [t1])a
full join
(select px=row_number()over(order by (select 1)),*
 from [t2])b on a.px=b.px
full join 
(select px=row_number()over(order by (select 1)),*
 from [t3])c on b.px=c.px
order by a.date 
/*
date                    date                    date
----------------------- ----------------------- -----------------------
2010-06-24 00:00:00.000 2010-06-01 00:00:00.000 2010-06-10 00:00:00.000
2010-06-25 00:00:00.000 2010-06-02 00:00:00.000 NULL
2010-06-26 00:00:00.000 2010-06-03 00:00:00.000 NULL
2010-06-27 00:00:00.000 NULL                    NULL
*/

[Sharon_liu]

SF姐姐说的对，是FULL JOIN....

--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
go
create table [t1]([date] datetime)
insert [t1]
select '2010-06-24' union all
select '2010-06-25' union all
select '2010-06-26' union all
select '2010-06-27'


--> 测试数据：[t2]
if object_id('[t2]') is not null drop table [t2]
go
create table [t2]([date] datetime)
insert [t2]
select '2010-06-01' union all
select '2010-06-02' union all
select '2010-06-03'

--> 测试数据：[t3]
if object_id('[t3]') is not null drop table [t3]
go
create table [t3]([date] datetime)
insert [t3]
select '2010-06-10'

SELECT 
T1=ISNULL(CONVERT(VARCHAR(10),T1.date,120),'') 
,T2=ISNULL(CONVERT(VARCHAR(10),T2.date,120),'') 
,T3=ISNULL(CONVERT(VARCHAR(10),T3.date,120),'') 
FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) 'ID',[DATE] FROM T1) T1
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) 'ID',[DATE] FROM T2) T2 ON T1.ID=T2.ID
FULL JOIN (SELECT ROW_NUMBER() OVER (ORDER BY [DATE]) 'ID',[DATE] FROM T3) T3 ON T1.ID=T3.ID
/*
2010-06-24	2010-06-01	2010-06-10
2010-06-25	2010-06-02	
2010-06-26	2010-06-03	
2010-06-27		
*/

--怎么变成下面的格式
/*
t1             t2           t3 
2010-06-24  2010-06-01  2010-06-10
2010-06-25  2010-06-02
2010-06-26  2010-06-03
2010-06-27
*/

[SQL_Hhy]

select t,max(case t when 1 then date else '' end),
max(case t when 2 then date else '' end),
max(case t when 3 then date else '' end)
from
(
select 1 as t,* from t1
union all
select 2,* from t1
union all
select 3,* from t1
) t
group by t
/*
t                                                           
----------- ----------------------- ----------------------- -----------------------
1           2010-06-27 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
2           1900-01-01 00:00:00.000 2010-06-27 00:00:00.000 1900-01-01 00:00:00.000
3           1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 2010-06-27 00:00:00.000

(3 行受影响)

*/

[Xiao_Ai_Mei]

O(∩_∩)O谢谢