时间戳

首页 > 解决方案 > 从 SQL 查询中消除重复以避免从 LEFT/RIGHT JOIN 返回 NULL

sql - 从 SQL 查询中消除重复以避免从 LEFT/RIGHT JOIN 返回 NULL

问题描述

有这个 MS SQL 查询

SELECT
COUNT(*) AS Total,
CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.AmountGB, 0)) END AS AmountGB,
CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.TaxAmountGB, 0)) END AS TaxAmountGB,
CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.AmountUS, 0)) END AS AmountUS,
CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.TaxAmountUS, 0)) END AS TaxAmountUS,
CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.AmountAU, 0)) END AS AmountAU,
CASE WHEN COUNT(*) = 0 THEN 0 ELSE SUM(ISNULL(p.TaxAmountAU, 0)) END AS TaxAmountAU,
...
...
...
FROM Page AS p
RIGHT JOIN (SELECT PageId,... FROM Campaign 
WHERE CampaignId = @Campaign AND...) AS c ON p.PageId = c.PageId

当 JOIN 左侧没有匹配的行时，想要返回 0 而不是 NULL，这就是为什么

WHEN COUNT(*) = 0 THEN 0 ELSE...

无需定义标量函数，有没有办法去除重复

WHEN COUNT(*) = 0 THEN 0 ELSE...

或更简洁的方式返回0？

注意：页表中的值（例如 AmountGB）可以有空值，这就是 ISNULL(f.AmountGB, 0) 的原因。

注意：不允许更改表定义。

标签： sqljoin

解决方案

这会简单得多：

SELECT COUNT(*) AS Total,
       COALESCE(SUM(f.AmountGB), 0)) AS AmountGB,
       COALESCE(SUM(f.TaxAmountGB), 0)) AS TaxAmountGB,
       . . .

我还推荐LEFT JOIN：

SELECT COUNT(*) AS Total,
       COALESCE(SUM(f.AmountGB), 0)) AS AmountGB,
       COALESCE(SUM(f.TaxAmountGB), 0)) AS TaxAmountGB,
       . . .
FROM Campaign c LEFT JOIN
     PageTotals f
     ON f.PageId = p.PageId
WHERE CampaignId = @Campaign AND ...

推荐阅读