位置: 编程技术 - 正文

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)

  • 所得税费用影响企业利润总额吗
  • 增值税税率改变后原项目的新增单价按哪个税率
  • 购买免税产品的app
  • 债券到期账务处理
  • 小规模未达到起征点申报表怎么填
  • 员工辞退补偿金需要交个税吗
  • 工资不纳税项目
  • 有金额的框架协议要交印花税吗
  • 税务局三代手续费政策
  • 增值税普通发票和普通发票的区别怎么交税
  • 退销售货款的现金流是哪一个
  • 出借包装物一次摊销金额计算
  • 资产减值损失转回的账务处理
  • 一般纳税人开专票和普票的税率
  • 车船使用税是否必须交
  • 进项增值税发票怎么认证
  • 中小五金企业如何进行成本核算
  • 残保金中的工资总额在哪看
  • 索赔发票会计入账
  • 独立核算的分公司可以享受小微企业吗
  • 消费税的计算公式推导过程
  • 抵押贷款逾期怎么处理抵押物
  • 包装物租金怎么算
  • 若依框架入门
  • 日历怎么不能添加事件了
  • 医保养老保险怎么查
  • 其他货币资金明细账填写样本
  • 滞纳金不得超过税款
  • 企业所得税的应纳税额计算公式
  • vue 浮动窗口
  • node.js什么意思
  • 购买房屋用于出租属于投资行为吗
  • 被投资的公司注销后投资公司怎么处理
  • 长期借款主要包括哪些
  • 应交税费借方属于什么科目
  • 速动资产扣除应纳税所得
  • 税金及附加如何预测
  • 企业代收水电费算不算收入
  • 开票资料更改
  • 合营企业和联营企业都是权益法吗
  • 债权人债务重组损益计入什么科目
  • 资产减值损失的定义
  • 建筑业挂靠企业所得税如何收取?
  • 内账需要哪些单据
  • 房产税应纳税额计算例题
  • 营业外收入可以冲减管理费用吗
  • 如何区分借贷方和负债方
  • 房地产开发结转成本
  • 可供出售金融资产可以转为交易性金融资产吗
  • 开公司合理避税的方法
  • 营改增的范围
  • 固定资产计提完了怎么处理
  • 展览展示服务费计入什么科目
  • 员工出国境有关说法
  • sql server高级编程
  • sql服务在哪
  • 怎么删除服务器管理的服务
  • linux px
  • linux磁盘管理fdisk
  • 在linux中,要解包bugzilla.tar.gz
  • xp系统超级终端机在哪
  • mac关机快捷键是什么键
  • msng.exe是什么
  • win7升级到win10有无影响
  • win10自动更新win11怎么办
  • Android 近百个项目的源代码
  • linux rpm包怎么安装
  • html截取字符串
  • android时间轴实现
  • 用python做开发
  • jquery 使用
  • javascript初级教程
  • android获取本地相册中图片
  • jquery改变img的src
  • twist python
  • jquery动态添加html代码
  • 国家税务总局官网电子税务局
  • 研发材料占研发费用比例超出50%
  • 增值税应税货物清单
  • 2018年税务局
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

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

    友情链接: 武汉网站建设