证券表SEC
SEC_ID, SECURITY_NAME,STATUS
1 AA 0
2 BB 0
3 CC 1行情表MKT_DATA
SEC_ID, CLOSE_PRICE,STATUS
1 10.01 0
3 9.8 1希望得到的是STATUS = 0 的数据:
SEC_ID, SECURITY_NAME,CLOSE_PRICE
1 AA 10.01
2 BB我写出的sql文是
1>select a.sec_id, a.security_name, b.close_price
2>from sec a
3>left outer join
4>(select sec_id, close_price from mkt_data where status = 0) b
5>on a.sec_id = b.sec_id
6>and a.status = 0
感觉第4行的sql文有点多,第4行的选择项目需要在第1行再写一遍,不知有没有其他优化的代码
SEC_ID, SECURITY_NAME,STATUS
1 AA 0
2 BB 0
3 CC 1行情表MKT_DATA
SEC_ID, CLOSE_PRICE,STATUS
1 10.01 0
3 9.8 1希望得到的是STATUS = 0 的数据:
SEC_ID, SECURITY_NAME,CLOSE_PRICE
1 AA 10.01
2 BB我写出的sql文是
1>select a.sec_id, a.security_name, b.close_price
2>from sec a
3>left outer join
4>(select sec_id, close_price from mkt_data where status = 0) b
5>on a.sec_id = b.sec_id
6>and a.status = 0
感觉第4行的sql文有点多,第4行的选择项目需要在第1行再写一遍,不知有没有其他优化的代码
from SEC a left jion MKT_DATA b on a.sec_id = b.sec_id
and a.status=0 and b.status =0
select a.SEC_ID,a.SECURITY_NAME,b.CLOSE_PRICE
from SEC a left jion MKT_DATA b on a.sec_id = b.sec_id
where a.status=0 and b.status =0
就只能出来
1 AA 10.01 这条
select a.sec_id, a.security_name, b.close_price
from sec a
left join mkt_data b
on a.sec_id = b.sec_id
and b.status = 0
where a.status=0
select a.sec_id,
a.security_name,
b.close_price from(
select sec_id, security_name from sec where status = 0
)a
left join(
select sec_id, close_price from mkt_data where status = 0
)b
on a.sec_id = b.sec_id
select a.sec_id, a.security_name, b.close_price
from sec a left outer join mkt_data b
on a.sec_id = b.sec_id
where a.status = 0
业务上蛮有道理,status = 1 的证券,其行情也status = 1但如果status = 0 的证券,其行情status = 1证券表SEC
SEC_ID, SECURITY_NAME,STATUS
1 AA 0
2 BB 0
3 CC 0行情表MKT_DATA
SEC_ID, CLOSE_PRICE,STATUS
1 10.01 0
3 9.8 1这样的话,根据要求,就不对了。结果应该是
1 AA 10.01
3 CC
2 BB 而不是
1 AA 10.01
3 CC 9.8
2 BB