您现在的位置是：网站首页> 编程资料编程资料

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

2023-05-27 378人已围观

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

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

(

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提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                
                
                                            上一篇：关于MSSQL 2005的安装与补丁_mssql2005_
                                                                下一篇：SqlServer 2005 T-SQL Query 学习笔记（2）_mssql2005_

您现在的位置是：网站首页> 编程资料编程资料

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

相关内容

点击排行

本栏推荐

猜你喜欢