用我们大家都熟悉的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=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
2题没懂
where A.name = B.name
order by B.rowid asc;
select a.aid from a,b whre a.name=b.name order by b.name
select a.aid from a,b whre a.name=b.name order by b.aid
肯定一个是你意思吧
2)select a.aid from a,b whre a.name=b.name order by b.aid
但不知道有没有其他隐患。
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;
rownum外的方法2.
select aid from a,b where a.name=b.name(+)
[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
UPDATE s_dept SET dept_id = ROWNUM WHERE id = 2;
--2、
SELECT aid FROM A,B WHERE A.name = B.name ORDER BY bid;
UPDATE s_dept SET dept_id = ROWNUM WHERE id = 2;
--2、
SELECT aid FROM A,B WHERE A.name = B.name ORDER BY bid;
1> UPDATE s_dept SET dept_id = ROWNUM WHERE id = 2;2> SELECT a.aid FROM b,a
WHERE b.NAME=a.NAME
ORDER b.aid
1> UPDATE s_dept SET dept_id = ROWNUM WHERE id = 2;2> SELECT a.aid FROM b,a
WHERE b.NAME=a.NAME
ORDER b.aid