我想用SQL语句实现:从上向下扫描记录,一旦NAME字段值为空,则取上一记录的NAME字段值),其显示结果由表1改为表2
表1:
ID NAME
1 #1
2
3
4 #2
5
6 #3
7 #4
8
9
10 #5
… …(以下类似,省略)
表2:
ID NAME
1 #1
2 #1
3 #1
4 #2
5 #2
6 #3
7 #4
8 #4
9 #4
10 #5
… …
表1:
ID NAME
1 #1
2
3
4 #2
5
6 #3
7 #4
8
9
10 #5
… …(以下类似,省略)
表2:
ID NAME
1 #1
2 #1
3 #1
4 #2
5 #2
6 #3
7 #4
8 #4
9 #4
10 #5
… …
解决方案 »
- wingate的"SOCKS Proxy server"中用户认证为什么一直用不了?100分求经验
- 没有出错,但就是没有更新,请大家帮忙
- 请教一个实现排除的SQL语句。在线等。
- 求单日内15分钟品均值的问题。(高分悬赏,还可加分)
- 三表联合查询!!
- sql2005 ROW_NUMBER() 一张表很快,两张表查询狂慢,不知道sql语句哪出问题了
- 高手救救我!!!
- 数据整合方案(做人难啊!)
- 欢迎讨论:师兄说DBMS中的约束,如FOREIGN KEY,CHECK,ASSERTION一般在具体系统中都不用,怕影响效率。真的吗?
- oracle白痴问题
- 请问一下,SQL报表中,自动生成需要1、2、3、4
- 为什么字符"-"不参与order by排序
insert @t
select 1,'#1' union all
select 2,NULL union all
select 3,NULL union all
select 4,'#2' union all
select 5,NULL union all
select 6,'#3' union all
select 7,'#4' union all
select 8,NULL union all
select 9,NULL union all
select 10,'#5'select id,name = isnull(name,(select name from @t where id = (select max(id) from @t where id < a.id and name is not null)))
from @t a
(select max(id) from tablename where id < a.id and name is not null)))
from tablename a
select id,name = isnull(name,(select name from db where id = (select max(id) from db where id < a.id and name is not null)))
from db a
以上db是我的表名,但运行时提示:用于函数参数的个数不对!如何修改该语句?
另问: a.id 是什么?是原表的复表或者视图映射么?
如果楼主使用一楼的测试数据查询成功的话,问题可能出在楼主的表上,请楼主把表结构贴出来,再加些测试数据.
--------------------------------
去看这个
www.idotnet.org/down/mssql.doc
理会了之后没你写不出来的.~
insert t
select 1,'#1' union all
select 2,NULL union all
select 3,NULL union all
select 4,'#2' union all
select 5,NULL union all
select 6,'#3' union all
select 7,'#4' union all
select 8,NULL union all
select 9,NULL union all
select 10,'#5'
select * from tdeclare mycur cursor
for
select * from t
open mycur
declare @name varchar(30),@id int,@curr_name varchar(30)
fetch next from mycur into @id,@name
set @curr_name=@name
while(@@fetch_status=0)
begin
if @name is null
update t set [name]=@curr_name where current of mycur
else
set @curr_name=@name
fetch next from mycur into @id,@name
endclose mycur
deallocate mycur
create table tb(id int,name varchar(10))
insert tb
select 1,'#1' union all
select 2,NULL union all
select 3,NULL union all
select 4,'#2' union all
select 5,NULL union all
select 6,'#3' union all
select 7,'#4' union all
select 8,NULL union all
select 9,NULL union all
select 10,'#5'select id,name = isnull(name,(select name from tb where id = (select max(id) from tb where id < a.id and name is not null)))
from tb a id name
----------- ----------
1 #1
2 #1
3 #1
4 #2
5 #2
6 #3
7 #4
8 #4
9 #4
10 #5(所影响的行数为 10 行)
我的表格在ACCESS中,希望能用SQL语句实现 :把空值行填满并只能和上面记录的值相等. 我的表结构很简单,2个字段:ID 数字类型 ,NAME 文本类型.表名为db. 这个表格有近上万行,所以只能针对以上10个记录是不行的,我用过各位的语句,
select id,name = isnull(name,(select name from db where id = (select max(id) from db where id < a.id and name is not null)))
from db a 但ACCESS运行时提示:用于函数参数的个数不对!
请各位大虾再帮我看看!谢谢!
select id,name = iif(name='',(select name from db where id = (select max(id) from db where id < a.id and name <>'')),name)
from db as a
试一下,可能有手误.
ACCESS有is null和 is not null 函数, 我用过name is null可以找到name为空的记录
我用您的语句select id,name = iif(name='',(select name from db where id = (select max(id) from db where id < a.id and name <>'')),name)
from db as a
只能返回如下结果:
id Expr1001
1 -1
2
3
4 -1
5
6 -1
7 -1
8
9
10 -1
不是我想要的填充效果
我写的语句的意思是我以为access会把null当空串即''处理我呼略了一点, jet sql不支持 别名=.. 的写法.
t-sql
select a='x' from tb
select 'x' a from tb
select 'x' as a from tb
jet sql
select 'x' as a from tb试一下.
SELECT id, iif(name is null,(select name from db where id = (select max(id) from db where id < a.id and name is not null)),name) AS NewName
FROM db AS a;
insert @a select 1,'#1'
union all select 2,null
union all select 3,null
union all select 4,'#4'
union all select 5,null
union all select 6,'#3'
union all select 7,'#4'
union all select 8,null
union all select 9,null
union all select 10,'#5'
select * from @a
select id,case when name is null then (select top 1 name from @a where name is not null and id<a.id order by id desc) else name end
from @a a