SQl 查询疑难，请教各位高手
如下数据：
a b c d
1 2121 123
2 2322 0
3 5445 0
4 2313 0
5 1323 0
6 2443 0
7 5453 256
8 4331 0
9 1321 0
10 5434 0
11 2321 0
12 2123 0
13 2323 145
14 2441 0
15 1111 0
16 2132 0
17 1223 0
数据为有序的，c字段的为0的数据与之前最接近的不为0的数据为一类。如何标示？
如在数据为0的行的d字段标识为b字段的数据。
如下所示：
a b c d
1 2121 123
2 2321 0 2121
3 5445 0 2121
4 2313 0 2121
5 1323 0 2121
6 2443 0 2121
7 5453 256
8 4331 0 5453
9 1321 0 5453
10 5434 0 5453
11 2321 0 5453
12 2123 0 5453
13 2323 145
14 2441 0 2323
15 1111 0 2323
16 2132 0 2323
17 1223 0 2323
请教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 ,123 UNION ALL
SELECT 2 ,2322 ,0 UNION ALL
SELECT 3 ,5445 ,0 UNION ALL
SELECT 4 ,2313 ,0 UNION ALL
SELECT 5 ,1323 ,0 UNION ALL
SELECT 6 ,2443 ,0 UNION ALL
SELECT 7 ,5453 ,256 UNION ALL
SELECT 8 ,4331 ,0 UNION ALL
SELECT 9 ,1321 ,0 UNION ALL
SELECT 10 ,5434 ,0 UNION ALL
SELECT 11 ,2321 ,0 UNION ALL
SELECT 12 ,2123 ,0 UNION ALL
SELECT 13 ,2323 ,145 UNION ALL
SELECT 14 ,2441 ,0 UNION ALL
SELECT 15 ,1111 ,0 UNION ALL
SELECT 16 ,2132 ,0 UNION 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=A