一个简略的查询

一个简单的查询

SQL code

USE tempdb ;GOCREATE TABLE Test(  A INT,  B INT);INSERT INTO Test VALUES(123,456),(NULL,789),(234,567),(NULL,678),(NULL,789);/*期待结果C123456123789234567234678234789*/

SQL code

USE tempdb ;GOif object_id('Test') is not null drop table TestCREATE TABLE Test(  A INT,  B INT);INSERT INTO Test VALUES(123,456)INSERT INTO Test VALUES(NULL,789)INSERT INTO Test VALUES(234,567)INSERT INTO Test VALUES(NULL,678)INSERT INTO Test VALUES(NULL,789)declare @tmp table(id int IDENTITY(1,1),A INT,B INT)insert into @tmp(A,B)select A,B from Testselect convert(varchar(10),case when t.A is not null then t.A else (select top 1 A from @tmp where id<t.id and A is not null order by id desc) end)+convert(varchar(10),t.B) from @tmp t/*--------------------123456123789234567234678234789*/
[解决办法]
这个的难点在产生一个id，1楼的方法可行，不过我觉得我的也不错，
code=SQL]USE tempdb ;
GO
CREATE TABLE Test
(
 A INT,
 B INT
);
INSERT INTO Test VALUES
(123,456),
(NULL,789),
(234,567),
(NULL,678),
(NULL,789);
/*期待结果
C
123456
123789
234567
234678
234789
*/
select ROW_NUMBER()over(order by getdate()) id ,a,b ,convert(varchar(8),a)+convert(varchar(8),b) as c,ROW_NUMBER()over(order by getdate())-1 as pid into #t
from test  

select t1.a,t1.b,case when t1.a is not null then convert(varchar(8),t1.a)+convert(varchar(8),t1.b)  
else  (select top 1 convert(varchar(8),a) from #t t2 where t2.id<t1.id and t2.a is not  null) +convert(varchar(8),t1.b)
end c
from #t t1 [/code]