SQL 魔法学 第2部分 - Select，超越基础

在我们开始之前，如果您错过了第1部分（关于 JOIN），您可以在此处找到它。

• 下载 SQL_Wizardry_02.zip - 1,010 B

引言

好吧，我说过这个星期天我会写一篇关于 CTE 的文章，我仍然打算这样做，但是回答论坛中的一些问题让我意识到，我的系列文章中仍然需要一篇关于 SELECT 语句的文章。因此，这篇文章将涵盖 SELECT 的大部分基础知识。像窗口函数和使用 XML 等内容，将在后面单独介绍。

 

要充分利用 SQL 文章，您确实需要在 SQL Server 中进行操作，在那里您可以运行 SQL、更改它并查看结果如何变化。为了方便起见，我希望您下载并安装 AdventureWorks 数据库，因为它为您提供了一组很好的示例数据供您使用。您可以在此处下载它。去吧，我等着。

简单 SELECT

好的，当您打开我提供的 SQL 文件时，您会想要一次取消注释一个 SELECT 语句并运行它，以逐一学习我的示例。第一个很简单

select * from Sales.Currency

这只会返回整个表。尽管 SELECT * 会返回所有行，但您应该避免使用它。这是因为您的代码没有指定您想要哪些列。如果有人针对您的 SQL 编写代码，通过序号而不是名称处理列，那么新列可能会破坏该代码。如果删除了某个列，您的 SQL 仍然会运行，但下游代码将会中断。一个好的原则是，如果出现问题，让它尽可能地接近原因处中断，这样您就可以轻松找出问题所在。

 

请注意，虽然货币代码可能是按字母顺序排列的（并且很可能它们是按此顺序插入的），但顺序并不能保证。事实上，SQL 是一种基于集合的语言。集合没有保证的顺序。如果您关心数据的顺序，您应该始终使用 ORDER BY 语句。

带 ORDER BY 的 SELECT

select CurrencyCode, Name, ModifiedDate from Sales.Currency order by name

这将返回所有货币，按名称排序（如果您打算在列表中显示它们，这可能是您想要的）。请注意，如果第一个排序的列具有重复值，您可以指定额外的列进行排序，用逗号分隔。

TOP - 选择 X 行

您还可以选择一定数量的行，像这样

select top 10 CurrencyCode, Name, ModifiedDate from Sales.Currency order by name

您也可以按降序排序。这里有一个我学到的技巧，可以从数据库中获取一页数据。

declare @page int = 5
declare @pageSize int = 10
 
select CurrencyCode, Name, ModifiedDate
from
(
select top (@pageSize) CurrencyCode, Name, ModifiedDate from 
(
  select top (@pageSize * (@page + 1)) CurrencyCode, Name, ModifiedDate from Sales.Currency order by name
) p
order by name desc
) t order by name

这会按顺序拉出您想要的页面结尾的数据，然后从列表底部计算页面大小，最后再次翻转顺序。

选择随机行

如果您想快速抽样数据，有一个关键字“TABLESAMPLE”。它不适用于 Sales.Currency 表，我不知道为什么，尽管我知道它不适用的规则

 

TABLESAMPLE 不能应用于

• 派生表，来自链接服务器的表
• 从表值函数派生的表
• 行集函数
• OPENXML

它也不能在视图或函数中使用。

它的工作原理如下

SELECT FirstName, LastName
FROM Person.Person 
TABLESAMPLE (10 PERCENT) ;

这不起作用

SELECT FirstName, LastName
FROM Person.Person 
TABLESAMPLE (50 ROWS) ;

但这有效

SELECT FirstName, LastName
FROM Person.Person 
TABLESAMPLE (500 ROWS) ;

所以我认为当你要求太小的样本集时它会混淆。它不是真正的随机，但你可以让它重复相同的集合，如下所示

SELECT FirstName, LastName
FROM Person.Person 
TABLESAMPLE (10 PERCENT)
REPEATABLE (205)

