Question

有时，您编写一个分组查询，其中每个组是一行，如having count(*) = 1。这意味着像min，max，sum等通常的聚合函数有点毫无意义：最小值等于最大值，等于总和，等于平均值。因为只有一个值可以聚合。

我通常会随意选择min。如果我们以熟悉的表格为例，将一本书映射到其作者，我可能只想查询只有一个作者的书籍：

-- For books that have a single author, pull back that author's id.
select book_id,
       min(author_id) as author_id
       -- I could equally well use max(author_id) or even sum(author_id)...
from book_authors
group by book_id
having count(*) = 1

有些列类型 (如 Microsoft SQL Server 中的bit) 不支持min聚合函数,因此您必须执行convert(bit, min(convert(int, mycol)))等变通方法。

所以，我希望答案是否定的，但是有没有更好的方法来指定我的意图？

select book_id,
       there_must_be_one_value_so_just_return_it(author_id) as author_id
from book_author
group by book_id
having count(*) = 1

显然，如果您不需要count(*)=1，那么您将不再保证单个值，并且无法使用特殊的聚合函数。编译 SQL 时可能会捕获该错误。

所需的结果将等同于上面的min查询。

我正在使用 Microsoft SQL Server（2016），但由于这是一个相当“蓝天”的问题，我也会对其他 SQL 方言的回复感兴趣。

2

Answer 1

你可以，相反，使用一个窗口COUNT，然后根据过滤器：

WITH CTE AS(
    SELECT ba.book_id,
           ba.author_id,
           COUNT(ba.book_id) OVER (PARTITION BY ba.book_id) AS Authors
    FROM dbo.book_authors ba)
SELECT c.book_id,
       c.author_id
FROM CTE c
WHERE c.Authors = 1;

另一种方法是使用相关的子查询：

SELECT ba.book_id,
       ba.author_id
FROM dbo.book_authors ba
WHERE EXISTS (SELECT 1
              FROM dbo.book_authors e
              WHERE e.book_id = ba.book_id
              GROUP BY e.book_id
              HING COUNT(*) = 1);

我还没有测试性能上的任何一个体面的数据量，但是，我会希望，对于一个相关的子查询与一个良好的索引表，你应该看到更好的性能。