我有一张表:
col1 col2
suttf 1
2
3
address 1
2
tabother 1
2
3
4
怎样将col1的空值补上啊?
我要返回的结果是:
col1 col2
suttf 1
suttf 2
suttf 3
address 1
address 2
tabother 1
tabother 2
tabother 3
tabother 4
谢谢各位!!
col1 col2
suttf 1
2
3
address 1
2
tabother 1
2
3
4
怎样将col1的空值补上啊?
我要返回的结果是:
col1 col2
suttf 1
suttf 2
suttf 3
address 1
address 2
tabother 1
tabother 2
tabother 3
tabother 4
谢谢各位!!
@temp=col1=ISNULL(@temp,col1),
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (col1 VARCHAR(8),col2 INT)
INSERT INTO @T
SELECT 'suttf',1 UNION ALL
SELECT null,2 UNION ALL
SELECT null,3 UNION ALL
SELECT 'address',1 UNION ALL
SELECT null,2 UNION ALL
SELECT 'tabother',1 UNION ALL
SELECT null,2 UNION ALL
SELECT null,3 UNION ALL
SELECT null,4--SQL查询如下:DECLARE @temp varchar(20)UPDATE @T SET
@temp=col1=ISNULL(col1,@temp)
SELECT * FROM @T
/*
col1 col2
-------- -----------
suttf 1
suttf 2
suttf 3
address 1
address 2
tabother 1
tabother 2
tabother 3
tabother 4(9 行受影响)
*/
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (col1 VARCHAR(8),col2 INT)
INSERT INTO @T
SELECT 'suttf',1 UNION ALL
SELECT null,2 UNION ALL
SELECT null,3 UNION ALL
SELECT 'address',1 UNION ALL
SELECT null,2 UNION ALL
SELECT 'tabother',1 UNION ALL
SELECT '',2 UNION ALL
SELECT null,3 UNION ALL
SELECT null,4--SQL查询如下:DECLARE @temp varchar(20)UPDATE @T SET
@temp=col1=ISNULL(NULLIF(col1,''),@temp)
SELECT * FROM @T
/*
col1 col2
-------- -----------
suttf 1
suttf 2
suttf 3
address 1
address 2
tabother 1
tabother 2
tabother 3
tabother 4(9 行受影响)*/
此表没有ID标识列吗?
有的话create table #1(id int identity(1,1),col1 nvarchar(80),col2 int)insert #1 values('suttf',1)
insert #1 values('',2)
insert #1 values('',3)
insert #1 values('address',1)
insert #1 values('',2)
insert #1 values('tabother',1)
insert #1 values('',2)
insert #1 values('',3)
insert #1 values('',4)update a
set col1 = (select top 1 col1 from #1 where col1 <> '' and a.id - id > 0 order by a.id - id )
from #1 a
where col1 = '' or col1 is nullselect * from #1
id col1 col2
----------- -------------------------------------------------------------------------------- -----------
1 suttf 1
2 suttf 2
3 suttf 3
4 address 1
5 address 2
6 tabother 1
7 tabother 2
8 tabother 3
9 tabother 4(9 row(s) affected)
@temp=col1=ISNULL(col1,@temp)
SELECT * FROM table1