SQl 查询疑难，请教各位高手
如下数据：
abcd
12121123
223220
354450
423130
513230
624430
75453256
843310
913210
1054340
1123210
1221230
132323145
1424410
1511110
1621320
1712230
数据为有序的，c字段的为0的数据与之前最接近的不为0的数据为一类。如何标示？
如在数据为0的行的d字段标识为b字段的数据。
如下所示：
abcd
12121123
2232102121
3544502121
4231302121
5132302121
6244302121
75453256
8433105453
9132105453
10543405453
11232105453
12212305453
132323145
14244102323
15111102323
16213202323
17122302323
请教SQL应该怎么写？


[解决办法]
create table #aa(a int,b int,c int,d int)
insert #aa(a,b,c) select
1,2121,123 union select
2,2322,0 union select
3,5445,0 union select
4,2313,0 union select
5,1323,0 union select
6,2443,0 union select
7,5453,256 union select
8,4331,0 union select
9,1321,0 union select
10,5434,0 union select
11,2321,0 union select
12,2123,0 union select
13,2323,145 union select
14,2441,0 union select
15,1111,0 union select
16,2132,0 union select
17,1223,0
select *,(select top 1 b from #aa B where B.c <> 0 and A.c=0 and B.a <A.a order by A.a-B.a)d from #aa A order by a

a b c d d
----------- ----------- ----------- ----------- -----------
1 2121 123 NULL NULL
2 2322 0 NULL 2121
3 5445 0 NULL 2121
4 2313 0 NULL 2121
5 1323 0 NULL 2121
6 2443 0 NULL 2121
7 5453 256 NULL NULL
8 4331 0 NULL 5453
9 1321 0 NULL 5453
10 5434 0 NULL 5453
11 2321 0 NULL 5453
12 2123 0 NULL 5453
13 2323 145 NULL NULL
14 2441 0 NULL 2323
15 1111 0 NULL 2323
16 2132 0 NULL 2323
17 1223 0 NULL 2323

（所影响的行数为 17 行）

[解决办法]
update a
set d=(select top 1 b from tablename where a <a.a and c <> 0 order by a desc)
from tablename a
where c=0

[解决办法]
try:
CREATE TABLE #T(a int,b int,c int,d int)
INSERT INTO #T(a,b,c)
SELECT 1,2121,123UNION ALL
SELECT 2,2322,0UNION ALL
SELECT 3,5445,0UNION ALL
SELECT 4,2313,0UNION ALL
SELECT 5,1323,0UNION ALL
SELECT 6,2443,0UNION ALL
SELECT 7,5453,256UNION ALL
SELECT 8,4331,0UNION ALL
SELECT 9,1321,0UNION ALL
SELECT 10,5434,0UNION ALL
SELECT 11,2321,0UNION ALL
SELECT 12,2123,0UNION ALL
SELECT 13,2323,145UNION ALL
SELECT 14,2441,0UNION ALL
SELECT 15,1111,0UNION ALL
SELECT 16,2132,0UNION ALL
SELECT 17,1223,0


SELECT A.a,A.b,A.c ,B.a AS ba,B.b AS d INTO #T1 FROM #T AS A LEFT OUTER JOIN
(SELECT * FROM #T AS A1 WHERE A1.c <> 0 AND EXISTS(SELECT 1 FROM #T AS B1 WHERE B1.c=0 AND B1.a-1=A1.a))

AS B
ON B.a <A.a AND A.c=0
SELECT A.a,A.b,A.c,A.d FROM #T1 AS A WHERE NOT EXISTS(SELECT 1 FROM #T1 AS B WHERE A.a=B.a AND B.ba> A.ba)

DROP TABLE #T ,#T1

/*
abc d
-----------------------------
12121123NULL
2232202121
3544502121
4231302121
5132302121
6244302121
75453256NULL
8433105453
9132105453
10543405453
11232105453
12212305453
132323145NULL
14244102323
15111102323
16213202323
17122302323
*/