更改预言机查询的输出顺序

Question

我想更改预言机查询的输出顺序。查询：

select name,address from users where name=any(select u.name from users u,bookingdetails b where u.user_id=b.user_id and b.name != 'HDFC') and address=any(select u.address from users u,bookingdetails b where u.user_id=b.user_id and b.name != 'HDFC');

它的输出：

NAME
--------------------
ADDRESS
--------------------------------------------------------------------------------
Johan
Delhi
John
Bangalore
Krena
Mumbai

Target output--:

NAME
--------------------
ADDRESS
--------------------------------------------------------------------------------
John
Bangalore
Krena
Mumbai
Johan
Delhi

我怎样才能达到这个结果。有什么帮助/建议吗？

Answer 1

这应该可以完成这项工作：

select name, address from users 
where name=any(select u.name from users u, bookingdetails b 
where u.user_id=b.user_id and b.name != 'HDFC') 
and address=any(select u.address from users u, bookingdetails b 
where u.user_id=b.user_id and b.name != 'HDFC')
Order By Decode(name, 'John', 1, 'Krena', 2, 3);

就您在评论中提到的优化查询而言，我相信此查询将更简洁地达到相同的结果：

select u.name, u.address from users u
where u.user_id in (select b.user_id from bookingdetails b 
where b.name != 'HDFC')
Order By Decode(u.name, 'John', 1, 'Krena', 2, 3);