日期:2014-05-17 浏览次数:20621 次
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 */
USE tempdb ; GO if object_id('Test') is not null drop table Test CREATE 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 Test select 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 /* -------------------- 123456 123789 234567 234678 234789 */
------解决方案--------------------
这个的难点在产生一个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]