在oracle中使用连接查询
select * from A a where a.id=
(
select b.id from B b,C c where b.id = c.id and c.name='scott'
)
这样是可以查出结果, 但是我需要按照C表中的一个字段对结果集进行排序,
不知道这个order by应该如何写, 请高手指教
select * from A a where a.id=
(
select b.id from B b,C c where b.id = c.id and c.name='scott'
)
这样是可以查出结果, 但是我需要按照C表中的一个字段对结果集进行排序,
不知道这个order by应该如何写, 请高手指教
解决方案 »
- 大神们,job该怎么停啊
- 这个错误什么意思:数据库当前处于 OPEN 状态。但是由于存在以上错误, EM 无法连接到此数据库
- 求助!存储过程中没有参数时怎么写?
- pl/sql中如何打印?
- 请问哪里有关于Oracle用ODBC开发的资料下载?谢谢!急!
- 关于oracle中触发器的疑惑
- sql server 2k个人版在win2k pro上装不上,干脆转oracle玩,问问哪个版能在win2k pro上用.
- 请进帮忙
- 恢复数据库的问题,求教
- 【请教各位大虾】这么难的查询怎么做?
- 各位大侠帮我解释一下,下面的@colm2 还有4和20 是什么意思? 谢谢!
- oracle 9.0 查找用户正在执行和刚刚执行的SQL语句
A.*
FROM A,
B,
C
WHERE B.ID = C.ID
AND A.ID = B.ID
AND C.NAME = 'SCOTT'
ORDER BY C.ID;
按照一楼哥们的基础,将a中的字段分别select出来并重命名,然后再在外面套个大的select,不知是否可行
select
ancientboo0_.ANCIENTBOOK_ID as ANCIENTB1_0_,
ancientboo0_.USER_ID as USER3_0_,
ancientboo0_.UNIT_ID as UNIT4_0_,
ancientboo0_.F_RANK_ID as F5_0_,
ancientboo0_.S_RANK_ID as S6_0_,
ancientboo0_.T_RANK_ID as T7_0_,
ancientboo0_.RANK_ID as RANK8_0_,
ancientboo0_.CHECKNUMBER as CHECKNUM9_0_,
ancientboo0_.FORSORT as FORSORT0_,
ancientboo0_.ANCIENTSOURCE as ANCIENT11_0_,
ancientboo0_.INDEXNUMBER as INDEXNU12_0_,
ancientboo0_.ORIGINAL as ORIGINAL0_,
ancientboo0_.SOURCE as SOURCE0_,
ancientboo0_.DEVOLUTIONTYPE as DEVOLUT15_0_,
ancientboo0_.OTHERAPPENDIX as OTHERAP16_0_,
ancientboo0_.PRO1 as PRO17_0_,
ancientboo0_.PRO2 as PRO18_0_,
ancientboo0_.STATUS as STATUS0_,
ancientboo0_.HASCHILD as HASCHILD0_,
ancientboo0_.NEEDEXPERT as NEEDEXPERT0_,
ancientboo0_.TYPE as TYPE0_,
ancientboo0_.STATUS1 as STATUS23_0_,
ancientboo0_.REGISTERTIME as REGISTE24_0_,
ancientboo0_.CHILDTYPE as CHILDTYPE0_
from
SCOTT.ANCIENTBOOK ancientboo0_
where
ancientboo0_.ANCIENTBOOK_ID in (
select
ancientboo1_.ANCIENTBOOK_ID
from
SCOTT.ANCIENTBOOK ancientboo1_,
SCOTT.COUNTERMARK countermar2_,
SCOTT.BLOCK nvgroup3_,
SCOTT.BOOKNAME bookname4_,
SCOTT.BOOKAUTHOR bookauthor5_,
SCOTT.T_VERSION version6_,
SCOTT.USERUNIT unit7_
where
1=1
and ancientboo1_.ANCIENTBOOK_ID=countermar2_.ANCIENTBOOK_ID
and countermar2_.COUNTERMARK_ID=nvgroup3_.COUNTERMARK_ID
and nvgroup3_.BLOCK_ID=bookauthor5_.BLOCK_ID
and nvgroup3_.BLOCK_ID=bookname4_.BLOCK_ID
and countermar2_.COUNTERMARK_ID=version6_.COUNTERMARK_ID
and unit7_.UNIT_ID=ancientboo1_.UNIT_ID
and (
checkNumber='8'
or checkNumber='88'
)
order by bookname4_.bookname -- 当我加上 order by就出错 提示缺少右括号 --
)
不加的话没有问题,但是需求要求按照bookname字段来排序~!
A.*
FROM A,
B,
C
WHERE B.ID = C.ID
AND A.ID = B.ID
AND C.NAME = 'SCOTT'
ORDER BY C.ID;