求一条拆分SQL语句
如何把下面的语句变成:
2012-09-05 2012-09-05 JD001A中第六條2012-09-05 JD000B中第三條
2012-09-05 2012-09-05 JD001A中第六條2012-09-05 JD002B中第六條
2012-09-05 2012-09-05 JD001 A中第六條 '','','' '','','' 2012-09-05 JD000 B中第三條 '','','' 2012-09-05 JD002 B中第六條
DECLARE @TAB TABLE ([col1] varchar(1),[col2] int,[col3] int)insert @TABselect 'a',1,2 union allselect 'a',2,3 union allselect 'a',3,4 union allselect 'b',4,5 union allselect 'b',5,6 union allselect 'b',6,7--------------开始查询--------------------------select case px when 1 then col1 else '' end as col1, col2,col3from (select px=row_number()over(partition by col1 order by getdate()),* from @TAB)t/*col1 col2 col3a 1 2 2 3 3 4b 4 5 5 6 6 7 */
[解决办法]
SELECT * FROM TESTA a left join TESTB b on a.recdate= b.recdateAND 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.recdateAND EXISTS(SELECT 1 FROM TESTB WHERE recdate=b.recdate AND rmk<>b.rmk)ORDER BY a.recdate