Repeatable 作为一个静态的“随机”种子，可以是任何值。

 

你会注意到数据倾向于以块的形式选择。如果你想要真正的随机数据，可以这样做

SELECT TOP 500 FirstName, LastName
FROM Person.Person order by newid() 

NewId() 返回一个 `UNIQUEIDENTIFIER`，这是 T-SQL 中的 GUID。这会比较慢，但根据您的需求，会得到更好的结果。

另一个选项是这样的

 

SELECT TOP 500 FirstName, LastName
FROM Person.Person order by (SELECT NULL) 

NULL 是一个特殊值，NULL 不等于 NULL，所以如果您按 NULL 排序，值的排序顺序是随机的，因为这些值不相等且未知。

WHERE 和 DISTINCT

当然，您通常不希望得到所有数据，您希望得到一个经过筛选的视图。这就是 WHERE 语句的作用。

select firstname, lastname from person.person where persontype = 'EM'

将返回所有员工。如果您想知道所有人员类型是什么，您可以这样做

select distinct persontype from person.person

注意：Distinct 在您知道存在一系列值并想要特定值时很有用。当您将表连接在一起时，有时会发现结果重复。这通常意味着您连接的某个表中，您指定的键中存在重复值，从而使其他表的结果加倍。或者仅仅意味着您的 SQL 没有经过深思熟虑。不要使用 DISTINCT 来隐藏您的 bug。如果您获得重复的行，请找出原因。

GROUP BY

现在我们来看我真正想写点东西的结构，因为我看到许多论坛问题都基于人们不了解它。我怀疑人们想用 SQL 做的第一件事，就是超越“给定数据”，而是获取某物的总和，或者其他计算。如果你这样做

select FirstName, LastName, LineTotal from sales.salesorderdetail sod 
inner join sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
inner join sales.customer sc on sc.customerid = soh.customerid
right join person.person p on sc.personID = p.BusinessEntityID
order by lastName, firstName

你可以看到用户支付的单独价格。但如果你这样做

select FirstName, LastName, sum(LineTotal) from sales.salesorderdetail sod 
inner join sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
inner join sales.customer sc on sc.customerid = soh.customerid
right join person.person p on sc.personID = p.BusinessEntityID
order by lastName, firstName

您将收到一个错误

Column 'person.person.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

这个错误是什么意思？嗯，SUM 是一个聚合函数。这意味着它不会在单独的一行中给出值，而是一个聚合值（从一组行中派生）。SQL 足够聪明，它会意识到您可能不想一遍又一遍地看到整个总和。如果您想知道所有订购项目的总和，请这样做

select sum(LineTotal) from sales.salesorderdetail

注意，我删除了 SQL 中所有的连接。这些连接会起到过滤值的作用（注意我也使用了右连接，因为我发现 personid 有时为 null）。如果不需要，永远不要连接，它可能会产生意想不到的后果，即过滤掉存在于您关心但不存在于您连接到的表中的行。

然而，我们想知道每个客户的总额。我们这样做

select LastName, FirstName, sum(LineTotal) as Total from sales.salesorderdetail sod 
inner join sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
inner join sales.customer sc on sc.customerid = soh.customerid
right join person.person p on sc.personID = p.BusinessEntityID
group by firstname, lastname
order by lastname, firstname

注意，我们的一些值是 NULL。我能看到的示例中，数据库中只有一个值，并且它的行总计为 NULL。但是，我想指出的行为是 NULL 不是零，因此如果您将任何值添加到 NULL，结果就是 NULL。NULL 意味着“未知值”，因此它不能被 SUM 求和或用于任何数学函数。所以正确的 SQL 是

select LastName, FirstName, sum(coalesce(LineTotal, 0)) as Total from sales.salesorderdetail sod 
inner join sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
inner join sales.customer sc on sc.customerid = soh.customerid
right join person.person p on sc.personID = p.BusinessEntityID
group by firstname, lastname
order by lastname, firstname

