SQLServer学习笔记系列7

一.写在前面的话

转眼又是周一,回想双休的日子,短暂而幸福,在阳光明媚的下午,可以自己做自己想做的任何事,惬意舒适,或读书,或运动,或音乐,当我们静下心来慢慢感受这些的时候,会突然发觉,原来生活是这么的幸福!有所求,有所感,就够啦!简简单单的生活其实就是最奢华的享受!忘记不开心的事,做自己生活的主导,摆脱烦恼,希望园子的朋友们,都能有一个好心情,不顺心的时候,出去走走,让情绪行走无边,放空自己!相信美好的事情终将发生!

二.视图

视图可以看作定义在SQL Server上的虚拟表.视图包含查询的一组结果集.常规视图本身并不存储实际的数据,而仅仅存储一个Select语句和所涉及表的metadata.利用视图,可以根据我们的需要,将多个表的数据进行组合,而且视图一旦建立,就一直存在,可以循环使用。

例如:假如我们要查找美国顾客的相关信息,那么就可以建立一个视图,每次只要查询美国顾客信息,只要根据视图名称查询就可以啦!同理,视图也需要先定义再查询。sql如下:

1 CREATE VIEW USA_cusomers
2 AS
3 (
4    SELECT * FROM
5    sales.customers
6    WHERE country=‘USA‘
7 )

定义完成以后,执行sql,那么命令就创建完成,然后就可以利用sql查询语句来查询美国的顾客信息。

1 SELECT custid,country FROM dbo.USA_cusomers;

执行结果如图所示:

同时我们在sql的对象资源管理器中——》视图中可以看到,已经添加了名称为dbo.USA_cusomers的视图,那么可以循环利用这个视图进行查询。

如果想删除这个视图的话,可以利用sql语句drop进行操作:

1 DROP VIEW dbo.USA_cusomers;

三.集合运算

集合预算主用用于对查询的结果进行操作。合集(Union)、交集(Intersect)、差集(Except),跟数学中的集合运算一样。

(1)合集,将所查询的两个或者多个结果集进行合并进行展示。

例如:我们要查询顾客表(sales.customers)和雇员表(hr.employees)里面所有的国家信息,那么就需要先查询出顾客表里面的所有国家,然后查询雇员表里面的所有国家,将两个结果集进行合并。sql如下:

1 SELECT country
2 FROM Sales.Customers
3 UNION ALL
4 SELECT country
5 FROM hr.Employees;

注意此处用的是union all,那么假如使用union结果又是什么了?

1 SELECT country
2 FROM Sales.Customers
3 UNION
4 SELECT country
5 FROM hr.Employees;

可以看到使用union all与union之间进行合集以后,结果集是不同的,其实这就说明了一点:

union all 不去重,包含所有的结果。

union 去重,显示的是去重以后的结果集。所以上述结果集显示21条,是去重后的结果。

(2)交集,将所查询的两个或者多个结果集进行交集展示。其中包含两个结果集中相同的部分。

同样我们将顾客表(sales.customers)和雇员表(hr.employees)里面所有的国家信息进行交集处理,看哪些国家既有顾客也有雇员。sql如下:

1 SELECT country
2 FROM Sales.Customers
3 intersect
4 SELECT country
5 FROM hr.Employees;

其中需要注意的是:intersect求交集,也是去重后的结果。

(3)差集,将所查询的两个或者多个结果集进行差集合并,找出其中一个集合在另一个集合中不存在的结果集。

例如:我们找出雇员表(hr.employees)里面的员工所在国家在顾客表(sales.customers)里面不存在的部分,也就是找出有顾客没有雇员的国家有哪些。sql如下:

1 SELECT country
2 FROM Sales.Customers
3 EXCEPT
4 SELECT country
5 FROM hr.Employees;

四.透视(pivot)

所谓的透视,也就是表的转置,在数据库操作中,有些时候我们遇到需要实现“行转列”的需求,统计每一阶段或者每一季度,每一星期的数量,可能数据库中存的数据格式是一行一行的,那么我们需要用一列展示具体的统计情况,这时候就需要用到透视(pivot)。

