如果没有行匹配，该计数如何返回0？

Question

如果没有匹配，我需要以下查询来返回0：

CREATE TABLE #Operations (
    Discriminator varchar(40)
);

INSERT INTO #Operations
VALUES
    ('HistoricoCarga'),
    ('HistoricoDescarga')
;

SELECT
    o.Discriminator,
    CASE 
        WHEN COUNT(*) IS NULL THEN 0
        ELSE COUNT(*)
    END AS Total
FROM 
    HistoricosOperacion ho
        RIGHT JOIN #Operations o
            ON ho.Discriminator = o.Discriminator
WHERE
    ho.FechaEnvioIntegracionUTC IS NULL
    AND DATEDIFF(MINUTE, ho.FechaUTC, GETUTCDATE()) > 10
GROUP BY o.Discriminator;

我尝试过将count(*)更改为count(ho.Discriminator)或将temp表更改为：

CREATE TABLE #Operations (
    Discriminator varchar(40),
    dummy int
);

INSERT INTO #Operations
VALUES
    ('HistoricoCarga', 0),
    ...

我需要输出如下：

HistoricoCarga         0
HistoricoDescarga      0

Answer 1

不要计数(*)，从联接的稀疏侧数联接列

CREATE TABLE #Operations (
    Discriminator varchar(40)
);

INSERT INTO #Operations
VALUES
    ('HistoricoCarga'),
    ('HistoricoDescarga')
;

SELECT
    o.Discriminator,
    COUNT(ho.Discriminator) AS Total
FROM 
    
    #Operations o
    LEFT JOIN
    HistoricosOperacion ho
    ON ho.Discriminator = o.Discriminator AND DATEDIFF(MINUTE, ho.FechaUTC, GETUTCDATE()) > 10

GROUP BY o.Discriminator;

将限制移出WHERE并进入ON意味着只有两件事都是真的行才会加入。你还可以考虑：

#Operations o
LEFT JOIN
(SELECT * FROM HistoricosOperacion WHERE DATEDIFF(MINUTE, ho.FechaUTC, GETUTCDATE()) > 10) ho
ON ho.Discriminator = o.Discriminator 

如果这对你来说更有意义