一个member TABLE
包括FamilyName,GivenName,dateOfBirth date内容。
想要安排出席者入座一张18人长桌的两边(每边9个座位)这样年纪最大的member和年纪最轻的member对坐,然后依次排下去(第二老的和第二年轻的对坐)
写出sql program来创建两排名字列表,就根据座位安排的计划。 应该怎么写
包括FamilyName,GivenName,dateOfBirth date内容。
想要安排出席者入座一张18人长桌的两边(每边9个座位)这样年纪最大的member和年纪最轻的member对坐,然后依次排下去(第二老的和第二年轻的对坐)
写出sql program来创建两排名字列表,就根据座位安排的计划。 应该怎么写
解决方案 »
- 存储过程 行列转置
- update 简单问题提示单行字返回多行
- 多个记录用order by排序后,如何用SQL语法只保留结果的第一条记录,而删除所有其它记录?
- Oracle数据库临时表空间的问题,在线等待,请高手指点!!!
- ORACLE
- oracle中能不能执行select * into t3 from t2,这样的语句
- oracle中怎样得到某表某列的默认值
- 如何用從一個表來更新另一個表的多個字段
- 一个监听器的问题(oracle817 for linux).
- AIX 备份ORACLE数据库控制文件成功但是打开后空白
- 请教大家一个关于丢失更新的问题。
- 64位oracle10g,没有Oracle.DataAccess.dll的问题
select a.GivenName GivenName_old,b.GivenName GivenName_young
from
(
select FamilyName,GivenName,row_number() over(order by dateOfBirth) rn
from member
) a,
(
select FamilyName,GivenName,row_number() over(order by dateOfBirth) rn
from member
) b
where a.rn=b.rn
and a.rn<=9 and b.rn<=9
我在9i里面运行是提示
ERROR at line 4:
ORA-00911: invalid character
的
Family Name: Swanson
Given Name: Yunfei
Date of Birth: 44-AUG-1987一共18个人。
怎么列出来2排表里一共就这3项数据
(memberNo char(3) primary key,
FamilyName varchar(15) not null,
GivenName varchar(15),
street varchar(30),
suburb varchar(20),
postcode char(4),
phone varchar(15),
dateOfBirth date,
dateJoined date,
gender char(1),
feesPaid number(5,2),
registrationNumber number,
CONSTRAINT registrationNumber_un UNIQUE (registrationNumber));INSERT INTO member VALUES(123,'Jenkins','Harry','32 High St','Launceston','7250','(03)6312 3456',
'17-MAR-1968','18-JUN-1995','M', 75, 2351);
INSERT INTO member VALUES(124,'Jenkins','Susie','32 High St','Launceston','7250','(03)6312 3456',
'27-MAR-1971','18-JUN-1995','F', 75, 2396);
INSERT INTO member VALUES(162,'Hawkins','Steven','2 Jenkins St','Launceston','7250','(03)6334 1256',
'14-AUG-1972','28-JUN-2001','M', 0, 2321);
INSERT INTO member VALUES(163,'Hawkins','Susie','2 Jenkins St','Launceston','7250','(03)6334 1256',
'03-JAN-1971','02-JUN-2006','F', 0, 2345);
INSERT INTO member VALUES(12,'Martin','Mary','22 High St','Launceston','7250','(03)6312 3468',
'07-DEC-1958','18-JUL-1990','F', 75, 2352);
INSERT INTO member VALUES(14,'Martin','Kevin','22 High St','Launceston','7250','(03)6312 3468',
'30-NOV-1961','20-JUL-1990','M', 75, 2387);
INSERT INTO member VALUES(262,'Tucker','Tommy','2 Corner Square','Launceston','7250','(03)6334 4256',
'17-AUG-1972','28-JUN-2002','M', 50, 2360);
INSERT INTO member VALUES(263,'Greene','Lorne','2 Hawkins St','Launceston','7250','(03)6334 6767',
'10-FEB-1973','31-AUG-2002','M', 60, 2325);
INSERT INTO member VALUES(3,'Atrick','Gerry','27 Sunset Court','Launceston','7250','(03)6334 2418',
'22-OCT-1944','21-MAY-1990','M', 40, null);
INSERT INTO member VALUES(4,'Atrick','Maisie','27 Sunset Court','Launceston','7250','(03)6334 2418',
'01-AUG-1947','28-JUN-1990','F', 40, null);
INSERT INTO member VALUES(285,'Tillizer','Krys','2 Smithy Lane','Riverside','7249','(03)6334 6921',
'27-SEP-1983','28-APR-2005','F', 75, 2362);
INSERT INTO member VALUES(284,'Tillizer','Fleur','2 Smithy Lane','Riverside','7249','(03)6334 6921',
'27-SEP-1983','28-APR-2005','F', 75, 2361);
INSERT INTO member VALUES(248,'Tuckwell','Barry','12 Fountain Court','Launceston','7250','(03)6332 4267',
'17-MAY-1963','18-JUN-2006','M', 75, 2355);
INSERT INTO member VALUES(249,'deGrass','Moe','127 Main Road','Launceston','7250','(03)6332 4271',
'19-APR-1972','18-JUN-2002','M', 75, 2356);
INSERT INTO member VALUES(250,'Clippers','Neil','2 Side Street','Launceston','7250','(03)6332 4248',
'11-MAY-1965','18-JUN-2002','M', 75, 2357);
INSERT INTO member VALUES(251,'Smith','Quentin','12 Side Street','Launceston','7250',null,
'31-DEC-1963','05-DEC-2002','M', 75, 2358);脚本的原版是这样的这里是16个数据,在之前要求里还要增加上2个数据
运行这个时
select a.familyname, a.givenname, b.familyname, b.givenname
from
(select row_number(),c.familyname, c.givenname from member c order by c.dateofbirth desc) a,
(select row_number(),d.familyname, d.givenname from member d order by d.dateofbirth asc) b
where a.rn=b.rn
and a.rn <=9 and b.rn <=9;出现
ERROR at line 3:
ORA-30484: missing window specification for this function
你语法错误
用rownum也可以实现的select aa.familyname, aa.givenname,bb.familyname, bb.givenname,(select a.*,rownum rn
from
(
select c.familyname, c.givenname from member c order by c.dateofbirth desc
)
where rownum<=9) aa,
(select a.*,rownum rn
from
(
select c.familyname, c.givenname from member c order by c.dateofbirth asc
)
where rownum<=9)where aa.rn=bb.rn
这样也可以的
over(partition by 分组条件 order by 排序条件 )
对于取总体排名(编号)
row_number( ) over用法和rownum是一样的
不过对于分组排名,就要用到row_number() over了