Union，它从第一个表中排除值

Question

我试图解决的原始问题是，我需要显示来自特定"联接"表的所有行。然而，这些有时是空白的，没有总计，通常不会显示(想想类别和每个类别的计数)。

所以我想做的是联合到一个"0值"数据集来显示所有类别。然而，当我做并集时，它显示了一个0值的行，以及正常的数据。下面是一个例子……

SELECT category_name, COUNT(files_number)
FROM files
LEFT JOIN categories ON categories.category_id = files.category_id
UNION 
SELECT category_name, 0
FROM categories

这将给我一个类似于下面的结果集:

category_name | value
----------------------
open file     | 0
open file     | 23
closed file   | 0

是否有办法删除重复的零值条目?请不要在实际查询中也有一个复杂的WHERE子句，因此最好避免重复。

Answer 1

我不明白你为什么要做左连接和联合。

你可以做下面的操作来删除重复项，包装你的查询并按

分组

;with cte
as
(
SELECT category_name, COUNT(files_number)
FROM files
LEFT JOIN categories ON categories.category_id = files.category_id
UNION 
SELECT category_name, 0
FROM categories
)
select categoryname,sum(aggcol)
from cte
group by 
category

Answer 2

一种方法是从类别表中选择所有类别，并将LEFT JOIN连接到文件计数(按category_id分组)。

SELECT c.category_name, ISNULL(fc.FileCount, 0) AS FileCount
FROM categories c
    LEFT JOIN (
        SELECT category_id, COUNT(files_number) AS FileCount
        FROM files
        GROUP BY category_id
    ) fc ON c.category_id = fc.category_id

编辑如果想要反转查询，可以这样做，使用RIGHT OUTER JOIN—这样就会返回categories表中的每个类别，而不管它是否有任何文件:

SELECT c.category_name, COUNT(f.category_id) AS FileCount
FROM files f
    RIGHT JOIN categories c ON c.category_id = f.category_id
GROUP BY c.name