SQL笔记（五）

1.通用表表达式CTE

CTE是一个指定的临时结果集，在select的from子句使用。使包含派生表的查询更简单。

WITH CTE名称 （列列表）

AS

   （SELECT查询语句）

然后对CTE进行查询，可以得到例如特定地区的特定售货员的销售额。

2.关键字IN和BETWEEN...AND

IN的意思是给定可选项，查询所需，如IN(2006,2007)就是指在2006和2007这两个年份查询

BETWEEN....AND是指限定查询范围，如between 2000  and 2010

3.PIVOT运算符

可以生成交叉制表报表，将行旋转成列

Use AdventureWorks
go
  select CardType,[2006] as Year2006,[2007] as Year2007  //2006和2007先是在列显示，反转后在行显示
from
(select CardType,ExpYear from Sales.CreditCard)piv Pivot
(count(ExpYear) for ExpYear in ([2006],[2007])) as carddetail
order by CardType

查询了各种银行卡在2006年和2007年到期的总数。

4.ROW_NUMBER()和PARTITION BY函数

ROW_NUMBER()函数用于排列，给还回值赋予唯一的标号;PARTITION BY运用与将结果集划分为应用了ROW_NUMBER()的分区。

select CustomerID,TerritoryID,
Row_Number() over (Partition by TerritoryID order by CustomerID) as [RowCount] //按照区域ID划分，对客户ID进行排列
from Sales.Customer
where TerritoryID in (1,2) and CustomerID between 1 and 75  //区域ID限定为1和2（1有一个顺序计数，2也有），客户ID限定到75

5.模式匹配

%：表示0和多个字符，例如LIKE "%C#%"表示查询字符串中包含C#，而不管它所处位置。

_：表示单个字符，如LIKE “_def__”表示查询字符串是6个字符，第2、3、4个字符必须是def。

[]：指定在查询范围内的任意字符，如LIKE '[A-K]def'表示查询字符是4个字符，第一个字符必须是A与K之间的字符且后面四个字符必须是以def结尾。

[^]：指定不是括号内的字符，如LIKE ‘de[^f]%’表示开头是de，第3个字符不是f的任意字符串。