在这里我们首先创建一张表dbo.orders,同时往表里面插入一些数据。

 1 IF OBJECT_ID(‘dbo.orders‘,‘U‘) IS NOT NULL
 2 DROP TABLE dbo.orders;
 3 CREATE TABLE dbo.orders
 4 (
 5    orderid int NOT NULL  PRIMARY KEY,
 6    empid int NOT NULL,
 7    custid int NOT NULL,
 8    orderdate datetime,
 9    qty int
10 );
11
12 INSERT INTO dbo.orders(orderid,empid,custid,orderdate,qty)
13 VALUES (30001,3,1,‘20070802‘,10),
14        (30002,2,4,‘20070601‘,20),
15         (10001,4,5,‘20070802‘,30),
16         (20001,5,2,‘20070802‘,40),
17         (40001,3,2,‘20070802‘,50),
18         (30006,5,6,‘20070802‘,50),
19         (30008,4,8,‘20070802‘,60),
20         (60001,6,1,‘20070802‘,70)
1 SELECT * FROM dbo.orders

查询表数据有:

现在假如有一个需求,要求出每位顾客所消费的金额,根据常规想法,我们可以根据顾客id进行分组,然后用聚合函数sum求和,sql语句如下:

1 SELECT empid,SUM(qty) AS N‘顾客消费金额‘
2 FROM dbo.orders
3 GROUP BY empid;

但是我们现在想将顾客消费金额变成一列,也就是行转列,该怎么考虑了,在这里我们先用传统的方式,用到case when,然后根据case when条件进行一次求和。sql语句如下:

1 SELECT empid,
2 SUM(CASE when empid=2 THEN qty end) AS N‘2号顾客消费金额‘,
3 SUM(CASE when empid=3THEN qty end) AS N‘3号顾客消费金额‘,
4 SUM(CASE when empid=4 THEN qty end) AS N‘4号顾客消费金额‘,
5 SUM(CASE when empid=5 THEN qty end) AS N‘5号顾客消费金额‘,
6 SUM(CASE when empid=6 THEN qty end) AS N‘6号顾客消费金额‘
7 FROM dbo.orders
8 GROUP BY empid;

其执行结果如图:

从执行结果图可以看出,已经将顾客ID转换到一行显示,每位顾客的消费在列中都可以看到。但是这里我们采取新的一种方式,显得更好用,那就是pivot方式。只不过pivot帮我们做了很多的转换工作而已,这里我们只关注pivot如何用。对于上面的需求,我们可以这样使用pivot:

 1 SELECT empid,[1],[2],[4],[6],[8]
 2 FROM
 3 (
 4     --只返回pivot中用到的列
 5    SELECT empid,qty,custid
 6    FROM dbo.orders
 7 ) AS t
 8 PIVOT (
 9      SUM(t.qty) FOR t.custid IN ([1],[2],[4],[6],[8])--做列名称
10 ) AS P

希望各位大牛给出指导,不当之处虚心接受学习!谢谢!

时间: 04-12

SQLServer学习笔记系列7的相关文章

SQLServer学习笔记系列3

一.写在前面的话 今天又是双休啦!生活依然再继续,当你停下来的时候,或许会突然显得不自在.有时候,看到一种东西,你会发现原来在这个社会上,优秀的人很多,默默 吃苦努力奋斗的人也多!星期五早上按时上班,买好早餐,去公司餐厅吃早餐,我遇见了一个人,也许一次两次我还不会去注意,然而我每次在餐厅吃早餐, 都会遇到他,我看到他的是每一次都带着一碗白粥在那里吃,甚至连一点咸菜都没用,或许我这样的单身狗,不能理解有家室的痛楚,也许这是他的一种生活 方式,但我更多的看到的是他的一种吃苦,为了家人,为了将来的一种

SQLServer学习笔记系列6

一.写在前面的话 时间是我们每个人都特别熟悉的,但是到底它是什么,用什么来衡量,可能很多人会愣在那里.时间可以见证一切,也可以消磨一切,那些过往的点点滴滴可思可忆.回想往年清明节过后,在家乡的晚上总能听见阵阵的青蛙叫声,那是清脆的叫声,那是家乡的味道.时间一转眼,貌似那些日子已离我远去好久,在城市的喧嚣浮华中,找寻不到那种内心的宁静.感叹时间流逝的同时,怀念过去的点点滴滴.我想在繁华的都市中寻找一种安定的心情来学习,或许是一种不错的方式.学习才会让我们认清自己,找回自我,做内心的强者,不骄不躁,

SQLServer学习笔记系列5

一.写在前面的话 转眼又是一年清明节,话说“清明时节雨纷纷”,武汉的天气伴随着这个清明节下了一场暴雨,整个城市如海一样,朋友圈渗透着清明节武汉看海的节奏.今年又没有回老家祭祖,但是心里依然是怀念着那些亲人,虽说他们已离我们远去,然而那些血浓于水的亲情是一辈子无法忘记的,在心里深深的想念他们.生活继续,激情永恒!时刻保持着奋斗的节奏,为那些爱我们的和我爱的人,好好活着,做一个斗士,让我们都能够获得幸福!继续我们的学习吧!在这里首先分享海子的一首诗: 面对大河我无限惭愧, 我年华虚度,空有一身疲倦,

