有两张表genaral(将军)和position(职位),
position表的name(职位名称)主键列是genaral表的外键gposition
potision表有一个列rank(职位等级),标明了0-10的数字
现在我要查询genaral表所有将军信息,并以position表的rank排序我写的如下select *
from genaral
order by gposition=(select * from position order by rank)
是错的,请教高人解答,谢谢
position表的name(职位名称)主键列是genaral表的外键gposition
potision表有一个列rank(职位等级),标明了0-10的数字
现在我要查询genaral表所有将军信息,并以position表的rank排序我写的如下select *
from genaral
order by gposition=(select * from position order by rank)
是错的,请教高人解答,谢谢
select g.*
from genaral g,position p
where g.gposition=p.gposition
order by p.rank
在本题基础上我要去除职位相同的所有将军,然后输出其他将军的信息,该怎么写,我就是想知道distinct的用法,如果不能这么用的话,distinct应该在什么情况下用到
--测试数据
with genaral as(
select '张学好' name,to_date('2045-10-12','yyyy-mm-dd') dt,9600000 arm_num from dual union all
select '杨虎层',to_date('2055-10-25','yyyy-mm-dd'),894520 from dual union all
select 'James',to_date('2056-12-01','yyyy-mm-dd'),1000000 from dual),
position as(
select '张学好' name,'captain' position,5 rank from dual union all
select '杨虎层','commander',1 from dual union all
select 'James','commander',10 from dual)
select g.*,p.rank
from genaral g,position p
where g.name=p.name
order by p.rank;
--查询genaral表所有将军信息,并以position表的rank排序
NAME DT ARM_NUM RANK
------ ----------- ---------- ----------
杨虎层 2055-10-25 894520 1
张学好 2045-10-12 9600000 5
James 2056-12-1 1000000 10
--去除职位相同的所有将军,然后输出其他将军的信息
select g.*,p.pos,p.rank
from genaral g,
(select *
from position
where pos=(
select pos
from position
having count(name)=1
group by pos))p
where g.name=p.name;
--
NAME DT ARM_NUM POS RANK
------ ----------- ---------- --------- ----------
张学好 2045-10-12 9600000 captain 5
position as(
select '张学好' name,'captain' pos,5 rank from dual union all
select '杨虎层','commander',1 from dual union all
select 'James','commander',10 from dual)