一条让人难以下手的sql语句?
表a的结构为
id point
1 (12)
(34)
(56)
2 (12)
3 (34)
(5)
4 (8)
5 (9)
把她导出到b表
结果为
id point
1 (12)
1 (34)
1 (56)
2 (12)
3 (34)
3 (5)
4 (8)
5 (9)
a表的回车键不知如何处理!谢谢大家了，在线等.....
[解决办法]
create table #z(id int, point varchar(20))
insert into #z
select 1 , '(12) ' union all
select null , '(34) ' union all
select null , '(56) ' union all
select 2 , '(12) ' union all
select 3 , '(34) ' union all
select null , '(5) ' union all
select 4 , '(8) ' union all
select 5 , '(9) '
select cid=identity(int,1,1), * into #aa from #z
select point,(select max(id) from #aa where isnull(id, ' ') <> ' ' and a.cid> =cid ) from #aa a
/*
point
-------------------- -----------
(12) 1
(34) 1
(56) 1
(12) 2
(34) 3
(5) 3
(8) 4
(9) 5
（所影响的行数为 8 行）
*/
[解决办法]
create table #a (sid varchar(1), point varchar(10))
insert #a
select '1 ', '(12) ' union all
select ' ', '(34) ' union all
select ' ', '(56) ' union all
select '2 ', '(12) ' union all
select '3 ', '(34) ' union all
select ' ', '(5) ' union all
select '4 ', '(8) ' union all
select '5 ', '(0) '
select id = identity(int,1,1), * into #b from #a
declare @sid varchar(1)
select sid = (case when isnull(sid, ' ') = ' ' then (select top 1 b1.sid from #b b1 where isnull(sid, ' ') <> ' ' and b1.id < b2.id order by b1.id desc)
else sid end),point from #b b2
go
drop table #b
go
drop table #a
