oracle结果当条件,用子查询做条件后，查询结果不对

现象描述：

取数据库表中的一批数据，但是其中一条记录的一个字段值不是此条记录的真正值。

oracle版本：

ORACLE RDBMS Version: 11.1.0.6.0

sql语句：

a)select g1.team_id,  g1.player_id,  g1.effective_yellow_n, g1.stage

from game_total_player g1

where g1.team_id = '27'

and g1.cup_id = '20090000'

and g1.stage = (select max(g2.stage)

from game_total_player g2

where g2.cup_id = g1.cup_id

and g2.player_id = g1.player_id

and g2.flag = g1.flag)

result(在此只列出其中的一条，其它记录没有列出)：

team_id  player_id  effective_yellow_n  stage

27       162        0                   5

c)此条记录的真实值

select team_id,  player_id,  effective_yellow_n, stage

from game_total_player

where cup_id = '20090000'

and player_id = '162'

result:

team_id  player_id  effective_yellow_n  stage

27       162        2                   5

也就是说此记录的effective_yellow_n字段值为2，但是用a处的sql检索结果却为0.

此数据库表中只存在一条player_id为162的记录，cup_id、player_id、flag和stage为表game_total_player的主键，均不为空。

d)其它sql及结果

增加一个关联字段

select g1.team_id,  g1.player_id,  g1.effective_yellow_n, g1.stage

from game_total_player g1

where g1.team_id = '27'

and g1.cup_id = '20090000'

and g1.stage = (select max(g2.stage)

from game_total_player g2

where g2.cup_id = g1.cup_id

and g2.player_id = g1.player_id

and g2.flag = g1.flag

and g2.team_id = g1.team_id)

result(正确):

team_id  player_id  effective_yellow_n  stage

27       162        2                   5

增加一个条件进行限定

select g1.team_id,  g1.player_id,  g1.effective_yellow_n, g1.stage

from game_total_player g1

where player_id = '162'

and g1.team_id = '27'

and g1.cup_id = '20090000'

and g1.stage = (select max(g2.stage)

from game_total_player g2

where g2.cup_id = g1.cup_id

and g2.player_id = g1.player_id

and g2.flag = g1.flag)

result(正确):

team_id  player_id  effective_yellow_n  stage

27       162        2                   5

e)验证sql

用错误值进行检索

select team_id,  player_id,  effective_yellow_n, stage

from game_total_player

where team_id = '27'

and player_id = '162'

and effective_yellow_n = 0

and stage = 5

result：

no rows returned

用正确值检索

select team_id,  player_id,  effective_yellow_n, stage

from game_total_player

where team_id = '27'

and player_id = '162'

and effective_yellow_n = 2

and stage = 5

result(返回唯一的一条记录):

team_id  player_id  effective_yellow_n  stage

27       162        2                   5

问题：

我们知道oracle不推荐这种用法，但是到工程后期已经无法修改，而且通过增加关联字段也可以杜绝这个问题，

只是不明白错在哪个地方，烦请各位高人指点。

其它信息：

GAME_TOTAL_PLAYER表的主键是：CUP_ID,STAGE, PLAYER_ID,FLAG ，均不为空。除此之外没有其它索引和约束。