我试图找出那些薪水低于他们最好朋友薪水的名字,我有三个表student(name,id(,friend_id(id,friend_id(和package(id,salary(。我尝试了一种方法,将所有三个表连接起来,然后自连接,然后比较薪水。这个解决方案有什么问题?
select s.name
from (
select *
from students
join friends on students.id=friends.id
join packages on friends.id=packages.id
) as s
join (
select *
from students
join friends on students.id=friends.id
join packages on friends.id=packages.id
) as b
where s.salary<b.salary
and s.Friend_ID=b.ID
order by b.salary;
我期待着一个输出,显示这样的学生的名字,他们的朋友的工资比他们的朋友高;
我得到这个结果
(select * from students join friends on students.id=friends.id join packages on friends.id=packages.id) as s join (select * from students join friends on students.id=friends.id join packages on friends.id=packages.id) as b where s.salary<b.salary and s.Friend_ID=b.ID order by b.salary
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
-
在Oracle中,表标识符或子查询与别名之间的
AS
是无效语法。在ANSI SQL中,它是可选的,但在Oracle中是禁止的。删除两个
AS
关键字。 -
一旦解决了这个问题,就会发现两个子查询之间有一个
JOIN
,而联接条件没有ON
子句。将联接的
WHERE
子句更改为ON
子句。
select s.name
from (
select *
from students
join friends on students.id=friends.id
join packages on friends.id=packages.id
) s
join (
select *
from students
join friends on students.id=friends.id
join packages on friends.id=packages.id
) b
on ( s.salary<b.salary
and s.Friend_ID=b.ID)
order by b.salary;
db<gt;小提琴这里