Question

oracle单行子查询返回多行

select distinct * from ( select userinfo0_.USER_ID as USER1_34_, userinfo0_.create_time as create2_34_, userinfo0_.BOSS_EMAIL as BOSS3_34_,
userinfo0_.EMAIL_CHECKING as EMAIL4_34_, userinfo0_.mobile as mobile34_, userinfo0_.OPER as OPER34_, userinfo0_.REMARKS as REMARKS34_,
userinfo0_.user_city as user8_34_, userinfo0_.user_name as user9_34_, userinfo0_.WEIBO_NAME as WEIBO10_34_ ,
(select count(t.mail_address) from ad_click_detail t, user_info u
where t.mail_address =u.boss_email(+) group by t.mail_address ,u.boss_email) count

from user_info userinfo0_ where 1=1
and userinfo0_.BOSS_EMAIL in ( select u.boss_email from ad_click_detail t, user_info u
where t.mail_address =u.boss_email group by t.mail_address ,u.boss_email )
and
userinfo0_.create_time>=to_date('1999-07-16 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and userinfo0_.create_time<to_date('2014-07-13',
'yyyy-mm-dd hh24:mi:ss') ) order by count

仿照的是这个例子：
select 广告表.*, (select count(1) from 广告点击表 where 广告表.id = 广告点击表.广告ID) 点击次数
from 广告表
order by 点击次数 desc

Answer 1

(select count(t.mail_address) from ad_click_detail t, user_info u where t.mail_address =u.boss_email(+) group by t.mail_address ,u.boss_email) count
和
userinfo0_.BOSS_EMAIL in ( select u.boss_email from ad_click_detail t, user_info u where t.mail_address =u.boss_email group by t.mail_address ,u.boss_email )
将这里面的order by 语句去掉，子查询中好像不能够使用order by语句的，而且order by 在这毫无用处，完全是多余而又浪费资源的一种行为。去掉order by语句