位置: 编程技术 - 正文

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

编辑:rootadmin

Select字句在逻辑上是SQL语句最后进行处理的最后一步,所以,以下查询会发生错误:

SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM dbo.OrdersGROUP BY OrderYear;

因为group by是在Select之前进行的,那个时候orderYear这个列并没有形成。

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

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM (SELECT YEAR(OrderDate) AS OrderYear, CustomerID FROM dbo.Orders) AS DGROUP BY OrderYear;

还有一种很特殊的写法:

SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM (SELECT YEAR(OrderDate), CustomerID FROM dbo.Orders) AS D(OrderYear, CustomerID)GROUP BY OrderYear;

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

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

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

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

Common Table Expressions (CTE)

CTE是在SQL新加入的一种表的表示类型。

它的定义如下:

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 NumCustsFROM CGROUP BY OrderYear;

当然,作者本人有更推荐的写法:

WITH C(OrderYear, CustomerID) AS( SELECT YEAR(OrderDate), CustomerID FROM dbo.Orders)SELECT OrderYear, COUNT(DISTINCT CustomerID) AS NumCustsFROM CGROUP 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, NumCustsFROM C2WHERE NumCusts > ;

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 GrowthFROM 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;GOWITH 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, FaxINTO dbo.CustomersDupsFROM 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.VYearCntASWITH YearCnt AS( SELECT YEAR(OrderDate) AS OrderYear, COUNT(DISTINCT CustomerID) AS NumCusts FROM dbo.Orders GROUP BY YEAR(OrderDate))SELECT * FROM YearCnt;

CTE实例六(CTEs的递归)

作者给了一个例子,来讲述这个在SQL的新内容,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索引是SQL的新功能。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为,当n=0时,无限制。

推荐整理分享SqlServer 2005 T-SQL Query 学习笔记(1),希望有所帮助,仅作参考,欢迎阅读内容。

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

文章相关热门搜索词:,内容如对您有帮助,希望把文章链接给更多的朋友!

SqlServer T-SQL Query 学习笔记(2) SQL增加了4个关于队计算的函数:分别是ROW_NUMBER,RANK,DENSE_RANK,NTILE.注意:这些函数只能出现在SELECT和ORDERBY的查询中。语法如下:ranking_functionover([partiti

SqlServer T-SQL Query 学习笔记(3) ADHOCPAGING:就是指用页面的序号和页面的大小请求一个单独的页面。下面是例子。DECLARE@pagesizeASINT,@pagenumASINT;SET@pagesize=5;SET@pagenum=2;WITHSalesCTEAS(SELECTROW_NUM

SqlServer T-SQL Query 学习笔记(4) 比如,我要建立一个1,,行的数字表:CREATETABLEdbo.Nums(nINTNOTNULLPRIMARYKEY);DECLARE@maxASINT,@rcASINT;SET@max=;SET@rc=1;INSERTINTONumsVALUES(1);WHILE@rc*2=@maxBEGININSE

标签: SqlServer 2005 T-SQL Query 学习笔记(1)

本文链接地址:https://www.jiuchutong.com/biancheng/319296.html 转载请保留说明!

上一篇:关于MSSQL 2005的安装与补丁(ms sql 2012)

下一篇:SqlServer 2005 T-SQL Query 学习笔记(2)

  • 简易计税方法和免税该怎么选择
  • 金融类企业
  • 小规模企业免税怎么做账
  • 自己打印的机票怎么退票
  • 红字信息表可以开一半吗
  • 税控服务费列入管理费用什么费用?
  • 注销公司需要提供股东身份证吗
  • 企业收到政府补助要交税吗
  • 承兑汇票银行背书
  • 一个十图
  • 旅游合同签订
  • 民间非营利组织有哪些
  • 报关单上的运费怎么看
  • 没有及时去做财务会计制度备案怎么办?
  • 利润表的上期金额和本期金额之间的关系
  • 商贸公司进项税额大
  • 什么叫交付使用
  • PHP:xml_error_string()的用法_XML解析器函数
  • php更新数据库
  • pc安装苹果系统
  • 商会账目
  • 红掌的养殖方法和注意事项
  • 调制解调器的作用包括
  • 结算借款的账务处理办法
  • 怎么启用framework3.5
  • 现金折扣的账务处理最新规定
  • 利润分配的会计分录怎么写
  • 弃置费用的会计处理
  • 请简述你对php的理解并描述php的工作流程
  • pytorch train()
  • 员工报销的话费要缴纳个税吗?
  • vue3的
  • php图片代码
  • 拨出专款年末结转
  • 织梦发布文章栏目怎么不显示
  • 收入成本怎么做账
  • 小规模缴纳印花税吗怎么做账
  • 餐饮服务需要卫生许可证吗
  • 未满一年需要工商年报吗
  • 委托加工物资验收时发生的成本怎么处理
  • 期末留底的进项税怎么下账
  • 年金一定是一年一次吗
  • 企业职工福利费使用范围和标准
  • 跨年更正错误记账凭证的方法
  • 境外劳务输出收费表标准
  • 实收资本可以做现金吗
  • 对公账户付个人工资怎么入账
  • 公司人事怎么办公积金提取流程?
  • 公司团体体检一般什么价位
  • 可供分配利润的计算公式
  • 旅客运输服务客体是什么
  • 总公司与分公司的关系
  • 其他收益和营业外收入有什么区别
  • sql语句计算两个字符
  • mysql数据库主机一般填什么
  • linux datetime命令
  • freebsd安装pkg
  • mac休眠怎么设置
  • win8怎么进去安全系统
  • linux安装.sh
  • three.js gui
  • nginx日志按天切割
  • django环境配置
  • Python列表list
  • unity打包64位出错
  • linux删除文件语句
  • 分页jsp
  • python内置函数用来打开或创建文件并返回文件对象
  • Unity3D AsssetBundle加载效率比较
  • jquery 放大镜
  • unity 内嵌浏览器
  • java中主要使用unicode编码方式
  • javascript用户名验证
  • Mac Error Create Android Project - “Errors running builder 'Android Resource Manager' on project”
  • 国家税务总局在哪
  • 支付宝中油好客e站怎么开发票
  • 文化服务包括哪些专业
  • 税控盘离线开票金额为零怎么办
  • 天津地税自助办税终端
  • 知道金额,税额怎么计算
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设