表kkk
列 a e
1 b
2
3 c
4 d
5
6 g
7 i
8 t
9 l
10
11 p要求,e列=''的更新为a-1的e列。
结果:
列 a e
1 b
2 b
3 c
4 d
5 d
6 g
7 i
8 t
9 l
10 l
11 p
列 a e
1 b
2
3 c
4 d
5
6 g
7 i
8 t
9 l
10
11 p要求,e列=''的更新为a-1的e列。
结果:
列 a e
1 b
2 b
3 c
4 d
5 d
6 g
7 i
8 t
9 l
10 l
11 p
解决方案 »
- 数据库无法查询中文的问题
- sql2000 联合查询2个表,where条件用到2个表的id,字段名一样,结果查不出数据!!!
- 求一SQL语句
- 如何用sql语句取出表中所有字段的名称,类型,长度,注释等
- 菜鸟求救,怎么通过身份证号码判断性别
- 求SQL查询语句。
- Sql Server 2000数据丢失,因为突然断电,当开机后发现断电之前的数据都没有保存下来,能用什么方法恢复吗? 急!~急急急。
- 查询分析器可用,企业管理器不能用,请问什么原因?
- 如何区分sql anywhere中的用户表格
- 一个为有趣的sql问题:数据从一个表到另一个表,竟变为了<![CDATA[]]>,各位大虾不可错过哦
- sql 2000 连接 sql 2008
- SQL SERVER 2008 链接服务器 建立 excel 2007文件链接
update a set e =(select e from kkk where a=a.a-1) from kkk a
where isnull(e,'')=''
/***********************************************--> 测试数据:KKKK
--> 测试时间:2009-08-06 17:21:21
--> 我的淘宝:<<戒色坊>> http://shop36766744.taobao.com/***********************************************/if object_id('[kkk]') is not null drop table [kkk]
create table [kkk]([a] int,[e] varchar(1))
insert [kkk]
select 1,'b' union all
select 2,null union all
select 3,'c' union all
select 4,'d' union all
select 5,null union all
select 6,'g' union all
select 7,'i' union all
select 8,'t' union all
select 9,'l' union all
select 10,null union all
select 11,'p'
update KKK set e=(select top 1 e from KKK where a<t.a)from KKK t where e is nulldrop table TTT
set e=(select e from kkk where k.a=a+1)
from kkk k
where e is null
if object_id('[kkk]') is not null drop table [kkk]
go
create table [kkk]([a] int,[e] varchar(1))
insert [kkk]
select 1,'b' union all
select 2,null union all
select 3,'c' union all
select 4,'d' union all
select 5,null union all
select 6,'g' union all
select 7,'i' union all
select 8,'t' union all
select 9,'l' union all
select 10,'' union all
select 11,'p'
---更新---
update
t
set
e=case when len(isnull(e,''))=0 then (select e from kkk where a=t.a-1) else e end
from
kkk t---查询---
select * from [kkk]
---结果---
a e
----------- ----
1 b
2 b
3 c
4 d
5 d
6 g
7 i
8 t
9 l
10 l
11 p(所影响的行数为 11 行)
if object_id('[kkk]') is not null drop table [kkk]
create table [kkk]([a] int,[e] varchar(1))
insert [kkk]
select 1,'b' union all
select 2,null union all
select 3,'c' union all
select 4,'d' union all
select 5,null union all
select 6,'g' union all
select 7,'i' union all
select 8,'t' union all
select 9,'l' union all
select 10,null union all
select 11,'p'
update k
set e=(select e from kkk where k.a=a+1)
from kkk k
where e is nullselect * from kkk
/*
a e
----------- ----
1 b
2 b
3 c
4 d
5 d
6 g
7 i
8 t
9 l
10 l
11 p
*/
Drop table [kkk]
Go
Create table [kkk]([a] int,[e] nvarchar(1))
Insert kkk
Select 1,'b' union all
Select 2,'' union all
Select 3,'c' union all
Select 4,'d' union all
Select 5,'' union all
Select 6,'g' union all
Select 7,'i' union all
Select 8,'t' union all
Select 9,'l' union all
Select 10,'' union all
Select 11,'p'
Go
--Select * from kkk-->SQL查询如下:
declare @e varchar(10)
update kkk set e=@e,@e=case when e='' then isnull(@e,e) else e endselect * from kkk
/*
a e
----------- ----
1 b
2 b
3 c
4 d
5 d
6 g
7 i
8 t
9 l
10 l
11 p(11 行受影响)
*/
update a set e =b.e from kkk a left join kkk b
on a.a=b.a+1 where isnull(a.e,'')=''
update k
set e=(select e from kkk where k.a=a+1)
from kkk k
where e is null
update Tb
set e=b.e
from Tb e where Tb.a=e.a-1 and Tb.e is null