用我们大家都熟悉的s_dept表来说明
1)update s_dept set dept_id=10 where id=2;如果我的s_dept表中有十个id=2的记录,这条语句会把这十条记录的dept_id值都改为10,现在我想把这十个dept_id的值依次改为1,2,3.....10,怎么做?(要使用update语句)
2)现在有两个表:表A,表B
A表中有aid,name两个字段
B表中有bid,name两个字段
现在AB中都有多条记录,其中name的值都是一样的,但是顺序不一样,要求查询A中的aid,并且以B表中的name为顺序,显示出来
A表 B表
1 a 1 b
2 b 2 c
3 c 3 a
要求输出结果
2
3
1
------解决方案--------------------
1） update s_dept set dept_id=(select rownum from s_dept where id=2) where id=2
2) 而题没太明白，按要求应该是输出： 3、1、2
------解决方案--------------------
1。
create table s_dept (
id number,
dept_id number,
name varchar2(10));
delete from s_dept;
insert into s_dept values(1,2, 'dasdfasf ');
insert into s_dept values(3,2, 'adsfsafsa ');
insert into s_dept values(2,1, 'tom ');
insert into s_dept values(2,1, 'jerry ');
insert into s_dept values(2,2, 'cat ');
insert into s_dept values(2,2, 'kk ');
insert into s_dept values(2,4, 'ccc ');
update s_dept set dept_id=rownum where id=2
2。
create table a(
aid number,
name varchar2(10));
create table b(
bid number,
name varchar2(10));
insert into a values(1, 'a ');
insert into a values(2, 'b ');
insert into a values(3, 'c ');
insert into b values(1, 'b ');
insert into b values(2, 'c ');
insert into b values(3, 'a ');
select aid from a,b where aid=bid order by b.name;
------解决方案--------------------
- SQL code
[TEST@ora10gr1#2009-11-04/08:37:07] SQL>--第一个[TEST@ora10gr1#2009-11-04/08:37:07] SQL>create table ta(taid int,id int);Table created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2);1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2);1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2);1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2);1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2);1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2);1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2);1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2);1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2);1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into ta values(1,2);1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>select * from ta; TAID ID---------- ---------- 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 210 rows selected.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>update ta set taid = rownum where id = 2;10 rows updated.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>select * from ta; TAID ID---------- ---------- 1 2 2 2 3 2 4 2 5 2 6 2 7 2 8 2 9 2 10 210 rows selected.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>[TEST@ora10gr1#2009-11-04/08:37:07] SQL>--第一个，同意2楼观点你说的有问题[TEST@ora10gr1#2009-11-04/08:37:07] SQL>create table a(aid int, name char(1));Table created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>create table b(bid int, name char(1));Table created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into a values(1,'a');1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into a values(2,'b');1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into a values(3,'c');1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into b values(1,'b');1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into b values(2,'c');1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>insert into b values(3,'a');1 row created.[TEST@ora10gr1#2009-11-04/08:37:07] SQL>select * from a; AID N---------- - 1 a 2 b 3 c[TEST@ora10gr1#2009-11-04/08:37:07] SQL>select * from b; BID N---------- - 1 b 2 c 3 a[TEST@ora10gr1#2009-11-04/08:37:07] SQL>--按照B表bid排序[TEST@ora10gr1#2009-11-04/08:37:07] SQL>select a.aid from a inner join b on a.name=b.name order by b.bid; AID---------- 2 3 1[TEST@ora10gr1#2009-11-04/08:37:07] SQL>--按照B表name排序[TEST@ora10gr1#2009-11-04/08:37:07] SQL>select a.aid from a inner join b on a.aid=b.bid order by b.name; AID---------- 3 1 2