SQLServer学习笔记系列4

一.写在前面的话 好多天没有记录sql学习笔记了,要坚持下去,坚信每一点的进步都是为在积蓄力量.今天看到一幅图,特此分享出来. 通过这幅图,我看到的是每人站在自己的角度看问题,感受是不一样的,就如同学习知识一样,总觉得自己的理解才是最独特的,有时候适当把东西分享出 去,听听别人的见解,或许会让我们理解的更加深刻.换位思考,冷静处理,沉着淡定,不骄不躁,bug只不过生活的一部分,正因为有了bug才会让我们进 步,让我们去学习,去追寻问题的答案,一起努力,做一个快乐的程序猿.这个世界唯一不变的就是变

SQLServer学习笔记系列12

一.写在前面的话 这个sql学习系列,今天准备告一段落,虽然短短的十几篇文章,深刻感受到将学习的东西记录下来,是需要一种坚持! 这些东西只有反复的学习吸收,最终沉淀下来的才是属于自己的知识.也是提醒自己,今后的日子更要有计划,转眼又是7月份了, 时间不等人,岁月不饶人!坚持自己的计划,坚持向往的东西,踏实学习,因为自己不会的还太多,那些大牛还在学习, 我就更没理由逃避!也希望结交一些朋友,一起讨论技术,一起学习,一起进步!   二.触发器 触发器是一种特殊类型的存储过程,不能被显示的执行.它所监

SQLServer学习笔记系列11

一.写在前面的话 身体是革命的本钱,这句放在嘴边常说的话,还是拿出来一起共勉,提醒一起奋斗的同僚们,保证睡眠,注意身体!偶尔加个班,也许不曾感觉到身体发出的讯号,长期晚睡真心扛不住!自己也制定计划,敦促自己按照作息时间来上班学习生活!虽然自己每星期运动,还是觉得晚睡带来的身体压力,无法承受!程序猿兄弟们,我们早上起来的时候,可以看看自己的眼睛,如果充满血丝,那我们就该需要调养,好好休息了!没了身体,Coding的世界即将一去不复返!好好休息,保重身体!善待朋友,真爱家人,迎接每一天美丽的日出!共

SQLServer学习笔记系列10

一.写在前面的话 生活的路很长,还是要坚持走下去,自己选择的生活,就该让这样的生活放射精彩!我不奢求现在的积累,在将来能够收获多少,至少在以后的日子里回忆起来,我不曾放弃过,我坚持过,我不后悔!最近跟朋友谈到成长的话题,我们似乎摆脱不了被敦促的年纪,结婚.下一代是父母对我们的期盼,不同的年龄看问题的方式或许不同,真的到了他们那个年龄,我们才能真正体会那种心情,那种期盼!我只想告诉父母们,我们会努力的!走进未来的幸福,也是我们追求的,只是在这条路上,我们需要更大的勇气努力!你们好好保重,幸福会来的

SQLServer学习笔记系列8

一.写在前面的话 最近一直在思考一个问题,什么才能让我们不显得浮躁,真正的静下心来,用心去感受,用心去回答每个人的问题,用心去帮助别人.现实的生活,往往让我们显得精疲力尽,然后我们仔细想过没用,其实支持我们一直走下去的其实是心里的那一份希冀,希望未来会更好,用一个逼格比较高的词语,或许那就是理想.那么,有人会说,什么是理想,因为生活的重担已将我们所谓的理想磨灭了.但是,那又何妨了,而今,我已经被打败过了,我用曾经的飞蛾扑火,换来今天手心里握着的一把余温尚存的灰烬.值得庆幸的是,我依然没有忘记,这

MongoDB学习笔记系列

回到占占推荐博客索引 该来的总会来的,Ef,Redis,MVC甚至Sqlserver都有了自己的系列,MongoDB没有理由不去整理一下,这个系列都是平时在项目开发时总结出来的,希望可以为各位一些帮助和启发,文章中有对新技术的研究(Mongo驱动),对老技术的回顾(代码重构),还有对架构设计的阐述等(面向接口编程,对扩展开放,对修改关闭,所以出现了IMongoRepository接口). MongoDB学习笔记系列~目录 MongoDB学习笔记~环境搭建 (2015-03-30 10:34) M