求一条拆分SQL语句

[王思明]

如何把下面的语句变成：
2012-09-05 2012-09-05 JD001 A中第六條 2012-09-05 JD000 B中第三條
2012-09-05 2012-09-05 JD001 A中第六條 2012-09-05 JD002 B中第六條

2012-09-05 2012-09-05 	JD001	A中第六條	'','',''
 '','',''                                       2012-09-05 	JD000	B中第三條
 '','',''                                	2012-09-05 	JD002	B中第六條

日期空格为1900-01-01 00:00:00也可以

[王思明]

下午發現你写错了，你把右边第四给吃掉了，我昨天一页都没有睡觉或者说真正睡觉并且睡着的时间不到
2个小时！

[王思明]

if object_id('TESTA')is not null  drop table TESTA
if object_id('TESTB') is not null drop table TESTB

create table TESTA
(
recdate smalldatetime,
partno varchar(100),
rmk nvarchar(100)
)
create table TESTB
(
recdate smalldatetime,
partno varchar(100),
rmk nvarchar(100)
)

insert TESTA
select '2012-09-01','JD000','A中第一條'
union select '2012-09-02','JD000','A中第二條'
union select '2012-09-04','JD000','A中第三條'
union select '2012-09-06','JD000','A中第四條'
union select '2012-09-11','JD001','A中第五條'
union select '2012-09-5','JD001','A中第六條'

insert TESTB
select '2012-09-01','JD000','B中第一條'
union select '2012-09-02','JD000','B中第二條'
union select '2012-09-05','JD000','B中第三條'
union select '2012-09-07','JD000','B中第四條'
union select '2012-09-06','JD001','B中第五條'
union select '2012-09-05','JD002','B中第六條'



select dateadd(dd,number,'2012-09-01') ,
isnull(a.recdate,'') as recdate,isnull(a.partno,'') as partno,isnull(a.rmk,'') as rmk,
isnull(b.recdate,'') as recdate,isnull(b.partno,'') as partno,isnull(b.rmk,'') as rmk
from master..spt_values  
left join TESTA  a on dateadd(dd,number,'2012-09-01') = a.recdate
left join TESTB  b on dateadd(dd,number,'2012-09-01') = b.recdate
where type = 'P' and number <11 and (a.recdate is not null or b.recdate is not null)

[王思明]

上面那个查询结果是：

2012-09-01 00:00:00.000	2012-09-01 00:00:00	JD000	A中第一條	2012-09-01 00:00:00	JD000	B中第一條
2012-09-02 00:00:00.000	2012-09-02 00:00:00	JD000	A中第二條	2012-09-02 00:00:00	JD000	B中第二條
2012-09-04 00:00:00.000	2012-09-04 00:00:00	JD000	A中第三條	1900-01-01 00:00:00		
2012-09-05 00:00:00.000	2012-09-05 00:00:00	JD001	A中第六條	2012-09-05 00:00:00	JD000	B中第三條
2012-09-05 00:00:00.000	2012-09-05 00:00:00	JD001	A中第六條	2012-09-05 00:00:00	JD002	B中第六條
2012-09-06 00:00:00.000	2012-09-06 00:00:00	JD000	A中第四條	2012-09-06 00:00:00	JD001	B中第五條
2012-09-07 00:00:00.000	1900-01-01 00:00:00			2012-09-07 00:00:00	JD000	B中第四條
2012-09-11 00:00:00.000	2012-09-11 00:00:00	JD001	A中第五條	1900-01-01 00:00:00		

[王思明]

我想要的查询结果：

2012-09-01 00:00:00.000	2012-09-01 00:00:00	JD000	A中第一條	2012-09-01 00:00:00	JD000	B中第一條
2012-09-02 00:00:00.000	2012-09-02 00:00:00	JD000	A中第二條	2012-09-02 00:00:00	JD000	B中第二條
2012-09-04 00:00:00.000	2012-09-04 00:00:00	JD000	A中第三條	1900-01-01 00:00:00		
2012-09-05 00:00:00.000	2012-09-05 00:00:00	JD001	A中第六條	1900-01-01 00:00:00	
2012-09-05 00:00:00.000	1900-01-01 00:00:00	                	2012-09-05 00:00:00	JD000	B中第三條
2012-09-05 00:00:00.000	1900-01-01 00:00:00	                    2012-09-05 00:00:00	JD002	B中第六條
2012-09-06 00:00:00.000	2012-09-06 00:00:00	JD000	A中第四條	1900-01-01 00:00:00	
2012-09-06 00:00:00.000                                         2012-09-06 00:00:00	JD001	B中第五條
2012-09-07 00:00:00.000	1900-01-01 00:00:00			            2012-09-07 00:00:00	JD000	B中第四條
2012-09-11 00:00:00.000	2012-09-11 00:00:00	JD001	A中第五條	1900-01-01 00:00:00		

[Alex20150716]

这个真不知道，帮你顶一下吧

[Felixzhaowenzhong]

没太看明白 但是可以给你个类似的案例

DECLARE @TAB TABLE ([col1] varchar(1),[col2] int,[col3] int)
insert @TAB
select 'a',1,2 union all
select 'a',2,3 union all
select 'a',3,4 union all
select 'b',4,5 union all
select 'b',5,6 union all
select 'b',6,7
--------------开始查询--------------------------
select
   case px when 1 then col1 else '' end as col1,
   col2,col3
from
   (select px=row_number()over(partition by col1 order by getdate()),* from @TAB)t
/*
col1	col2	col3
a	1	2
	2	3
	3	4
b	4	5
	5	6
	6	7
	*/

[汤姆克鲁斯]

SELECT  * FROM  TESTA  a  left join  TESTB  b on a.recdate= b.recdate
AND NOT EXISTS(SELECT 1 FROM TESTB WHERE recdate=b.recdate AND rmk<>b.rmk)
UNION ALL 
SELECT  a.recdate,'','',b.* FROM  TESTA  a   join  TESTB  b on a.recdate= b.recdate
AND  EXISTS(SELECT 1 FROM TESTB WHERE recdate=b.recdate AND rmk<>b.rmk)
ORDER BY a.recdate

[王思明]

谢谢了，万分感谢！我花了两个多小时都没写出来