表1: user_info 用户信息表
主键:user_id 用户ID
user_name 用户姓名
表2: bill_info 订单信息表
主键:bill_no 订单号
user_id 用户ID
state_code 订单状态代码
表3: sate_info 订单状态信息表
sate_code 订单状态代码
state_name 订单状态名称
表关系:user_info.user_id=bill_info.user_id
bill_info.state_code=state_info.state_code
现在需要查一个用户ID 对应的 “订单号”,“订单状态名称”,“用户姓名”
------解决方案--------------------
select B.bill_no 订单号,C.state_name 订单状态名称,A.user_name 用户姓名 from user_info as A
inner join bill_info as B on A.user_id = B.user_id
inner join sate_info as C on B.state_code= C.state_code
where A.user_id = 用户ID
------解决方案--------------------
select user_name, bill_no, state_name
from user_info as u, bill_info as b, state_info as s
where u.user_id = b.user_id
and b.state_code = s.state_code
and u.user_id = '1'