当前位置：Gxlcms > 数据库问题 > 笔记-Microsoft SQL Server 2008技术内幕：T-SQL语言基础-02 单表查询

笔记-Microsoft SQL Server 2008技术内幕：T-SQL语言基础-02 单表查询

时间：2021-07-01 10:21:17 帮助过：4人阅读

orderid, YEAR(orderdate) AS orderyear FROM Sales.Orders WHERE orderyear > 2006;

这是错误的，WHERE子句中并不能识别orderyear别名，应该改为：

  1. <span style="color: #0000ff;">SELECT</span> orderid, <span style="color: #ff00ff;">YEAR</span>(orderdate) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> orderyear
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> Sales.Orders
  3. </span><span style="color: #0000ff;">WHERE</span> <span style="color: #ff00ff;">YEAR</span>(orderdate) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">2006</span>;

 

关于WITH TIES选项

先看下面这段代码：

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> (<span style="color: #800000; font-weight: bold;">5</span><span style="color: #000000;">) orderid, orderdate, custid, empid
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> Sales.Orders
  3. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> orderdate <span style="color: #0000ff;">DESC</span>, orderid <span style="color: #0000ff;">DESC</span>;

执行查询结果如图：

技术分享
加上WITH TIES选项后：

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> (<span style="color: #800000; font-weight: bold;">5</span>) <span style="color: #0000ff;">WITH</span><span style="color: #000000;"> TIES orderid, orderdate, custid, empid
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> Sales.Orders
  3. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> orderdate <span style="color: #0000ff;">DESC</span>;

 

再看执行结果：

技术分享

也就是说WITH TIES选项能够返回与TOP n 行中最后一行（在这个例子中式2008年5月5日）的排序值（在这个例子中是orderdate）相同的其他所有行。 

OVER子句

先看下面这段代码：

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> orderid, custid, val,
  2.   </span><span style="color: #ff00ff;">SUM</span>(val) <span style="color: #0000ff;">OVER</span>() <span style="color: #0000ff;">AS</span><span style="color: #000000;"> totalvalue,
  3.   </span><span style="color: #ff00ff;">SUM</span>(val) <span style="color: #0000ff;">OVER</span>(PARTITION <span style="color: #0000ff;">BY</span> custid) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> custtotalvalue
  4. </span><span style="color: #0000ff;">FROM</span> Sales.OrderValues;

 执行结果：

技术分享

再看下面的代码：

  1. <span style="color: #0000ff;">SELECT</span>  <span style="color: #ff00ff;">SUM</span>(val) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> totalvalue
  2. </span><span style="color: #0000ff;">FROM</span>    Sales.OrderValues;

 执行结果：

技术分享

对比可知，使用OVER就不必对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。

再看一段代码：

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> orderid, custid, val,
  2.   </span><span style="color: #800000; font-weight: bold;">100</span>. <span style="color: #808080;">*</span> val <span style="color: #808080;">/</span> <span style="color: #ff00ff;">SUM</span>(val) <span style="color: #0000ff;">OVER</span>() <span style="color: #0000ff;">AS</span><span style="color: #000000;"> pctall,
  3.   </span><span style="color: #800000; font-weight: bold;">100</span>. <span style="color: #808080;">*</span> val <span style="color: #808080;">/</span> <span style="color: #ff00ff;">SUM</span>(val) <span style="color: #0000ff;">OVER</span>(PARTITION <span style="color: #0000ff;">BY</span> custid) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> pctcust
  4. </span><span style="color: #0000ff;">FROM</span> Sales.OrderValues;

 执行结果：

技术分享

注意上面这段代码中的一个小细节，就是100后面加个点，而不是直接使用整数100，因为这样可以隐式将整数值val和SUM(val)转换成十进制实数值，否则表达式中的除法将是“整数除法”，会截去数值的小数部分。

OVER子句也支持四种排名函数：ROW_NUMBER(行号)、RANK(排名)、DENSE_RANK(密集排名)、NTILE，看下面的代码：

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> orderid, custid, val,
  2.   ROW_NUMBER() </span><span style="color: #0000ff;">OVER</span>(<span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> val) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> rownum,
  3.   RANK()       </span><span style="color: #0000ff;">OVER</span>(<span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> val) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> rank,
  4.   DENSE_RANK() </span><span style="color: #0000ff;">OVER</span>(<span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> val) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> dense_rank,
  5.   NTILE(</span><span style="color: #800000; font-weight: bold;">10</span>)   <span style="color: #0000ff;">OVER</span>(<span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> val) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> ntile
  6. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> Sales.OrderValues
  7. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> val;

 执行结果：

技术分享技术分享

 

简单解释一下上面的各个函数。

ROW_NUMBER用于为查询的结果集中的各行分配递增的序列号，其逻辑顺序通过OVER子句中的ORDER BY语句进行指定。ROW_NUMBER生成的是唯一的行号值。RANK和DENSE_RANK的区别是：RANK表示之前有多少行具有更低的排序值，而DENSE_RANK则表示之前有多少个更低的排序值。NTILE函数可以把结果中的行关联到组，并为每一行分配一个所属的组的编号。NTILE函数接受一个表示组数量的输入参数，并要在OVER子句中指定逻辑顺序。上面代码例子中是分为10组。

