mysql - 如何在 MySQL 5.5 中执行 IFNULL(count(*),0)

Question

我尝试执行 IFNULL(count(),0) 或 IF (count() > 0, count(*),0)，但它不起作用，我的行“计数器”显示“NULL”而不是 0 :/

这是我的查询:

SELECT IF(fc.counter > 0, fc.counter, 0) counter, b.*, fc.* FROM client_branche cb INNER JOIN branche b On b.id = cb.branche_id LEFT OUTER JOIN (   

    SELECT count(*) as counter, ctn_b.branche_id as b_id
    FROM `historique` h 
    INNER JOIN contenu_branche ctn_b ON ctn_b.contenu_id = h.contenu_id 
    INNER JOIN utilisateur u ON u.id = h.utilisateur_id 
    WHERE h.h_fini = 1 AND ( u.client_id = 1 OR u.client_id = 0 ) AND h.h_dateheure BETWEEN '2015-12-24' AND '2015-12-30'
    group by ctn_b.`branche_id`) 
fc ON fc.b_id = cb.branche_id WHERE cb.client_id = 1

所以我尝试这样做:

SELECT IF(fc.counter > 0, fc.counter, 0) counter, b.*, fc.* FROM client_branche cb INNER JOIN branche b On b.id = cb.branche_id LEFT OUTER JOIN (   

    SELECT IFNULL(count(*),0) as counter, ctn_b.branche_id as b_id
    FROM `historique` h 
    INNER JOIN contenu_branche ctn_b ON ctn_b.contenu_id = h.contenu_id 
    INNER JOIN utilisateur u ON u.id = h.utilisateur_id 
    WHERE h.h_fini = 1 AND ( u.client_id = 1 OR u.client_id = 0 ) AND h.h_dateheure BETWEEN '2015-12-24' AND '2015-12-30'
    group by ctn_b.`branche_id`) 
fc ON fc.b_id = cb.branche_id WHERE cb.client_id = 1

安恩我失败了。我希望有人能帮助我。预先非常感谢，很抱歉我的英语不好，这不是我的母语:)

Answer 1

问题可能是 select 中的多个列具有相同的名称 - counter。解决这个问题的最好方法是不在查询中使用*，而是列出你想要的列。也许更简单的方法是重命名该列:

SELECT (CASE WHEN fc.counter > 0 THEN fc.counter ELSE 0 END) as fc_counter,
       b.*, fc.*
FROM client_branche cb INNER JOIN
     branche b
     On b.id = cb.branche_id LEFT OUTER JOIN
     (SELECT count(*) as counter, ctn_b.branche_id as b_id
      FROM `historique` h INNER JOIN
            contenu_branche ctn_b
            ON ctn_b.contenu_id = h.contenu_id INNER JOIN
            utilisateur u
            ON u.id = h.utilisateur_id 
      WHERE h.h_fini = 1 AND
            u.client_id IN (0, 1) AND
            h.h_dateheure BETWEEN '2015-12-24' AND '2015-12-30'
      GROUP BY ctn_b.branche_id
     ) fc
     ON fc.b_id = cb.branche_id
WHERE cb.client_id = 1;

我还做了一些其他的小更改，例如使用 IN 代替 OR 和 CASE (ANSI 标准)代替 IF()。

注意:如果 counter 永远不会为负数，则最通俗的逻辑编写方式是 COALESCE(counter, 0) 而不是 CASE或IF()。