c*********l
发帖数: 17 | 1 If I have two relations:
Employee(SSN, salary, name) SSN is primary key
Position(pID, pName, state) pID is primary key, state is the US state in
which a position released
And one relationship which link the two tables:
EP(SSN, pID, tDate) SSN, pID is primary key. tDate is the date on which
position was taken
Now I wanna write a SQL to retrieve data that meet requirement below from the
database: List the last four positions that took place in Illinois
Please give my some ideas, thank | n********a
发帖数: 68 | 2 The question can be interpreted by different ways.
I took it mean that find the lastest four dates and
give me every position.
In Oracle 8.1.6 upper, you could do:
select pName from (
select position.pName,
dense_rank() over (order by EP.tDate desc nulls last) dr
from EP, Position
where EP.pID=position.pID and Position.state='Illinois'
) where dr <= 4;
If your database supports inline view and rownum, you could do
select pName from (
select position.pName
from EP, Position
where EP.pID=position.
【在 c*********l 的大作中提到】
: If I have two relations:
: Employee(SSN, salary, name) SSN is primary key
: Position(pID, pName, state) pID is primary key, state is the US state in
: which a position released
: And one relationship which link the two tables:
: EP(SSN, pID, tDate) SSN, pID is primary key. tDate is the date on which
: position was taken
: Now I wanna write a SQL to retrieve data that meet requirement below from the
: database: List the last four positions that took place in Illinois
: Please give my some ideas, thank
|
|