在OVER子句中使用PARTITION BY语句：

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> orderid, custid, val,
  2.   ROW_NUMBER() </span><span style="color: #0000ff;">OVER</span>(PARTITION <span style="color: #0000ff;">BY</span><span style="color: #000000;"> custid
  3.                     </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> val) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> rownum
  4. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> Sales.OrderValues
  5. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> custid, val;

 执行结果：

技术分享

从结果中可以看出，行号是为每一个客户独立计算的。

 

注意一点，如果在SELECT处理阶段指定了开窗函数，开窗计算会在DISTINCT子句（如果有）之前进行处理。

谓词和运算符

常见的谓词有：IN、BETWEEN、LIKE等。

CASE表达式

先看一个简单的：

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> productid, productname, categoryid,
  2.   </span><span style="color: #ff00ff;">CASE</span><span style="color: #000000;"> categoryid
  3.     </span><span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Beverages</span><span style="color: #ff0000;">‘</span>
  4.     <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Condiments</span><span style="color: #ff0000;">‘</span>
  5.     <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Confections</span><span style="color: #ff0000;">‘</span>
  6.     <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">4</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Dairy Products</span><span style="color: #ff0000;">‘</span>
  7.     <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">5</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Grains/Cereals</span><span style="color: #ff0000;">‘</span>
  8.     <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">6</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Meat/Poultry</span><span style="color: #ff0000;">‘</span>
  9.     <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">7</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Produce</span><span style="color: #ff0000;">‘</span>
  10.     <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">8</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Seafood</span><span style="color: #ff0000;">‘</span>
  11.     <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Unknown Category</span><span style="color: #ff0000;">‘</span>
  12.   <span style="color: #0000ff;">END</span> <span style="color: #0000ff;">AS</span><span style="color: #000000;"> categoryname
  13. </span><span style="color: #0000ff;">FROM</span> Production.Products;

 执行结果：

技术分享

如果CASE表达式中没有ELSE子句，则默认将其视为ELSE NULL。

看一个复杂一点的：

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> orderid, custid, val,
  2.   </span><span style="color: #ff00ff;">CASE</span> NTILE(<span style="color: #800000; font-weight: bold;">3</span>) <span style="color: #0000ff;">OVER</span>(<span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> val)
  3.     </span><span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Low</span><span style="color: #ff0000;">‘</span>
  4.     <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Medium</span><span style="color: #ff0000;">‘</span>
  5.     <span style="color: #0000ff;">WHEN</span> <span style="color: #800000; font-weight: bold;">3</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">High</span><span style="color: #ff0000;">‘</span>
  6.     <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Unknown</span><span style="color: #ff0000;">‘</span>
  7.   <span style="color: #0000ff;">END</span> <span style="color: #0000ff;">AS</span><span style="color: #000000;"> titledesc
  8. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> Sales.OrderValues
  9. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> val;

 执行结果：

技术分享

CASE搜索表达式：

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> orderid, custid, val,
  2.   </span><span style="color: #ff00ff;">CASE</span> 
  3.     <span style="color: #0000ff;">WHEN</span> val <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">1000.00</span>                   <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Less then 1000</span><span style="color: #ff0000;">‘</span>
  4.     <span style="color: #0000ff;">WHEN</span> val <span style="color: #808080;">BETWEEN</span> <span style="color: #800000; font-weight: bold;">1000.00</span> <span style="color: #808080;">AND</span> <span style="color: #800000; font-weight: bold;">3000.00</span> <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Between 1000 and 3000</span><span style="color: #ff0000;">‘</span>
  5.     <span style="color: #0000ff;">WHEN</span> val <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">3000.00</span>                   <span style="color: #0000ff;">THEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">More than 3000</span><span style="color: #ff0000;">‘</span>
  6.     <span style="color: #0000ff;">ELSE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Unknown</span><span style="color: #ff0000;">‘</span>
  7.   <span style="color: #0000ff;">END</span> <span style="color: #0000ff;">AS</span><span style="color: #000000;"> valuecategory
  8. </span><span style="color: #0000ff;">FROM</span> Sales.OrderValues;

 执行结果：

技术分享

 

排序规则

如果想在列的排序规则是不区分大小写的前提下，让过滤条件是区分大小写的，则可以按如下方法修改表达式的排序规则：

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> empid, firstname, lastname
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> HR.Employees
  3. </span><span style="color: #0000ff;">WHERE</span> lastname COLLATE Latin1_General_CS_AS <span style="color: #808080;">=</span> N<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">davis</span><span style="color: #ff0000;">‘</span>;

日期和时间

先看下面代码：

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">GETDATE</span><span style="color: #000000;">()
  2. </span><span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">CURRENT_TIMESTAMP</span>

 上面两句代码返回的日期是一样的，但是CURRENT_TIMESTAMP是标准SQL，所以优先推荐使用CURRENT_TIMESTAMP。

笔记-Microsoft SQL Server 2008技术内幕：T-SQL语言基础-02 单表查询

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！