精通oracle的高手来看呀！！
select msg.id msgId,msg.test_result_id resultId,test.test_name testName,msg.message_times times,msg.receiver_id postilorId,msg.sender_id userId,postilor.real_name postilorName,msg.createdate createDate,msg.status status from (((select * from newclass.te_message union (select m1_.id,m1_.body,m1_.title,m1_.test_result_id,m1_.msg_type_id,m1_.sender_id,m1_.receiver_id,0,m1_.status,m1_.createby,m1_.createdate,m1_.lastupdateby,m1_.lastupdatedate from newclass.te_message m1_ where m1_.test_result_id not in (select distinct m0_.test_result_id from newclass.te_message m0_ where m0_.receiver_id=1173587))) msg inner join newclass.te_testresult result on msg.test_result_id=result.id) inner join newclass.te_test test on result.testid=test.id ) inner join newclass.te_test_postilor postilor on msg.receiver_id=postilor.id;
查不出记录，但是取中间的一节却没有问题，
select * from newclass.te_message where receiver_id=1173587 union (select m1_.id,m1_.body,m1_.title,m1_.test_result_id,m1_.msg_type_id,m1_.sender_id,m1_.receiver_id,0,m1_.status,m1_.createby,m1_.createdate,m1_.lastupdateby,m1_.lastupdatedate from newclass.te_message m1_ where m1_.test_result_id not in (select distinct m0_.test_result_id from newclass.te_message m0_ where m0_.receiver_id=1173587));
数据方面没有错误。
问题点数：20、回复次数：3Top
1 楼cenlmmx（学海无涯苦作舟）回复于 2006-03-04 19:13:41 得分 0
太乱了Top
2 楼dj0628（Reed）回复于 2006-03-06 11:09:25 得分 0
cenlmmx(学海无涯苦作舟) 真的很热心阿，呵呵Top
3 楼boydgmx（授人以鱼不如授人以渔(baidu&google)）回复于 2006-03-06 13:57:00 得分 0
我把你的语句格式调整了一下：
select msg.id msgId,
msg.test_result_id resultId,
test.test_name testName,
msg.message_times times,
msg.receiver_id postilorId,
msg.sender_id userId,
postilor.real_name postilorName,
msg.createdate createDate,
msg.status status from (
select * from newclass.te_message union
(select
m1_.id,
m1_.body,
m1_.title,
m1_.test_result_id,
m1_.msg_type_id,
m1_.sender_id,
m1_.receiver_id,
0,
m1_.status,
m1_.createby,
m1_.createdate,
m1_.lastupdateby,
m1_.lastupdatedate
from newclass.te_message m1_
where m1_.test_result_id not in (
select distinct m0_.test_result_id
from newclass.te_message m0_
where m0_.receiver_id=1173587)
)
) msg
inner join newclass.te_testresult result on (msg.test_result_id=result.id)
inner join newclass.te_test test on (result.testid=test.id)
inner join newclass.te_test_postilor postilor on (msg.receiver_id=postilor.id);
自己看看吧
我觉得很可能是这几个 inner join 导致结果集为空
你可以先去掉所有的inner join，查询看看
如果有结果，就加上一个join
依次尝试三个join
应该就能找到问题所在了
祝你好运！Top
