现在有

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,    nameFROM    shopWHERE    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.nameFROM    shopLEFT JOIN goods ON goods.shop_id = shop.shop_idWHERE    (        SELECT            count(*)        FROM            goods        WHERE            goods. STATUS = 1        AND goods.is_onshelf = 1        AND is_delete = 0        AND shop.shop_id = goods.shop_id    ) > 0LIMIT 1,100