证券表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行再写一遍，不知有没有其他优化的代码
------解决方案--------------------
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
------解决方案--------------------
不觉得多余啊……
- SQL code
select a.sec_id,a.security_name,b.close_price from(select sec_id, security_name from sec where status = 0)aleft join(select sec_id, close_price from mkt_data where status = 0)bon a.sec_id = b.sec_id