读《程序员的SQL金典》[3]--表连接、子查询

  1. 云栖社区>
  2. 博客>
  3. 正文

读《程序员的SQL金典》[3]--表连接、子查询

技术小胖子 2017-11-09 20:43:00 浏览809
展开阅读全文

一、表连接-JOIN

1. 自连接实例

查询类型相同的订单信息。

SELECT O1 .*,O2.*
FROM T_Order O1 JOIN T_Order O2
ON O1 .FTypeId= O2.FTypeId AND O1.FID <O2. FId

Image(3)

二、子查询

子查询允许将sql语句查询结果作为结果集供其他语句使用。子查询可以简化复杂sql,但是使用不当会造成性能问题。

1.INSERT子查询

INSERT...SELECT....可以将SELECT查询结果导入到另外一个表中,并且可以在导入之前对数据进行处理。

例如:

INSERT INTO T_ReaderFavorite2( [FCategoryId],FReaderId )
SELECT FCategoryId ,(FReaderId+ 1)
FROM T_ReaderFavorite

2.UPDATE子查询

可以在UPDATE的where子句中使用子查询。

例如:

复制代码
UPDATE T_Book
SET FYearPublished =2014
WHERE
(
SELECT COUNT (*)
FROM T_Book b2 WHERE T_Book .FCategoryId= b2.FCategoryId
)>3
复制代码

3.DELETE子查询

复制代码
DELETE FROM T_ReaderFavorite
WHERE
(
SELECT COUNT (*) FROM T_ReaderFavorite T WHERE T. FCategoryId=T_ReaderFavorite .FCategoryId
)>=5
复制代码

三、NULL

我们都知道,NULL在数据库中表示某个字段的值为未知。

以下面的数据为例:

Image(4)

1.NULL与比较运算符

在比较运算符运算时,运算结果仍未NULL,因为DBMS认为无法确定NULL是否在某个取值范围之内。例如:

SELECT * FROM T_Employee WHERE FSalary< 5000 OR FSalary>=5000

Image(5)

2.NULL与计算字段

如果NULL值出现在任何计算字段中,那么运算结果都为NULL。

可以根据具体业务需求进行处理,例如使用IS NOT NULL进行过滤,或者将Null处理成其他值。

例如:

SELECT FSalary /1000 FROM T_Employee WHERE FSalary IS NOT NULL
SELECT (CASE WHEN FSalary IS NULL THEN 0 ELSE FSalary END)/1000 FROM T_Employee

3.NULL与字符串运算

如果NULL与字符串进行计算,那么结果也都是NULL.

SELECT 'dear ' +FName FROM T_Employee

Image(6)

4.NULL与函数

NULL值出现在普通函数时,结果还是NULL; 但当NULL值出现在聚合函数时,NULL值将被忽略。

SELECT FSalary +1000 FROM T_Employee

Image(7)

SELECT Count (Fid), COUNT(FSalary ) FROM T_Employee

Image(8)

四、开窗函数

1. OVER()

开窗函数可以对结果集进行聚合计算,但和平时用的聚合函数有些差别。看个实际例子:

想查询工资低于5k的员工姓名和年龄,并且每行都要展示符合条件人群总数。

SELECT FName ,FAGE , ( SELECT COUNT (*)FROM T_Person WHERE FSALARY< 5000)
FROM T_Person
WHERE FSALARY <5000

Image(9)

通过子查询方式实现的想要的结果,但是使用开窗函数可以更加简单。

开创函数格式:函数(列) OVER()。

==》

SELECT FName , FAGE , COUNT(*) OVER()
FROM T_Person
WHERE FSALARY <5000

2.PARTITION BY()

开窗函数OVER后面括号中可以使用PARTITION BY子句进行行的分区来进行聚合运算,而且可以创建多个分区。这些分区主要是用来进行聚合运算的,不会影响结果集。

COUNT (*) OVER (PARTITION BY FCITY )表示按照FCITY字段进行分区,并且返回当前行所属的组的聚合计算结果。

实例:

SELECT FCITY ,FAGE, COUNT(*) OVER(PARTITION BY FCITY) AS NUM1 ,COUNT(*) OVER( PARTITION BY FAGE ) AS NUM2
FROM T_PERSON
ORDER BY FAGE DESC

Image(10)

3.高级开窗函数

①计算排名

ROW_NUMBER()计算一行在结果集中的行号,可以当作是唯一的排名。

RANK()和DENSE_RANK()函数都是用来计算排名的,不同的是当名次重复时的处理方式。

如:

复制代码
SELECT FName ,FSalary,
ROW_NUMBER() OVER(ORDER BY FSalary DESC ) ,
RANK() OVER(ORDER BY FSalary DESC ),
DENSE_RANK() OVER(ORDER BY FSalary DESC )
FROM T_Person
复制代码

Image(11)

②NTILE(num)分区

NTILE()函数用来将结果集中的行按照某个列的值进行平均分割,然后返回当前行所在的区域编号。NTILE()函数接受一个整数类型的值,这个值表示把结果集分割成的份数。注意必须在NTILE()函数后的OVER()子句中使用ORDER BY指定排序。

SELECT FName , FSalary ,FAge,

NTILE(3 ) OVER (ORDER BY FSalary )
FROM T_Person ;

Image(12)


    本文转自 陈敬(Cathy) 博客园博客,原文链接:http://www.cnblogs.com/janes/p/3574060.html,如需转载请自行联系原作者


网友评论

登录后评论
0/500
评论
技术小胖子
+ 关注