`
jiagyao
  • 浏览: 94997 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

SQL Server学习笔记7之--联接与集合操作

阅读更多
说到联接与集合操作,其实它们就是对表的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
*/
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics