假设我有一个名为表Cars
2列:CarName
,BrandName
现在我想执行这个查询:
select CarName from Cars order by BrandName
如您所见,我想返回一个列表,该列表按列排序,在查询的选择部分中不存在.
基本(未优化)执行的SQL命令的顺序为:from
,where
,group by
,having
,select
,order by
.
出现的问题是,BrandName不是执行select命令后剩下的部分.
我在书籍,谷歌和Stackoverflow上搜索过这个,但到目前为止我只发现了几个SO评论,例如"我知道数据库系统不允许它,但我不记得哪一个".
所以我的问题是:
1)SQL-92或SQL99 标准对此有何看法.
2)哪些数据库允许此查询,哪些不允许?
(背景:有几个学生问过这个,我想尽可能给他们最好的答案)
编辑:
- 成功测试Microsoft SQL Server 2012
您的查询是完全合法的语法,您可以按选择中不存在的列进行排序.
使用MySQL进行演示
使用SQL Server进行演示
使用Postgresql进行演示
使用SQLite进行演示
与Oracle合作演示
如果您需要有关合法排序的完整规范,则在SQL Standard 2003中有一长串关于应该和不应该包含的顺序的语句列表,(02-Foundation,第415页,第7.13节<查询表达式>,sub第28部分).这确认您的查询是合法语法.
我认为您的混淆可能来自于选择和/或按照组中不存在的列排序,或者使用distinct时不在select中的列排序.
两者都有相同的基本问题,而MySQL是我所知道的唯一一个允许的问题.
问题在于,当使用group by或distinct时,不需要任何未包含的列,因此它们在行之间有多个不同的值并不重要,因为它们永远不需要.想象一下这个简单的数据集:
ID | Column1 | Column2 | ----|---------+----------| 1 | A | X | 2 | A | Z | 3 | B | Y |
如果你写:
SELECT DISTINCT Column1 FROM T;
你会得到的
Column1 --------- A B
如果你再添加ORDER BY Column2
,你可以使用两个column2中的哪一个来订购A,X或Z?关于如何为column2选择值,这不是确定性的.
这同样适用于选择不在组中的列.为简化起见,只需想象上一个表的前两行:
ID | Column1 | Column2 | ----|---------+----------| 1 | A | X | 2 | A | Z |
在MySQL中你可以写
SELECT ID, Column1, Column2 FROM T GROUP BY Column1;
这实际上打破了SQL标准,但它适用于MySQL,但问题是它是非确定性的,结果是:
ID | Column1 | Column2 | ----|---------+----------| 1 | A | X |
没有或多或少不正确
ID | Column1 | Column2 | ----|---------+----------| 2 | A | Y |
所以你所说的是给我一行,每个不同的值Column1
,两个结果集都满足,所以你怎么知道你会得到哪一个?嗯,你没有,似乎是一个相当流行的误解,你可以添加和ORDER BY
子句来影响结果,所以例如以下查询:
SELECT ID, Column1, Column2 FROM T GROUP BY Column1 ORDER BY ID DESC;
确保您获得以下结果:
ID | Column1 | Column2 | ----|---------+----------| 2 | A | Y |
因为ORDER BY ID DESC
,但事实并非如此(如此处所示).
在MySQL的文件状态:
服务器可以自由选择每个组中的任何值,因此除非它们相同,否则所选的值是不确定的.此外,添加ORDER BY子句不会影响每个组中值的选择.
因此,即使您有一个订单,但在每个组选择了一行之后才会适用,并且这一行是不确定的.
SQL-Standard允许选择列表中的列不包含在GROUP BY中或聚合函数中,但是这些列必须在功能上依赖于GROUP BY中的列.从SQL-2003-Standard(5WD-02-Foundation-2003-09 - 第346页) - http://www.wiscorp.com/sql_2003_standard.zip
15)如果T是分组表,那么令G为T的分组列的集合.在<select list>中包含的每个<value expression>中,引用T列的每个列引用应引用某些列C,即功能上依赖于G或应包含在<set function specification>的聚合参数中,其聚合查询为QS.
例如,示例表中的ID是PRIMARY KEY,因此我们知道它在表中是唯一的,因此以下查询符合SQL标准并且将在MySQL中运行并且当前在许多DBMS中失败(在编写Postgresql时)是我所知道的最接近正确实施标准的DBMS - 例如:
SELECT ID, Column1, Column2 FROM T GROUP BY ID;
由于ID对于每一行都是唯一的,因此Column1
每个ID 只能有一个值,一个值Column2
对于每行返回的内容没有歧义.