编写SQL几年后,我发现经常不得不将我感兴趣的列放置在SELECT
上,然后在GROUP BY
中再次指定它们。我不禁想到,为什么我们必须这样做?
用户必须具体说明哪个列要组成的原因是什么?如果SELECT
中有汇总函数,则不能让SQL Engine假定由其余的非聚合列组组?
当您在SELECT
中拥有大的CASE WHEN
时,这将特别有用,更简洁。
,因为它们可能并不总是完全匹配。
例如,如果我想找出每个类别的书籍数量最多,我可以做:
select max(cnt)
from (
select count(*) as cnt
from books
group by category
) t;
在某些DBS(例如Oracle)中,您甚至可以执行此操作:
select max(count(*))
from books
group by category;
我真的不需要指定类别列,因为我不需要。
一些数据库(例如Postgres)支持该组中的别名。
我碰巧同意你的看法。如果有人想要一个更深奥的group by
- 说,遗漏了列 - 他们可以使用子查询。
如果我不得不猜测,SQL的作者不想在汇总功能中充满力量。您的建议将意味着select
中的功能正在确定结果集中的行的定义。通常,select
仅确定列。也就是说,查询在语法上失败是一回事,因为在没有group by
的情况下包括聚合。select
中的函数是另一件事,可以更改输出的行。
您可以将窗口函数与select distinct
一起使用。尽管我不建议语法,但您可以做:
select distinct x, count(*) over (partition by x)
from t;
好吧,这消除了group by
,但是您仍然必须重复每个窗口函数中的分组标准。
您必须想象您正在使用两个版本的表格。例如:
SELECT …
FROM table
GROUP BY …;
首先,请记住,在FROM
ANBD GROUP BY
子句之后,对SELECT
进行了评估。这意味着您可以选择的内容受这些条款的结果限制。
问题是想象GROUP BY
生成了一个新的 virtual 表。此虚拟表仅具有以下内容:
- 分组的列
- 摘要(汇总)所有列的
- 别无其他
每个不同的组都会有一行摘要。
如果您想要SELECT
子句中的特定列,则必须是组列或摘要,因为您只能从可用的内容中进行SELECT
。
即使没有GROUP BY
子句,也有一个隐式GROUP BY ()
,它导致一行摘要。有些DBMS(并非全部)甚至允许您添加它,尽管它不会改变任何内容。
请注意,GROUP BY
摘要中的行数受您要分组的列数的影响。通常,行的数量将是(不同的group1)*(不同的group2)等。这意味着您肯定不想通过超过您真正需要的分组。
异常
假设您有这样的语句:
SELECT state, name, count(*)
FROM customers
GROUP BY state;
当然,这将失败。选择一个状态有多个值的name
有什么意思?
但是,在传统模式下MySQL将让您这样做:它将选择一个的名称。但是,不能保证哪一个,所以它具有可疑的价值。
假设您想按月进行分组。您可能希望显示月份的名称,但按月编号进行排序。在这里,您需要两者进行分组。使用伪日期函数:
SELECT monthname, count(*)
FROM data
GROUP BY monthname, monthnumber
ORDER BY monthnumber;
这无非是解决方法。它利用了一个事实,每个月的名称恰好有一个月的编号,没有真正的进一步分组。就在GROUP BY
虚拟表中获得两个值。