Mysql如果count为空，则使其为零 - mysql if count is null then make it zero

mysql if count is null then make it zero

本文关键字：如果 count 为空 Mysql | 更新日期: 2023-09-27

如何将字段most_popular设置为0，如果它的计数为null，在select语句中使用select语句

我试过IFNULL(SELECT COUNT(*)…)，0)作为最受欢迎的，但它不起作用，我试过了Coalesce (select count (*) ....

    $stmt = $db->prepare("SELECT *,         
    i.medium_image, i.width, i.height, 
    (SELECT COUNT(*) FROM order_details od WHERE od.product_id = p.product_id) as most_popular
    FROM products p 
        INNER JOIN product_images i on i.product_id = p.product_id      
    WHERE p.department_id=:department_id AND p.is_active=1
    $orderby        
    LIMIT :limit OFFSET :offset");

试试这个，

SELECT  *, 
        i.medium_image, 
        i.width, 
        i.height, 
        COALESCE(s.totalCount, 0) most_popular 
FROM    products p 
        INNER JOIN product_images i 
            ON i.product_id = p.product_id 
        LEFT JOIN
        (
            SELECT  product_id, Count(*) totalCount
            FROM    order_details
            GROUP   BY product_id 
        ) s ON s.product_id = p.product_id
WHERE  p.department_id = :department_id 
       AND p.is_active = 1 
$orderby 
LIMIT :limit OFFSET :offset

或者how about this， (你当前的查询)

COALESCE((SELECT COUNT(*) 
          FROM order_details od 
          WHERE od.product_id = p.product_id), 0) as most_popular