根据最大订单从2个不同的表中选择数据

Baig 发表于 Dev

拜格

我需要从2个不同的表（产品和订单）中选择数据，订单表将productid作为外键

我需要的

我需要一个查询，该查询显示产品表中的某些列，具体取决于订单表中的最大订单数

我的努力

我尝试了以下查询

select sc_products.id, sc_products.name from sc_products, sc_orders where sc_orders.product = sc_products.id

问题

此查询只是通过复制产品表中的ID将数据返回给我，如下所示：

Full Texts          id  name
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt
    Edit    Delete  10  Black-White Cats
    Edit    Delete  10  Black-White Cats
    Edit    Delete  4   Aquarius Rhinestone T Shirt
    Edit    Delete  4   Aquarius Rhinestone T Shirt
    Edit    Delete  249     Volleyball Mom Blue Rhinestone T Shirt
    Edit    Delete  249     Volleyball Mom Blue Rhinestone T Shirt

以上结果显示在查询之后，我只需要显示多次排序的记录，就需要 count

我需要的更多

我需要如下结果

                            id  name                                        count
        Edit    Delete  9   3 Gold-Silver Elephants Rhinestone T Shirt   10
        Edit    Delete  10  product 2                                    15

萨哈什·沙（Saharsh Shah）

尝试这个：

SELECT p.id, p.name, COUNT(o.id) orderCnt
FROM sc_products p 
INNER JOIN sc_orders o ON o.product = p.id
GROUP BY p.id HAVING COUNT(o.id) > 1
ORDER BY orderCnt DESC

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。