根据一个表的数据情况显示另一个表的数据

现在有

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

 

转载于:https://www.cnblogs.com/baby123/p/5591031.html