SqlServer 2005 T-SQL Query 学习笔记（1)

select字句在逻辑上是sql语句最后进行处理的最后一步，所以，以下查询会发生错误：

select
 year(orderdate) as orderyear,
 count(distinct customerid) as numcusts
from dbo.orders
group by orderyear;

因为group by是在select之前进行的，那个时候orderyear这个列并没有形成。

 

如果要查询成功，可以像下面进行修改：

select orderyear, count(distinct customerid) as numcusts
from (select year(orderdate) as orderyear, customerid
   from dbo.orders) as d
group by orderyear;

还有一种很特殊的写法：

select orderyear, count(distinct customerid) as numcusts
from (select year(orderdate), customerid
   from dbo.orders) as d(orderyear, customerid)
group by orderyear;

在作者眼里，他是非常喜欢这种写法的，因为更清晰，更明确，更便于维护。

 

在查询中使用参数定向产生一批结果，这个技巧没有什么好说的。

 

嵌套查询，在处理逻辑上是从里向外进行执行的。

 

多重引用，有可能你的sql语句包含了多次从一个表进行查询后进行连接组合。比如你要比较每年的顾客数同先前年的顾客数的变化，所以你的查询就必须join了2个相同的表的实例，这也是不可避免的。

 

common table expressions (cte)

cte是在sql2005新加入的一种表的表示类型。

它的定义如下：

with cte_name

as

(

cte_query

)

outer_query_refferring to_cte_name;

注意：因为在标准的t-sql语言中已经包含了with关键字，所以为了区分，cte在语句的结尾加上了“;”作为停止符。

 

cte实例一（结果集别名）

with c as
(
 select year(orderdate) as orderyear, customerid
 from dbo.orders
)
select orderyear, count(distinct customerid) as numcusts
from c
group by orderyear;

 

当然，作者本人有更推荐的写法：

with c(orderyear, customerid) as
(
 select year(orderdate), customerid
 from dbo.orders
)
select orderyear, count(distinct customerid) as numcusts
from c
group by orderyear;

 

cte实例二（多重ctes）

with c1 as
(
 select year(orderdate) as orderyear, customerid
 from dbo.orders
),
c2 as
(
 select orderyear, count(distinct customerid) as numcusts
 from c1
 group by orderyear
)
select orderyear, numcusts
from c2
where numcusts > 70;

 

cte实例三（多重引用）

with yearlycount as
(
 select year(orderdate) as orderyear,
  count(distinct customerid) as numcusts
 from dbo.orders
 group by year(orderdate)
)
select cur.orderyear,
 cur.numcusts as curnumcusts, prv.numcusts as prvnumcusts,
 cur.numcusts - prv.numcusts as growth
from yearlycount as cur
 left outer join yearlycount as prv
  on cur.orderyear = prv.orderyear + 1;

 

cte实例四（修改数据）

1.把从customer表查询出来的结果，动态的组装进新表customersdups里：

if object_id('dbo.customersdups') is not null
 drop table dbo.customersdups;
go

with crosscustomers as
(
 select 1 as c, c1.*
 from dbo.customers as c1, dbo.customers as c2
)
select row_number() over(order by c) as keycol,
 customerid, companyname, contactname, contacttitle, address,
 city, region, postalcode, country, phone, fax
into dbo.customersdups
from crosscustomers;

 

2.使用cte移除数据，只保留customerdups表里同一customerid里keycol为最大的记录。

with justdups as
(
 select * from dbo.customersdups as c1
 where keycol <
  (select max(keycol) from dbo.customersdups as c2
   where c2.customerid = c1.customerid)
)
delete from justdups;

 

cte实例五（对象容器）

即提供了封装的能力，有利于组件化的编程。作者额外的提醒，cte无法直接内嵌，但是可以通过把cte封装进一个对象容器里并从一个外部的cte里对这容器的数据进行查询而实现内嵌。

 

作者也说明了，使用ctes在view和udfs里是没有什么价值的。

有个例子，如下:

create view dbo.vyearcnt
as
with yearcnt as
(
 select year(orderdate) as orderyear,
  count(distinct customerid) as numcusts
 from dbo.orders
 group by year(orderdate)
)
select * from yearcnt;

 

cte实例六（ctes的递归）

作者给了一个例子，来讲述这个在sql2005的新内容，ctes的递归。

根据employeeid，返回此员工的信息，并包含所有下级员工的信息。（等级关系基于empolyeeid和reportsto的属性）所返回的结果包含下列字段，employeeid,reportsto,firstname,lastname。

 

作者在这里，给予了一个最佳的索引方式：

create unique index idx_mgr_emp_ifname_ilname
 on dbo.employees(reportsto, employeeid)
 include(firstname, lastname);

 

作者的解释： 这个索引将通过一个单独的查询（局部扫描）来取得每个经理的直接下级。include（fristname,lastname）加在这里，即是覆盖列。

 

小知识：什么include索引？

include索引是sql2005的新功能。include索引的列并不影响索引行的物理存储顺序，他们作为一个挂件‘挂在'索引行上。挂这些‘挂件'的目的在于，只需要扫描一把索引就获得了这些附加数据。

 

回到作者的例子上，下面是递归的代码：

with empscte as
(
 select employeeid, reportsto, firstname, lastname
 from dbo.employees
 where employeeid = 5
 union all

 select emp.employeeid, emp.reportsto, emp.firstname, emp.lastname
 from empscte as mgr
  join dbo.employees as emp
   on emp.reportsto = mgr.employeeid
)
select * from empscte;

 

理解：一个递归的cte包含了至少2个查询，第一个查询在cte的身体里类似于一格锚点。这个锚点仅仅返回一个有效的表，并作为递归的一个锚。从上的例子看出来，锚点仅仅返回了一个employeeid = 5 的一行。然后的第2个查询是作为递归成员。当查询到下属成员的结果为空时，此递归结束。

 

如果你担心递归会造成永久循环，你可以使用下面的表达：

with cte_name as (cte_body) outer_query option (maxrecursion n);

默认的n为100，当n=0时，无限制。