说到联接与集合操作,其实它们就是对表的2种形式的操作。前者是表之间的水平操作,后者是表之间的垂直操作。
1.联接
联接分为2个时期.旧语法中,FROM子句后面表之间用逗号分开,没有JOIN ON 关键字.新语法才加入了JOIN关键字和ON子句.还引入了外部联接.
旧的语法也是支持外部联接的,不过形式有所不同。以前关于外部联接的语法是 *=和=*(左右联接),不推荐.
关于这方面的知识,可以参考JJ大大有篇写得很好的文章(
http://blog.csdn.net/jinjazz/archive/2009/08/10/4430354.aspx)
CROSS:
交叉连接可以用于对某个的表每条记录生产一定数量的副本.
举个例子: Customers 表 表示消费信息,Employees 表示员工信息 ,我现在要为消费者和员工的每个组合生成一个指定日期内每天的订单表
DECLARE @fromdate AS DATETIME, @todate AS DATETIME;
SET @fromdate = '20060101';
SET @todate = '20060131';
----2000
SELECT IDENTITY(int, 1, 1) AS OrderID,
CustomerID, EmployeeID,
DATEADD(day, n-1, @fromdate) AS OrderDate
INTO dbo.MyOrders
FROM dbo.Customers, dbo.Employees, dbo.Nums
WHERE n <= DATEDIFF(day, @fromdate, @todate) + 1;
select * from dbo.MyOrders
--2005
WITH Orders
AS
(
SELECT CustomerID, EmployeeID,
DATEADD(day, n-1, @fromdate) AS OrderDate
FROM dbo.Customers, dbo.Employees, dbo.Nums
WHERE n <= DATEDIFF(day, @fromdate, @todate) + 1
)
SELECT ROW_NUMBER() OVER(ORDER BY OrderDate) AS OrderID,
CustomerID, EmployeeID, OrderDate
FROM Orders;
利用Cross 可以提高行与聚合函数计算的效率
--计算表里的每行值 占总和的百分比以及与平均值的差值
create table #(a int)
insert # select 1
union all select 2
union all select 5
union all select 6
union all select 7
union all select 9
--方法1: 子查询
select A,
百分比=cast(A*1.0/(select SUM(A) from # ) as decimal(18,2)),
差值=A-(select AVG(1.*A) from #)
from #
--方法2:CROSS
select A,
百分比=cast(A*1.0/summ as decimal(18,2)),
差值=A-avgg
from #,(select SUM(A) as summ,AVG(A) as avgg from #) k
--方法3:over
select A,
百分比 =cast(A*1.0/ SUM(a) over() as decimal(18,2)),
差值=A- AVG(1.*A) over()
from #
/*
A 百分比 差值
----------- --------------------------------------- ---------------------------------------
1 0.03 -4.000000
2 0.07 -3.000000
5 0.17 0.000000
6 0.20 1.000000
7 0.23 2.000000
9 0.30 4.000000
*/
--通过执行计划 你可以发现 第一个是最慢的,最快的是第三个,比第二个还要快将近一倍
INNER:
这里先说个细节:其实用INNER JOIN ON 是个好习惯我觉得.如果你忘记在ON后面写条件,那么会出错.但是你忘在WHERE里面写条件,那分析器
只会把你的查询当做CROSS JOIN 。
OUTER:
分为三种:left.right.full 。注意他在逻辑步骤中的地位.是在ON筛选后,进行添加.
这个时候条件写where 后面和ON后面是有区别的.具体参考我之前写的 学习笔记4--逻辑查询
NATURAL JOIN :
..传说中的自然连接.隐含连接条件是表中具有相同列名的列 相等..未被支持现在...
其他链接如:自连接,不等连接,多连接,半连接等.
这里提下多连接:多个表的INNER JOIN 你无论怎么安排顺序都是一样的,不会影响查询结果的。执行计划甚至会出现不同的访问次序.
当多表链接设计到OUTER JOIN时候,可能不当的次序会出现逻辑错误.
create table #1 (A INT )
CREATE TABLE #2 (B int)
create table #3 (a int, b int)
insert #1 select
1 union all select 2 union all select 3
insert #2 select
2 union all select 3
insert #3 select
1,1 union all select 2,2 union all select 3,3
--目的:通过右连接 使得#1表的记录都存在 即使为NULL
select a.*
from #3 c right join #1 a on a.A= c.a join #2 b on c.b=b.B
/*
A
-----------
2
3
*/
select a.*
from #3 c join #2 b on c.b=b.B right join #1 a on a.A= c.a
/*
A
-----------
1
2
3
*/
select a.*
from #1 a left join (#2 b join #3 c on b.B=c.b) on a.A=c.a
/*
A
-----------
1
2
3
*/
-----这里就看出了多表连接涉及到外联接时候需要注意的地方 第一个结果并不是预期的结果 因为它进行了右连接后 又因为和第三个表链接
-----导致不符合条件的记录缺失.推荐使用第三种带括号的 条理会比较清晰 当然第二种也可以
关于连接的问题:
--问题描述:求每个月一年内的销售总额(这里假设每个月都有了)
MonthlyOrders 表如下:
/*
ordermonth numorders
----------------------- -----------
1996-07-01 00:00:00.000 22
1996-08-01 00:00:00.000 25
1996-09-01 00:00:00.000 23
1996-10-01 00:00:00.000 26
1996-11-01 00:00:00.000 25
1996-12-01 00:00:00.000 31
1997-01-01 00:00:00.000 33
1997-02-01 00:00:00.000 29
1997-03-01 00:00:00.000 30
1997-04-01 00:00:00.000 31
1997-05-01 00:00:00.000 32
1997-06-01 00:00:00.000 30
1997-07-01 00:00:00.000 33
1997-08-01 00:00:00.000 33
1997-09-01 00:00:00.000 37
1997-10-01 00:00:00.000 38
1997-11-01 00:00:00.000 34
1997-12-01 00:00:00.000 48
1998-01-01 00:00:00.000 55
1998-02-01 00:00:00.000 54
1998-03-01 00:00:00.000 73
1998-04-01 00:00:00.000 74
1998-05-01 00:00:00.000 14
*/
select b.ordermonth,
numorders=SUM(c.numorders)
from MonthlyOrders a
join MonthlyOrders b on DATEADD(M,11,b.ordermonth)=a.ordermonth
join MonthlyOrders c on c.ordermonth between b.ordermonth and a.ordermonth
group by b.ordermonth
/*
ordermonth numorders
----------------------- -----------
1996-07-01 00:00:00.000 337
1996-08-01 00:00:00.000 348
1996-09-01 00:00:00.000 356
1996-10-01 00:00:00.000 370
1996-11-01 00:00:00.000 382
1996-12-01 00:00:00.000 391
1997-01-01 00:00:00.000 408
1997-02-01 00:00:00.000 430
1997-03-01 00:00:00.000 455
1997-04-01 00:00:00.000 498
1997-05-01 00:00:00.000 541
1997-06-01 00:00:00.000 523
*/
--上面的问题就是采用了自连接的方法 连续聚合而成这些记录
2.集合操作
集合操作在2005中有
UNION--(返回2个输入表的行)
EXCEPT--(返回位于第一个输入表的行但是不属于第二个输入表的行)
INTERSECT--(返回2个输入表都存在的行)
PS:集合操作对NULL的判断是认为2NULL是相等的.
这里有一个问题;
也许你会觉得EXCEPT 可以用NOT EXISTS 代替. 其实有些情况下是不一样的,我举例说明:
create table test1 (a int ,b int)
create table test2 (c int, d int)
insert test1 select 1,2 union all select 1,null union all select 3,4
insert test2 select 1,2 union all select 1,null
-- except
select * from test1
except
select * from test2
/*
a b
----------- -----------
3 4
*/
-- not exists
select * from test1
where not exists(select * from test2 where a=c and b=d)
/*
a b
----------- -----------
1 NULL
3 4
*/
----------从上面可以看出 当筛选表里的记录和被筛选表的相同记录里含有NULL值时候 你用NOT EXISTS 它永远认为这2个是不相等的.
这里同样的问题会出现在INTERSECT.
这三个集合操作的优先级最高为:INTERSECT ,之后就是按从上到下出现的次序
再说最后一个注意点:
除了排序之为的逻辑处理,如联接 筛选 分组 TOP等都不允许直接用在集合结果集上 ,如:
create table #a (a int, b int )
create table #b (c int, d int )
insert #a select
1,2 union all select 3,9 union all select 4,8
insert #b select
4,2 union all select 5,9 union all select 3,2 union all select 4,9
--这里直接作于#A表 而不是整个结果集
select top 1
* from #a
union all
select * from #b
--列名 'A' 无效。
select * from #a
union all
select * from #b
group by A
-----我们可以通过将结果集包装成一个派生表 或者CTE进行操作.
小技巧:这时候如果你想对2输入表分别进行排序,比如上面的#a的a列升序,#b的d列降序,可以这样
select a,b
from (
select *,flag=1 from #a
union all
select *,2 from #b
) l
order by case when flag=1 then a end ,case when flag=2 then b end desc
/*
a b
----------- -----------
5 9
4 9
4 2
3 2
1 2
3 9
4 8
*/
相关推荐
卸载清除工具,干净的卸载sql server 标题: Microsoft SQL Server 2008 R2 安装程序 ------------------------------ 出现以下错误: MOF 编译器无法连接 WMI 服务器。原因可能是语义错误(例如,与现有 WMI ...
学习sql server 和sql 的两本经典的著作: 《sql server 2005 技术内幕 T-SQL查询》 《sql server 2005 技术内幕 T-SQL程序设计》 网上大多的资源都是英文的,好容易找到中文的了,上传上来和大家分享。 这两本书都...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
Microsoft SQL Server 2008技术内幕 T-SQL 查询 一书中,第四章,索引优化章节的示例数据库脚本。
SQL Server2000设计与T-SQL编程电子书籍,对学习ms sql 与存储过程代码编写是一本很不错大书
SQLServer2005技术内幕T-SQL查询的代码示例
java 连接sqlserver使用的java-sqlserver-connect.jar包。 java连接sqlserver使用的jar包,包含jre7、jre8两种使用 sqlserver.ja
Microsoft SQL SERVER 2008技术内幕 T-SQL查询
sqlserver个人学习笔记
复制)、SQL开发(数据库设计、SQL Server与CLR集成、在SQL Server中使用 XML、使用ADO.NET、使用SMO编程管理数据库对象、高级T-SQL、Service Broker 异步应用程序平台、空间数据类型跨实例链接、数据库管理自动...
SQL server 2008
SQLServer的内置XML支持--vs2003,SQLServer的内置XML支持--vs2003
SQLServer2019-x64-CHS.part2.rar
笔记是本人学习SQLServer一段时间后重新整理出来的,适合有一些入门基础的人学习。 ├─01 安装及使用 │ SQLServer2005安装及使用.txt │ ├─02 常用函数 │ function.sql │ ├─03 建表、建库 │ create.sql ...
microsoft sql server 2008技术内幕 t-sql语言基础 全面介绍sql server 2008相关的t-sql语言 适合初学者
Microsoft SQL Server 2008 All-in-One Desk Reference for Dummies
本人的SQL server2017笔记。 笔记内容:基础增删改查、视图操作、索引操作、索引种类、多表、ml查询、Transact-SQL、存储过程、触发器、SQL server自动化服务、SQL server集成服务…
包含了SQL Server高级查询和T-SQL编程的所有主要知识点,都是平日一点点的积累,非常全面的
里面包含了sqlserver 2005和2008官网下载地址--sqlserver2008安装详解--以及错误及解决办法
sql-server-2017-integration-services-cookbook.pdf sql-server-2017-integration-services-cookbook.pdf sql-server-2017-integration-services-cookbook.pdf sql-server-2017-integration-services-cookbook.pdf ...