这将把所有 null 值替换为 0，并提供全面的有意义的答案（我们可以假设在现实世界中，NULL 意味着没有付款，这等同于零，尽管 NULL 不等于零，在这种情况下我们可以假设它等于零）。

 

当然，如果您要连接到提供产品详细信息的表，您可以按产品或其他任何您喜欢的方式进行分组。这是一种在 SQL 中查找值的非常强大的技术，如果您以前没有使用过，应该花一些时间玩玩它。

您可以使用各种其他函数，例如

select LastName, FirstName, max(coalesce(LineTotal, 0)) as Total from sales.salesorderdetail sod 
inner join sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
inner join sales.customer sc on sc.customerid = soh.customerid
right join person.person p on sc.personID = p.BusinessEntityID
group by firstname, lastname
order by total desc

将按谁为单行支付最多金额的顺序给出客户姓名（不是行项目，因为 LineTotal 可能是 800 万个便宜商品）。

 

其他功能包括

AVG	计算平均值
MIN	获取最小值
MAX	获取最大值
SUM	将所有值相加
COUNT	告诉您存在多少行

 

HAVING

尝试运行这个

select LastName, FirstName, max(coalesce(LineTotal, 0)) as Total from sales.salesorderdetail sod 
inner join sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
inner join sales.customer sc on sc.customerid = soh.customerid
right join person.person p on sc.personID = p.BusinessEntityID
where Total > 500
group by firstname, lastname
order by total desc

您将收到此错误

Invalid column name 'Total'.

由于 SQL 处理您的请求的顺序，您的列 Total 尚不存在。除非我们想使用子查询，否则我们需要重现创建该值的语句。

select LastName, FirstName, max(coalesce(LineTotal, 0)) as Total from sales.salesorderdetail sod 
inner join sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
inner join sales.customer sc on sc.customerid = soh.customerid
right join person.person p on sc.personID = p.BusinessEntityID
where  max(coalesce(LineTotal, 0)) > 500
group by firstname, lastname
order by total desc

这又出现了一个新错误

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

我们该如何解决这个问题？错误信息给了我们一个线索。“having”子句是什么？

select LastName, FirstName, max(coalesce(LineTotal, 0)) as Total from sales.salesorderdetail sod 
inner join sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
inner join sales.customer sc on sc.customerid = soh.customerid
right join person.person p on sc.personID = p.BusinessEntityID
group by firstname, lastname
having  max(coalesce(LineTotal, 0)) > 500
order by total desc

注意顺序的变化，WHERE 语句需要在 GROUP BY 之前，HAVING 需要在 GROUP BY 之后。'HAVING' 用于指定分组函数的条件。如果您要过滤非分组函数，'HAVING' 的作用与 'WHERE' 相同，但是，您需要使用 'HAVING' 来过滤聚合函数。这是因为 HAVING 在您的 SELECT 之后运行，其目的是能够根据聚合函数的结果进行过滤。

 

您不需要使语句完全相同，您可以这样做

select LastName, FirstName, max(coalesce(LineTotal, 0)) as Total from sales.salesorderdetail sod 
inner join sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
inner join sales.customer sc on sc.customerid = soh.customerid
right join person.person p on sc.personID = p.BusinessEntityID
group by firstname, lastname
having  max(LineTotal) > 500
order by total desc

甚至这个

select LastName, FirstName, max(coalesce(LineTotal, 0)) as Total from sales.salesorderdetail sod 
inner join sales.salesorderheader soh on soh.salesorderid = sod.salesorderid
inner join sales.customer sc on sc.customerid = soh.customerid
right join person.person p on sc.personID = p.BusinessEntityID
group by firstname, lastname
having  sum(LineTotal) > 20000
order by total desc

您只需要对上面正在聚合的列进行操作。

ISNULL 和 COALESCE

鉴于我一直在使用它们，现在说可能有点晚了，但这两个方法都可以用来将 NULL 替换为默认值。然而，它们并不相同。它们之间存在一些差异。最重要的是，COALESCE 可以接受多个值。这是因为 COALESCE 是 CASE 语句的语法糖，而 CASE 语句可以无限延伸。

