在pl/sql developer上运行这样一条sql语句:
select t1.borrow_id as borrow_id,
t2.El_Name as resource_name,
t2.El_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_EleBorrowInfo t1,
intel.CS_ElectronicInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.El_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc -- 错误出现在这里
union
select t1.borrow_id as borrow_id,
t2.Film_Name as resource_name,
t2.Film_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_FilmBorrowInfo t1, intel.CS_FilmInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Film_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc
union
select t1.borrow_id as borrow_id,
t2.ReliefMap_Name as resource_name,
t2.ReliefMap_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_MapBorrowInfo t1,
intel.CS_ReliefMapInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.ReliefMap_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc
union
select t1.borrow_id as borrow_id,
t2.Book_Name as resource_name,
t2.Book_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_BookBorrowInfo t1, intel.CS_BookInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Book_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc
union
select t1.borrow_id as borrow_id,
t2.Maga_Name as resource_name,
t2.Maga_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_MagaBorrowInfo t1,
intel.CS_MagazineInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.Maga_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc;
出现错误: SQL command not properly ended
where t1.Borrow_ObjectID = t2.El_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc -- 错误出现在这里
于是我估计可能是因为
order by 必须在sql语句的最后面,但是我想对部分查询结果集进行排序,就是union合并这五个子结果查询记录集,我想分别对这五个子结果查询记录集排序。于是我把上面的改成(就是在每个查询加了一个括号):(select t1.borrow_id as borrow_id,
t2.El_Name as resource_name,
t2.El_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_EleBorrowInfo t1,
intel.CS_ElectronicInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.El_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc)
union
(select t1.borrow_id as borrow_id,
t2.Film_Name as resource_name,
t2.Film_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_FilmBorrowInfo t1, intel.CS_FilmInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Film_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc)
union
(select t1.borrow_id as borrow_id,
t2.ReliefMap_Name as resource_name,
t2.ReliefMap_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_MapBorrowInfo t1,
intel.CS_ReliefMapInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.ReliefMap_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc)
union
(select t1.borrow_id as borrow_id,
t2.Book_Name as resource_name,
t2.Book_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_BookBorrowInfo t1, intel.CS_BookInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Book_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc)
union
( select t1.borrow_id as borrow_id,
t2.Maga_Name as resource_name,
t2.Maga_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_MagaBorrowInfo t1,
intel.CS_MagazineInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.Maga_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc);结果又出现错误:missing right parenthesis (右括号不匹配)
错误出现在
(select t1.borrow_id as borrow_id,
t2.El_Name as resource_name,
t2.El_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_EleBorrowInfo t1,
intel.CS_ElectronicInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.El_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc) --错误出现在这里
select t1.borrow_id as borrow_id,
t2.El_Name as resource_name,
t2.El_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_EleBorrowInfo t1,
intel.CS_ElectronicInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.El_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc -- 错误出现在这里
union
select t1.borrow_id as borrow_id,
t2.Film_Name as resource_name,
t2.Film_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_FilmBorrowInfo t1, intel.CS_FilmInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Film_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc
union
select t1.borrow_id as borrow_id,
t2.ReliefMap_Name as resource_name,
t2.ReliefMap_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_MapBorrowInfo t1,
intel.CS_ReliefMapInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.ReliefMap_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc
union
select t1.borrow_id as borrow_id,
t2.Book_Name as resource_name,
t2.Book_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_BookBorrowInfo t1, intel.CS_BookInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Book_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc
union
select t1.borrow_id as borrow_id,
t2.Maga_Name as resource_name,
t2.Maga_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_MagaBorrowInfo t1,
intel.CS_MagazineInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.Maga_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc;
出现错误: SQL command not properly ended
where t1.Borrow_ObjectID = t2.El_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc -- 错误出现在这里
于是我估计可能是因为
order by 必须在sql语句的最后面,但是我想对部分查询结果集进行排序,就是union合并这五个子结果查询记录集,我想分别对这五个子结果查询记录集排序。于是我把上面的改成(就是在每个查询加了一个括号):(select t1.borrow_id as borrow_id,
t2.El_Name as resource_name,
t2.El_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_EleBorrowInfo t1,
intel.CS_ElectronicInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.El_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc)
union
(select t1.borrow_id as borrow_id,
t2.Film_Name as resource_name,
t2.Film_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_FilmBorrowInfo t1, intel.CS_FilmInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Film_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc)
union
(select t1.borrow_id as borrow_id,
t2.ReliefMap_Name as resource_name,
t2.ReliefMap_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_MapBorrowInfo t1,
intel.CS_ReliefMapInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.ReliefMap_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc)
union
(select t1.borrow_id as borrow_id,
t2.Book_Name as resource_name,
t2.Book_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_BookBorrowInfo t1, intel.CS_BookInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Book_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc)
union
( select t1.borrow_id as borrow_id,
t2.Maga_Name as resource_name,
t2.Maga_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_MagaBorrowInfo t1,
intel.CS_MagazineInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.Maga_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc);结果又出现错误:missing right parenthesis (右括号不匹配)
错误出现在
(select t1.borrow_id as borrow_id,
t2.El_Name as resource_name,
t2.El_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_EleBorrowInfo t1,
intel.CS_ElectronicInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.El_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by t1.Borrow_Date desc) --错误出现在这里
排序是针对整个UNION后获得的结果集的。
假如确实想按每个UNION子集排序。
建议在每个UNION中增加一个字段。
比如第一个SELECT中是1
第二个SELECT中是2。。
在最后使用order by按这个字段进行排序。
select t1.borrow_id as borrow_id,
t2.El_Name as resource_name,
t2.El_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_EleBorrowInfo t1,
intel.CS_ElectronicInfo t2,
cs_userinfo t3
where t1.Borrow_ObjectID = t2.El_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
union
select t1.borrow_id as borrow_id,
t2.Film_Name as resource_name,
t2.Film_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_FilmBorrowInfo t1, intel.CS_FilmInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Film_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
union
select t1.borrow_id as borrow_id,
t2.ReliefMap_Name as resource_name,
t2.ReliefMap_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_MapBorrowInfo t1, intel.CS_ReliefMapInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.ReliefMap_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
union
select t1.borrow_id as borrow_id,
t2.Book_Name as resource_name,
t2.Book_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_BookBorrowInfo t1, intel.CS_BookInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Book_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
union
select t1.borrow_id as borrow_id,
t2.Maga_Name as resource_name,
t2.Maga_LabelID as resource_labelid,
t3.user_name as user_name,
t3.user_organization as user_unit,
t1.Borrow_Date as resource_date,
t1.Borrow_ID as objid
from INTEL.CS_MagaBorrowInfo t1, intel.CS_MagazineInfo t2, cs_userinfo t3
where t1.Borrow_ObjectID = t2.Maga_ID
and t1.Borrow_UserID = t3.user_id
and t1.Borrow_Date > to_date('2007-9-4', 'yyyy-mm-dd')
and t1.Borrow_Date < to_date('2007-9-5', 'yyyy-mm-dd')
order by 6 desc;
“部分查询结果集进行排序,就是union合并这五个子结果查询记录集,我想分别对这五个子结果查询记录集排序”你上面的语句实际上是对五个子结果集合并后再排序。
你自己描述问题不清楚,实在不能怪别人。