select t.*,c.hlr_name,d.policy_des,d.price --,f.output_time as send_time
from simcardinfo t
inner join simcard_hlr C on t.hlr_code=c.hlr_code
left outer join policyinfo D on t.policy_code=d.policy_code ----------------------------------------------------
inner join card_output_summary F on
f.output_billno=(select max(g.output_billno) from card_output_detail G
where t.cardno=g.cardno and t.stock_type='D') inner join card_send_summary H on
H.Send_Billno=(select max(I.send_billno) from card_send_detail I
where t.cardno=i.cardno and t.stock_type='E')
----------------------------------------------------where (t.stock_owner='1421' or t.stock_owner in(
select a.worker_id from customer_info A where a.department_id='1421' ))
and (t.card_status<>'04' and t.card_status<>'05' and t.card_status<>'06')
order by t.cardno desc
需要判断的地方就在框起来那部分,
因为要取一个相关的时间,但是这个时间要根据sotck_type来获取,
也就是说,要做到如果stock_type=E的时候,要从card_send_summary里面得到相关时间,
stock_type=D的时候,要从card_output_summary里面取得相关时间,
这样的话,应该怎么来写?这样写不对的,一条数据也查不出来,
谢了.本来SQL就菜,这下更弄不好了.:(PS:只能写SQL语句,不能用存储过程来搞的.额.
from simcardinfo t
inner join simcard_hlr C on t.hlr_code=c.hlr_code
left outer join policyinfo D on t.policy_code=d.policy_code ----------------------------------------------------
inner join card_output_summary F on
f.output_billno=(select max(g.output_billno) from card_output_detail G
where t.cardno=g.cardno and t.stock_type='D') inner join card_send_summary H on
H.Send_Billno=(select max(I.send_billno) from card_send_detail I
where t.cardno=i.cardno and t.stock_type='E')
----------------------------------------------------where (t.stock_owner='1421' or t.stock_owner in(
select a.worker_id from customer_info A where a.department_id='1421' ))
and (t.card_status<>'04' and t.card_status<>'05' and t.card_status<>'06')
order by t.cardno desc
需要判断的地方就在框起来那部分,
因为要取一个相关的时间,但是这个时间要根据sotck_type来获取,
也就是说,要做到如果stock_type=E的时候,要从card_send_summary里面得到相关时间,
stock_type=D的时候,要从card_output_summary里面取得相关时间,
这样的话,应该怎么来写?这样写不对的,一条数据也查不出来,
谢了.本来SQL就菜,这下更弄不好了.:(PS:只能写SQL语句,不能用存储过程来搞的.额.
if ....
select ...
else
select ...
或是用存储过程来做.但你又不让使用.这下可麻烦了.
结果要4条SQL语句,
额.帮忙,帮忙..