重新构造数据
数据表Table1中的数据格式如下：
发货仓库 发货日期 发货单号
A 2012-10-11 JD001
A 2012-10-12 JD002
A 2012-10-13 JD003
B 2012-10-12 JD004
B 2012-10-13 JD005
如何将数据表Table1中的数据构造成下面的格式
仓库A发货日期 仓库A发货单号 仓库B发货日期 仓库B发货单号
2012-10-11 JD001 2012-10-12 JD004
2012-10-12 JD002 2012-10-13 JD005
2012-10-13 JD003
[最优解释]
----------------------------
-- Author :TravyLee(物是人非事事休，欲语泪先流！)
-- Date :2012-11-24 13:03:35
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
--Jul 9 2008 14:43:34
--Copyright (c) 1988-2008 Microsoft Corporation
--Developer Edition on Windows NT 6.1 <X86> (Build 7600: )
--
----------------------------
--> 测试数据：[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[发货仓库] varchar(1),
[发货日期] datetime,
[发货单号] varchar(5)
)
insert [test]
select 'A','2012-10-11','JD001' union all
select 'A','2012-10-12','JD002' union all
select 'A','2012-10-13','JD003' union all
select 'B','2012-10-12','JD004' union all
select 'B','2012-10-13','JD005'
go
with
t
as(
select
px=ROW_NUMBER()over(order by [发货日期]),
*
from
test
where
[发货仓库]='A'
),
m as
(
select
px=ROW_NUMBER()over(order by [发货日期]),
*
from
test
where
[发货仓库]='B'
)
select
t.发货日期 as 仓库A发货日期,
t.发货单号 as 仓库A发货单号,
m.发货日期 as 仓库B发货日期,
m.发货单号 as 仓库B发货单号
from
t
full join
m
on
t.px=m.px
/*
仓库A发货日期仓库A发货单号仓库B发货日期仓库B发货单号
---------------------------------------
2012-10-11 00:00:00.000JD0012012-10-12 00:00:00.000JD004
2012-10-12 00:00:00.000JD0022012-10-13 00:00:00.000JD005
2012-10-13 00:00:00.000JD003NULLNULL
*/
