现在有

shop表、goods表

要获取shop的前100条数据，

条件:根据goods表中的is_onshelf=1 and status=1 and is_delete=0的商品数量>0

关联条件

shop    shop_id

goods   shop_id

(1)

SELECT

id,

shop_id,

name

FROM

shop

WHERE

shop_id IN (

SELECT

shop_id

FROM

goods

WHERE

STATUS = 1

AND is_onshelf = 1

AND is_delete = 0

GROUP BY

shop_id

HAVING

count(*) > 0

)

LIMIT 1,

100

 

(2)

SELECT DISTINCT

shop.id,

shop.shop_id,

shop.name

FROM

shop

LEFT JOIN goods ON goods.shop_id = shop.shop_id

WHERE

(

SELECT

count(*)

FROM

goods

WHERE

goods. STATUS = 1

AND goods.is_onshelf = 1

AND is_delete = 0

AND shop.shop_id = goods.shop_id

) > 0

LIMIT 1,

100

 

精彩链接

评论可见，请评论后查看内容，谢谢！！！评论后请刷新页面。