Select coalesce(val1, val2, val3, 0) 

与此相同

Select case
When val1 is not null then val1
When val2 is not null then val2
When val3 is not null then val 3
Else 0
End

ISNULL 是 SQL Server 中的一个独立方法。它只接受两个值。这可能解释了为什么 COALESCE 往往比 ISNULL 慢。

 

如果您正在使用 Entity Framework，这意味着由于 ISNULL 使用列表中第一个对象的类型，该类型将是可空的，因此如果它是一个像 int 这样的类型，您的 EF 属性将是 INullable。您可以使用 COALESCE 尝试解决此问题，或者您可以显式地将返回类型强制转换为不可空。EF 的另一个问题是，如果您返回 1 或 0，EF 会将其视为 INT，而不是 BIT，您需要将其强制转换为 BIT 才能在 EF 中获得 bool 类型。

ISNULL 使用第一个参数来确定返回值的类型，而 COALESCE 使用与 CASE 语句相同的规则（因为它就是 CASE 语句）。

COALESCE 是标准 SQL，而 ISNULL 是专有的 T-SQL。

还有第三种结构，NULLIF。如果两个值相同，则返回 NULL。如果它们不相等，则返回第一个表达式。显然，它的结果类型是第一个表达式的类型。

CASE 语句

我也已经使用过这些了，但是，CASE 语句允许您将过程逻辑注入到基于集合的数据库代码中。

 

例如，这是显示整个 AdventureWorks 数据库季度销售总额的 SQL

select sum(linetotal),
case
    when month(modifieddate) < 4 then 1
    when month(modifieddate) < 7 then 2      
    when month(modifieddate) < 10 then 3
    else 4
end as quarter,
year(modifieddate) as year
from  sales.salesorderdetail
group by 
year(modifieddate),
case
    when month(modifieddate) < 4 then 1
    when month(modifieddate) < 7 then 2      
    when month(modifieddate) < 10 then 3
    else 4
end
order by year, quarter

请注意，我假设 CASE 语句将按照给定的顺序进行评估。根据我所读到的，似乎所有语句都会被评估（有点像 VB6），但您可以依赖值 1、2 和 3 通过第一次检查而不会落入第二次。如果这让您感到不安，您总是可以使它更明确。在生产环境中，我可能会这样做。SQL 与过程语言不同，您告诉数据库您想要什么，而不是您希望它如何完成。我发现最好总是尽可能地明确。

优化您的查询

 

我认为使用 SQL 的人们经常会忽略的一点是，您可以做很多事情，例如使用 LIKE 搜索子字符串，或者使用 YEAR 等函数搜索日期中的年份，但是如果您使用函数来修改列中的数据，您就会取消 SQL Server 使用其索引的任何机会，这将使您的查询变慢。所以，与其使用

select * from tbl where YEAR(birthday) = 1969

一个不修改表中列的查询会更快，例如

select * from tbl where birthday between '19690101' and '19691231'

这种特定形式仅在“birthday”是日期而不是日期时间时才有效，请阅读我关于日期处理的文章，了解更多关于如何良好处理日期和时间列的详细信息。

结论

我假设任何阅读本文的人都曾编写过 SELECT 语句，但希望我至少为某些人打开了几扇额外的门。一旦你理解了 SQL 的工作原理，它就变得异常强大和富有表现力。最好的学习方法是拿起我的语句，修改它们并查看你得到的结果。这就是 AdventureWorks 很棒的原因，那里有大量数据供你尝试其他任务，看看你是否能弄清楚如何完成它们。

正如我所说，我很快就会开始写一篇关于 CTE 的文章，其中包含在 SQL Server 中创建递归查询的能力。这非常酷，我怀疑它不会在周日上线，因为我预计我需要相当长的时间来涵盖所有方面，但一旦我完成，它就会上线。我希望你玩得开心，我当然玩得很开心。