表设计没有问题 ORDER , ORDER_ITEM 然后在多的表ORDER_ITEM 中添加对`oi_id`的外键。不明白楼主认为下面SQL语句有什么问题?select * from (select * from `order` as oi limit 2) as oit left join order_item on o_id=oi_order_id select * from (SELECT * FROM `order` as oi limit 2) as oit left join order_item on o_id=oi_order_id and oi_name='Box'
select * from (select * from `order` as oi limit 2) as oit left join order_item on o_id=oi_order_id 这句没问题, 但这句:我想 有box的前2个单,但搜索不到的,box为空的都有 select * from (SELECT * FROM `order` as oi limit 2) as oit left join order_item on o_id=oi_order_id and oi_name='Box'
表设计没问题。 你要的结果用下面的sql语句试试。select * from (select oi_order_id from order_item where oi_name='Box' -- 条件 group by oi_order_id limit 2) as oit left join `order` on oit.oi_order_id = `order`.o_id left join order_item on oit.oi_order_id = order_item.oi_order_id
只要订单信息?select * from (select oi_order_id from order_item where oi_name='Box' -- 条件 group by oi_order_id limit 10) as oit left join `order` on oit.oi_order_id = `order`.o_id
想起来了,你这个还有另一个问题,为了测试简单,我这只left join一个表,如果我再left join了其它表,那这个sql又无效了吧。比如我现在的sql是这样:select * from ( select * from v2_order as eo order by o_id desc limit 0,50 ) as v2_order left join v2_order_detail on v2_order_detail.od_o_id = v2_order.o_id left join v2_products on v2_products.product_id = v2_order_detail.od_product_id left join v2_order_addition on v2_order_addition.oa_order_id = v2_order.o_id left join v2_store on v2_order.o_account = v2_store.store_name left join v2_product_inventory on v2_product_inventory.pi_pid=v2_products.product_id
原理是这样的 如果你要取的是订单信息。则不管你要连几张表,一律写在子查询中,条件也一律加上,最后加上group by o_id order by o_id limit N 例如: select v2_order.o_id from v2_order left join 表1 on XXXXXXXX left join 表2 on XXXXXXXX ...... where 条件1 and 条件2 and 条件3 ...... group by v2_order.o_id order by v2_order.o_id limit N这样取出来的就是符合条件的前 n 条订单ID。然后再去连其他需要的表就是了。
select * from (select o.* from `order` inner join order_item on o_id=oi_order_id and oi_name='Box' limit 2) as oit left join order_item on o_id=oi_order_id
select * from (select o.* from `order` inner join order_item on o_id=oi_order_id and oi_name='Box' limit 2) as oit left join order_item on o_id=oi_order_id 那这个还要group by吧
select * from (SELECT * FROM `order` as oi limit 2) as oit left join order_item on o_id=oi_order_id and oi_name='Box'
但这句:我想 有box的前2个单,但搜索不到的,box为空的都有
select * from (SELECT * FROM `order` as oi limit 2) as oit left join order_item on o_id=oi_order_id and oi_name='Box'
你要的结果用下面的sql语句试试。select * from (select oi_order_id from order_item
where oi_name='Box' -- 条件
group by oi_order_id
limit 2) as oit
left join `order` on oit.oi_order_id = `order`.o_id
left join order_item on oit.oi_order_id = order_item.oi_order_id
你的条件里要oi_name='Box'
是指出现订单商品中包含'box'的订单下的所有商品,还是仅出现订单商品是'box'的商品?上述SQL是出现包含'box'商品的订单及该订单的下所有商品。
只要订单信息?select * from (select oi_order_id from order_item
where oi_name='Box' -- 条件
group by oi_order_id
limit 10) as oit
left join `order` on oit.oi_order_id = `order`.o_id
如果你要取的是订单信息。则不管你要连几张表,一律写在子查询中,条件也一律加上,最后加上group by o_id order by o_id limit N
例如:
select v2_order.o_id from v2_order
left join 表1 on XXXXXXXX
left join 表2 on XXXXXXXX
......
where
条件1
and 条件2
and 条件3
......
group by v2_order.o_id
order by v2_order.o_id
limit N这样取出来的就是符合条件的前 n 条订单ID。然后再去连其他需要的表就是了。