SQL刷题记录

这篇博客详细记录了在NowCoder上的SQL刷题经历,重点讲解了MySQL中的字符串处理、OVER函数、CASE关键字的用法,以及在不同场景下IN和EXISTS的比较。内容涵盖字符串计数、窗口函数的应用、分组排名和条件判断,并提供了多个示例和代码演示。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

nowcoder SQL刷题记录:

https://www.nowcoder.com/ta/sql

复习题目:12, 18, 21, 44, 57, 88, 60, 61, 91, 88

60虽然做出来了,但是对over中的partition by 和order by 还是不够理解,可以尝试不用window function可以做出来吗

61虽然做出来了,但是学习子查询欠缺

91虽然做出来了,但是with语法并不会

88有大佬使用了一个NB的解法,这个题和76题并不同,76题是单个条目取中位数的数据,但是88是很多条目累计的和,如果使用76的方法肯定是行不通的

这个题要么取巧,要么还是好好复习

 

 

如果over中使用了order by 则之后的clause不用再使用order by语句,order by在window中使用会导致所有的行都有效

 

 

 

【Mysql】查找字符串'10,A,B' 中逗号','出现的次数cnt。

题目描述:查找字符串'10,A,B' 中逗号','出现的次数cnt。。
知识点总结:

统计字符串长度:
char_length('string')/char_length(column_name)
1、返回值为字符串string或者对应字段长度,长度的单位为字符,一个多字节字符(例如,汉字)算作一个单字符;
2、不管汉字还是数字或者是字母都算是一个字符;
3、任何编码下,多字节字符都算是一个字符;
参考资料来源:https://blog.youkuaiyun.com/iris_xuting/article/details/53763894

length('string')/length(column_name)
1、utf8字符集编码下,一个汉字是算三个字符,一个数字或字母算一个字符。
2、其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。

字符串替换:REPLACE(s,s1,s2),将字符串 s2 替代字符串 s 中的字符串 s1
MySQL常用函数:https://www.runoob.com/mysql/mysql-functions.html

代码:

1

select char_length("10,A,B")-char_length(replace("10,A,B",",",""))

 

 

 

select s.emp_no, a.max-min(s.salary) growth from (select emp_no, salary max from salaries where to_date='9999-01-01') a 
LEFT JOIN salaries s on a.emp_no=s.emp_no GROUP BY s.emp_no ORDER BY growth;

select s.emp_no, a.max-min(s.salary) growth from (select emp_no, salary max from salaries where to_date='9999-01-01') a LEFT JOIN salaries s on a.emp_no=s.emp_no GROUP BY s.emp_no ORDER BY growth
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'nowcoder.salaries.salary' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

对于下面的SQL,并不是不在group by 后边的字段出现在select中就会出现错误,但是必须是来自同一张表才可以

select c.name, count(f.film_id) from film f join film_category fc on f.film_id=fc.film_id

    join category c on c.category_id=fc.category_id

    join (

        select fc.category_id, count(fc.film_id) cnt from film f join film_category fc on f.film_id=fc.film_id

        group by fc.category_id

    ) r on r.category_id= fc.category_id

    where f.description like "%robot%" and r.cnt >= 5 group by c.category_id;

 

非group by 的列查询出来的结果是不确定的,不是想要的那行数据

group by 之后的hvaing也只能出现group by后的字段吗?> 1054 - Unknown column 'client_id' in 'IN/ALL/ANY subquery'

 

select job, count(id) cnt from grade

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'nowcoder.grade.job'; this is incompatible with sql_mode=only_full_group_by

 

 

 

MySQL over函数的用法

over不能单独使用,要和分析函数:rank(),dense_rank(),row_number()等一起使用。
其参数:over(partition by columnname1 order by columnname2)
含义:按columname1指定的字段进行分组排序,或者说按字段columnname1的值进行分组排序。
例如:employees表中,有两个部门的记录:department_id =10和20
select department_id,rank() over(partition by department_id order by salary) from employees就是指在部门10中进行薪水的排名,在部门20中进行薪水排名。如果是partition by org_id,则是在整个公司内进行排名。

以下是个人见解:

sql中的over函数和row_numbert()函数配合使用,可生成行号。可对某一列的值进行排序,对于相同值的数据行进行分组排序。

执行语句:select row_number() over(order by AID DESC) as rowid,* from bb

 

CASE关键字有两种使用方法

分别是‘简单case函数’和‘case搜索函数’

简单case函数

1

2

3

4

CASE sex

    WHEN '1' THEN '男'

    WHEN '2' THEN '女'

ELSE '其他' END

case搜索函数

1

2

3

4

CASE

    WHEN sex='1' THEN '男'

    WHEN sex='2' THEN '女'

ELSE '其他' END

 

分析说明

    1. 简单case函数是case搜索函数的真子集
      • 简单case函数的使用方法与一些高级语言(如:java)中的switch语句相似:CASE给定匹配字段,WHEN给出具体的字段值,如果匹配到后返回THEN值。
      • 简单case函数其实就是case搜索函数的‘=’逻辑的实现。case搜索函数可以实现简单case函数的所有功能,而简单case函数却不可实现case搜索函数的‘=’逻辑以外的功能。
    2. case函数匹配原则
      • case函数与switch的不同在于case仅返回第一个匹配到的结果,而switch则会在没有中断的情况下继续后面的判断,将会执行所有匹配的结果。
    3. case搜索函数比简单case函数更加灵活
      • case搜索函数与简单case函数相比的灵活之处在于可以在WHEN中书写判断式。

 

 

1、等值转换

SELECT user_name,( CASE WHEN sex = 0 THEN '男人' WHEN sex = 1 THEN '女人' ELSE '中性人' END ) AS 性别   FROM   imooc_goddess;

SELECT user_name,( CASE sex WHEN 0 THEN '男人' WHEN 1 THEN '女人' ELSE null END ) AS 性别  FROM   imooc_goddess;

 

 

 

 

 

2、范围转换

select user_name, (case when age BETWEEN 0 and 18 then '未成年' when age BETWEEN 18 and 30 then '成年' when age BETWEEN 30 and 50 then '中年人' else '老年人' end) as 年龄段
FROM imooc_goddess;

 select user_name,(case when age>0 and age<=18 then '未成年' when age >18 and age<=30 then '成年' when age >30 and age<=50 then '中年人' else '老年人' end) as 年龄段 FROM  imooc_goddess;

 

3、行转列

select name , (case when sub = 'english' then score else 0 end ) 'english' ,
(case when sub = 'maths' then score else 0 end) 'maths' ,
(case when sub = 'chinese' then score else 0 end) 'chinese'
FROM stu;

 

4、聚合函数使用case when

select name , sum((case when sub = 'english' then score else 0 end )) 'english' ,
sum((case when sub = 'maths' then score else 0 end)) 'maths' ,
sum((case when sub = 'chinese' then score else 0 end)) 'chinese'
FROM stu GROUP BY name ;

 

 

1.IF

  表达式:IF( expr1 , expr2 , expr3 )

  expr1条件,条件为true,则值是expr2 ,false,值就是expr3

2. IFNULL

  表达式:IFNULL( expr1 , expr2)

  在 expr1 的值不为 NULL的情况下都返回 expr1,否则返回 expr2

 

 

 

MySQL为查询结果添加序号

时间:2018-12-19

本文章向大家介绍MySQL为查询结果添加序号,主要包括MySQL为查询结果添加序号使用实例、应用技巧、基本知识点总结和需要注意事项,具有一定的参考价值,需要的朋友可以参考一下。

查询结果
现在的要求是:根据salary的值排名,并添加序号。
有三种添加序号的方式:

  1. 顺序排名
  2. 顺序排名,有相同的salary采取并列,保持排名不变
  3. 顺序排名,采取并列,且排名不变的salary仍然占用一个位置

一、不考虑并列( “:=”表示赋值)

select
s.emp_no,s.salary,
 @n:=@n+1 rank
from salaries s, (select @n:= 0) d
where s.to_date='9999-01-01' order by s.salary desc

查询结果
在这里插入图片描述
二、采取并列(需要添加一个字段来保存上一次的salary值进行比较,如果相同,排名不变;如果不同,排名+1)

select
s.emp_no,s.salary,
case
when @t=s.salary then @n
when @t:=s.salary then @n:=@n+1
end rank
from salaries s, (select @n:= 0,@t:= -1) d
where s.to_date='9999-01-01' order by s.salary desc

还有一种简便写法

SELECT emp_no,salary,
@rank := @rank + (@pre <> (@pre := salary)) Rank
FROM salaries, (SELECT @rank := 0, @pre := -1) INIT
WHERE to_date = '9999-01-01
order by salary

 

mysql分组排序加序号(不用存储过程,就简简单单sql语句哦)

 

一 建表

 

CREATE TABLE `my_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_code` varchar(255) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

 

二 模拟数据

 

INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('01', '001');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('01', '002');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('02', '001');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('01', '003');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('02', '002');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('03', '001');
INSERT INTO `my_test` (  `parent_code`, `code`) VALUES ('04', '001');

 

查询 结果如下:

三 不分组加序号

select  (@i := @i + 1) rownum,my_test.*  from    my_test , (SELECT  @i := 0) AS a  group  by   parent_code ,code ,id  order  by   parent_code 

结果如下:

解释一下 这个地方用了@i变量  刚开始的 让 @i=0   然后 每查询一条 让  @i+=1

四  分组 排序 加 序号了

刚开始的没 思路,就度娘了 ,有用 存储过程  创建临时表  插入临时表实现的,还有用存储过程游标实现,对于好久没动sql,而且之前也没写过mysql 查询的 淫来说 好复杂,

好囧 ,赶脚要再我女神面前丢人了,but 多谢上天眷顾,查看我女神聊天记录的时候,灵感来了,为什么不继续发掘下变量的作用呢 。

于是 再定义一个变量@pre_parent_code:='' 再存上一个 parent_code  ,只要  pre_parent_code不等于当前的parent_code  让 @i:=0 else  @i+=1 就ok了

 

select 
                        -- rownum  判断  @pre_parent_code是否和当前的parent_code一样 ,true:让 @i+=1 false:重置@i
                    
                        (@i := case  when  @pre_parent_code=parent_code then @i + 1 else 1 end )  rownum,  
                        
                        my_test.*,
                        --  设置 @pre_parent_code等于上一个 parent_code 
                         (@pre_parent_code:=parent_code)  
                         
                     from    my_test , 
                     
                     (SELECT  @i := 0, @pre_parent_code:='') AS a  
                     
                     group  by   parent_code ,code ,id  
                     
                     order  by   parent_code 

 

结果如下图

链接:https://www.cnblogs.com/CharlieLau/p/6737243.html

mysql实现over()开窗函数功能

Mysql实现开窗函数功能:

参考:http://stackoverflow.com/questions/3333665/rank-function-in-mysql

 

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');
---------------------------------------------
--不是做分区,只做排名---
SELECT    first_name,
          age,
          gender,
          @curRank := @curRank + 1 AS rank
FROM      person p, (SELECT @curRank := 0) r
ORDER BY  age;

 

--分区排名:按照性别分区,on条件相等的那个,按照>条件的字段age作为排名--
SELECT    a.first_name,
      a.age,
      a.gender,
        count(b.age)+1 as 'rank'
FROM  person a 
left join person b 
on a.age>b.age and a.gender=b.gender 
group by  a.first_name,
      a.age,
      a.gender
    ORDER BY a.gender,a.age;

MYSQL如何实现row_number()over()函数功能

一:创建表格

 

use data;
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

二:代码编写

 

set @rank:=0;
set @CI:=null;
select SId,CId,score,rank from(
select 
SId, CId,score,
@rank:=if(@CI=CId,@rank+1,1) as rank,
@CI:=CId
from data.sc
order by CId,score desc) as t1

mysql实现开窗函数、Mysql实现分析函数

目录

 


关键字:mysql实现开窗函数、Mysql实现分析函数、利用变量实现窗口函数

 

【Mysql5.7及以下版本】

适用范围:mysql5.7及以下版本,mysql8.0+ 可以直接使用窗口函数

注意,变量是从左到右顺序执行的

【测试数据】

 

-- 测试数据
CREATE TABLE `tem` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`str` char(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ;


INSERT INTO `test`.`tem`(`id`, `str`) VALUES (1, 'A');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (2, 'B');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (3, 'A');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (4, 'C');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (5, 'A');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (6, 'C');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (7, 'B');

 

【1】row_number() over(order by )

变量会最后再计算,所以是先排序好之后,才会开始计算@num

 

SELECT
    @num := @num+1 num,
    id,
    str
FROM
    tem, (SELECT @str := '', @num := 0) t1
ORDER BY
    str, id;

 

 

 

【2】实现分组排名效果(row_number() over(partition by order by ))

 

--变量方式
SELECT
    @num := IF(@str = str, @num + 1, 1) num,
    id,
    @str := str str
FROM
    tem, (SELECT @str := '', @num := 0) t1
ORDER BY
    str, id;

 

--子查询方式【取分组中前N行(排名前几名)】

 

 

 

 

mysql 相关子查询参考

select * from testGroup as a
where a.ID in (select  ID from testGroup b where a.UserID = b.UserID order by b.OrderID limit 2)
--或者
select * from testGroup a
where not exists (select 1 from testGroup b where a.UserID = b.UserID and a.OrderID > b.OrderID 
having count(1) >= 2)
--或者
select * from testGroup a
where (select count(1) from testGroup b where a.UserID = b.UserID and a.ID >= b.ID) <= 2
--没有唯一标识的表,可以用checksum来标识每行(MSSQL?)
select * from testGroup as a
where checksum(*) in (select top 2 checksum(*) from testGroup b where a.UserID = b.UserID order by b.OrderID)

 

mysql使用子查询实现

 

create table test1_1(id int auto_increment primary key,`subject` char(20),score int);
insert into test1_1 values(null,'语文',99),(null,'语文',98),(null,'语文',97);
insert into test1_1 values(null,'数学',89),(null,'数学',88),(null,'数学',87);
insert into test1_1 values(null,'英语',79),(null,'英语',78),(null,'英语',77);

-- 根据成绩,求出每个科目的前2名

select * from test1_1;

select * from test1_1 t1
where (select count(1) from test1_1 t2 where t1.subject=t2.subject and t2.score>=t1.score ) <=2;

-- 查询结果【左边】原表内容  【右边】需求结果,根据成绩,求出每个科目的前2名

 

 

 

 

 

【3】实现dense_rank() over(order by)

 

--变量会最后再计算,所以是先排序好之后,才会开始计算@num

select id,@num:=IF(@STR=STR,@num,@num+1) rn,@str:=str str
from tem t1,(select @str:='',@num:=0) t2 
order by str 
--

 

---case when 形式,但该方法在mysql5.5中,只支持非0数字排序生成,字符会有大问题(任意字符被case when 'a' then else end,都会走else)

--且,赋值语句等于0时也为假

 

--错误的方式
select id,str,
case when @str=str then @num
when @str:=str then @num:=@num+1 end as 'rn'
from tem t1,(select @str:='',@num:=1) t2 
order by str
  

  

 

--正确的方式
select id,str,
case when @str=str then @num
when @str:=str then @num:=@num+1 
else @num:=@num+1 end as 'rn'
from tem t1,(select @str:='',@num:=0) t2 
order by str

  

 

 

 

--关于数字的case when 验证

 

  

 

关于字符、字符串 的case when验证
  

  

 

 

 

【4】rank() over()

  

 

 

---------------------
测试数据代码大部分引用自:https://blog.youkuaiyun.com/mingqing6364/article/details/82621840 

链接:https://www.cnblogs.com/gered/p/10430829.html

 

 

窗口函数

窗口:记录集合
窗口函数:在满足某些条件的记录集合上执行的特殊函数,对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同,窗口大小都是固定的,称为静态窗口;有的函数则相反,不同的记录对应着不同的窗口,称为滑动窗口。

1. 窗口函数和普通聚合函数的区别:
①聚合函数是将多条记录聚合为一条;窗口函数是每条记录都会执行,有几条记录执行完还是几条。
②聚合函数也可以用于窗口函数。

2. 窗口函数的基本用法:
函数名 OVER 子句

over关键字用来指定函数执行的窗口范围,若后面括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行,窗口函数基于所有行进行计算;如果不为空,则支持以下4中语法来设置窗口。
①window_name:给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读;
②PARTITION BY 子句:窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行;
③ORDER BY子句:按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号;
④FRAME子句:FRAME是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用。

3. 按功能划分可将MySQL支持的窗口函数分为如下几类:
①序号函数:ROW_NUMBER()、RANK()、DENSE_RANK()
用途:显示分区中的当前行号
应用场景:查询每个学生的分数最高的前3门课程
ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY score)

mysql> SELECT *
    -> FROM(
    ->     SELECT stu_id,
    ->     ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY score DESC) AS score_
order,
    ->     lesson_id, score
    ->     FROM t_score) t
    -> WHERE score_order <= 3
    -> ;
+--------+-------------+-----------+-------+
| stu_id | score_order | lesson_id | score |
+--------+-------------+-----------+-------+
|      1 |           1 | L005      |    98 |
|      1 |           2 | L001      |    98 |
|      1 |           3 | L004      |    88 |
|      2 |           1 | L002      |    90 |
|      2 |           2 | L003      |    86 |
|      2 |           3 | L001      |    84 |
|      3 |           1 | L001      |   100 |
|      3 |           2 | L002      |    91 |
|      3 |           3 | L003      |    85 |
|      4 |           1 | L001      |    99 |
|      4 |           2 | L005      |    98 |
|      4 |           3 | L002      |    88 |
+--------+-------------+-----------+-------+

对于stu_id=1的同学,有两门课程的成绩均为98,序号随机排了1和2。但很多情况下二者应该是并列第一,则他的成绩为88的这门课的序号可能是第2名,也可能为第3名。
这时候,ROW_NUMBER()就不能满足需求,需要RANK()和DENSE_RANK()出场,它们和ROW_NUMBER()非常类似,只是在出现重复值时处理逻辑有所不同。

mysql> SELECT *
    -> FROM(
    ->     SELECT
    ->     ROW_NUMBER() OVER (PARTITION BY stu_id ORDER BY score DESC) AS score_order1,
    ->     RANK() OVER (PARTITION BY stu_id ORDER BY score DESC) AS score_order2,
    ->     DENSE_RANK() OVER (PARTITION BY stu_id ORDER BY score DESC) AS score_order3,
    ->     stu_id, lesson_id, score
    ->     FROM t_score) t
    -> WHERE stu_id = 1 AND score_order1 <= 3 AND score_order2 <= 3 AND score_order3 <= 3
    -> ;
+--------------+--------------+--------------+--------+-----------+-------+
| score_order1 | score_order2 | score_order3 | stu_id | lesson_id | score |
+--------------+--------------+--------------+--------+-----------+-------+
|            1 |            1 |            1 |      1 | L005      |    98 |
|            2 |            1 |            1 |      1 | L001      |    98 |
|            3 |            3 |            2 |      1 | L004      |    88 |
+--------------+--------------+--------------+--------+-----------+-------+

ROW_NUMBER():顺序排序——1、2、3
RANK():并列排序,跳过重复序号——1、1、3
DENSE_RANK():并列排序,不跳过重复序号——1、1、2

②分布函数:PERCENT_RANK()、CUME_DIST()
PERCENT_RANK()
用途:每行按照公式(rank-1) / (rows-1)进行计算。其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数
应用场景:不常用
给窗口指定别名:WINDOW w AS (PARTITION BY stu_id ORDER BY score)
rows = 5

mysql> SELECT
    -> RANK() OVER w AS rk,
    -> PERCENT_RANK() OVER w AS prk,
    -> stu_id, lesson_id, score
    -> FROM t_score
    -> WHERE stu_id = 1
    -> WINDOW w AS (PARTITION BY stu_id ORDER BY score)
    -> ;
+----+------+--------+-----------+-------+
| rk | prk  | stu_id | lesson_id | score |
+----+------+--------+-----------+-------+
|  1 |    0 |      1 | L003      |    79 |
|  2 | 0.25 |      1 | L002      |    86 |
|  3 |  0.5 |      1 | L004      |    88 |
|  4 | 0.75 |      1 | L005      |    98 |
|  4 | 0.75 |      1 | L001      |    98 |
+----+------+--------+-----------+-------+

CUME_DIST()
用途:分组内小于、等于当前rank值的行数 / 分组内总行数
应用场景:查询小于等于当前成绩(score)的比例
cd1:没有分区,则所有数据均为一组,总行数为8
cd2:按照lesson_id分成了两组,行数各为4

mysql> SELECT stu_id, lesson_id, score,
    -> CUME_DIST() OVER (ORDER BY score) AS cd1,
    -> CUME_DIST() OVER (PARTITION BY lesson_id ORDER BY score) AS cd2
    -> FROM t_score
    -> WHERE lesson_id IN ('L001','L002')
    -> ;
+--------+-----------+-------+-------+------+
| stu_id | lesson_id | score | cd1   | cd2  |
+--------+-----------+-------+-------+------+
|      2 | L001      |    84 | 0.125 | 0.25 |
|      1 | L001      |    98 |  0.75 |  0.5 |
|      4 | L001      |    99 | 0.875 | 0.75 |
|      3 | L001      |   100 |     1 |    1 |
|      1 | L002      |    86 |  0.25 | 0.25 |
|      4 | L002      |    88 | 0.375 |  0.5 |
|      2 | L002      |    90 |   0.5 | 0.75 |
|      3 | L002      |    91 | 0.625 |    1 |
+--------+-----------+-------+-------+------+

③前后函数:LAG(expr,n)、LEAD(expr,n)
用途:返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD(expr,n))的expr的值
应用场景:查询前1名同学的成绩和当前同学成绩的差值
内层SQL先通过LAG()函数得到前1名同学的成绩,外层SQL再将当前同学和前1名同学的成绩做差得到成绩差值diff。

mysql> SELECT stu_id, lesson_id, score, pre_score,
    -> score-pre_score AS diff
    -> FROM(
    ->     SELECT stu_id, lesson_id, score,
    ->     LAG(score,1) OVER w AS pre_score
    ->     FROM t_score
    ->     WHERE lesson_id IN ('L001','L002')
    ->     WINDOW w AS (PARTITION BY lesson_id ORDER BY score)) t
    -> ;
+--------+-----------+-------+-----------+------+
| stu_id | lesson_id | score | pre_score | diff |
+--------+-----------+-------+-----------+------+
|      2 | L001      |    84 |      NULL | NULL |
|      1 | L001      |    98 |        84 |   14 |
|      4 | L001      |    99 |        98 |    1 |
|      3 | L001      |   100 |        99 |    1 |
|      1 | L002      |    86 |      NULL | NULL |
|      4 | L002      |    88 |        86 |    2 |
|      2 | L002      |    90 |        88 |    2 |
|      3 | L002      |    91 |        90 |    1 |
+--------+-----------+-------+-----------+------+

④头尾函数:FIRST_VALUE(expr)、LAST_VALUE(expr)
用途:返回第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr))expr的值
应用场景:截止到当前成绩,按照日期排序查询第1个和最后1个同学的分数
添加新列:mysql> ALTER TABLE t_score ADD create_time DATE;
1
mysql> SELECT stu_id, lesson_id, score, create_time,
    -> FIRST_VALUE(score) OVER w AS first_score,
    -> LAST_VALUE(score) OVER w AS last_score
    -> FROM t_score
    -> WHERE lesson_id IN ('L001','L002')
    -> WINDOW w AS (PARTITION BY lesson_id ORDER BY create_time)
    -> ;
+--------+-----------+-------+-------------+-------------+------------+
| stu_id | lesson_id | score | create_time | first_score | last_score |
+--------+-----------+-------+-------------+-------------+------------+
|      3 | L001      |   100 | 2018-08-07  |         100 |        100 |
|      1 | L001      |    98 | 2018-08-08  |         100 |         98 |
|      2 | L001      |    84 | 2018-08-09  |         100 |         99 |
|      4 | L001      |    99 | 2018-08-09  |         100 |         99 |
|      3 | L002      |    91 | 2018-08-07  |          91 |         91 |
|      1 | L002      |    86 | 2018-08-08  |          91 |         86 |
|      2 | L002      |    90 | 2018-08-09  |          91 |         90 |
|      4 | L002      |    88 | 2018-08-10  |          91 |         88 |
+--------+-----------+-------+-------------+-------------+------------+

⑤其它函数:NTH_VALUE(expr, n)、NTILE(n)
NTH_VALUE(expr,n)
用途:返回窗口中第n个expr的值。expr可以是表达式,也可以是列名
应用场景:截止到当前成绩,显示每个同学的成绩中排名第2和第3的成绩的分数
mysql> SELECT stu_id, lesson_id, score,
    -> NTH_VALUE(score,2) OVER w AS second_score,
    -> NTH_VALUE(score,3) OVER w AS third_score
    -> FROM t_score
    -> WHERE stu_id IN (1,2)
    -> WINDOW w AS (PARTITION BY stu_id ORDER BY score)
    -> ;
+--------+-----------+-------+--------------+-------------+
| stu_id | lesson_id | score | second_score | third_score |
+--------+-----------+-------+--------------+-------------+
|      1 | L003      |    79 |         NULL |        NULL |
|      1 | L002      |    86 |           86 |        NULL |
|      1 | L004      |    88 |           86 |          88 |
|      1 | L001      |    98 |           86 |          88 |
|      1 | L005      |    98 |           86 |          88 |
|      2 | L004      |    75 |         NULL |        NULL |
|      2 | L005      |    77 |           77 |        NULL |
|      2 | L001      |    84 |           77 |          84 |
|      2 | L003      |    86 |           77 |          84 |
|      2 | L002      |    90 |           77 |          84 |
+--------+-----------+-------+--------------+-------------+

NTILE(n)
用途:将分区中的有序数据分为n个等级,记录等级数
应用场景:将每门课程按照成绩分成3组
mysql> SELECT
    -> NTILE(3) OVER w AS nf,
    -> stu_id, lesson_id, score
    -> FROM t_score
    -> WHERE lesson_id IN ('L001','L002')
    -> WINDOW w AS (PARTITION BY lesson_id ORDER BY score)
    -> ;
+------+--------+-----------+-------+
| nf   | stu_id | lesson_id | score |
+------+--------+-----------+-------+
|    1 |      2 | L001      |    84 |
|    1 |      1 | L001      |    98 |
|    2 |      4 | L001      |    99 |
|    3 |      3 | L001      |   100 |
|    1 |      1 | L002      |    86 |
|    1 |      4 | L002      |    88 |
|    2 |      2 | L002      |    90 |
|    3 |      3 | L002      |    91 |
+------+--------+-----------+-------+

NTILE(n)函数在数据分析中应用较多,比如由于数据量大,需要将数据平均分配到n个并行的进程分别计算,此时就可以用NTILE(n)对数据进行分组(由于记录数不一定被n整除,所以数据不一定完全平均),然后将不同桶号的数据再分配。

4. 聚合函数作为窗口函数:
用途:在窗口中每条记录动态地应用聚合函数(SUM()、AVG()、MAX()、MIN()、COUNT()),可以动态计算在指定的窗口内的各种聚合函数值
应用场景:截止到当前时间,查询stu_id=1的学生的累计分数、分数最高的科目、分数最低的科目
mysql> SELECT stu_id, lesson_id, score, create_time,
    -> SUM(score) OVER w AS score_sum,
    -> MAX(score) OVER w AS score_max,
    -> MIN(score) OVER w AS score_min
    -> FROM t_score
    -> WHERE stu_id = 1
    -> WINDOW w AS (PARTITION BY stu_id ORDER BY create_time)
    -> ;
+--------+-----------+-------+-------------+-----------+-----------+-----------+

| stu_id | lesson_id | score | create_time | score_sum | score_max | score_min |

+--------+-----------+-------+-------------+-----------+-----------+-----------+

|      1 | L001      |    98 | 2018-08-08  |       184 |        98 |        86 |

|      1 | L002      |    86 | 2018-08-08  |       184 |        98 |        86 |

|      1 | L003      |    79 | 2018-08-09  |       263 |        98 |        79 |

|      1 | L004      |    88 | 2018-08-10  |       449 |        98 |        79 |

|      1 | L005      |    98 | 2018-08-10  |       449 |        98 |        79 |

+--------+-----------+-------+-------------+-----------+-----------+-----------+

参考链接:http://www.cnblogs.com/DataArt/p/9961676.html

https://blog.youkuaiyun.com/weixin_39010770/article/details/87862407

 

MySQL中In与Exists的区别

什么是exists

exists表示存在,它常常和子查询配合使用,例如下面的SQL语句

SELECT * FROM `user` 
    WHERE exists (SELECT * FROM `order` WHERE user.id = order.user_id)

exists用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值TrueFalse

当子查询返回为真时,则外层查询语句将进行查询。
当子查询返回为假时,外层查询语句将不进行查询或者查询不出任何记录。

因此上面的SQL语句旨在搜索出所有下过单的会员。需要注意的是,当我们的子查询为 SELECT NULL 时,MYSQL仍然认为它是True

exists和in的区别和使用场景

是的,其实上面的例子,in这货也能完成,如下面SQL语句

SELECT * FROM `user`  
    WHERE id in (SELECT user_id FROM `order`)

那么!in和exists到底有啥区别那,要什么时候用in,什么时候用exists那?接下来阿北一一教你。

我们先记住口诀再说细节!“外层查询表小于子查询表,则用exists,外层查询表大于子查询表,则用in,如果外层和子查询表差不多,则爱用哪个用哪个。”

In关键字原理

SELECT * FROM `user`  
    WHERE id in (SELECT user_id FROM `order`)

in()语句只会执行一次,它查出order表中的所有user_id字段并且缓存起来,之后,检查user表的id是否和order表中的user_id相当,如果相等则加入结果期,直到遍历完user的所有记录。

in的查询过程类似于以下过程

$result = [];
$users = "SELECT * FROM `user`";
$orders = "SELECT user_id FROM `order`";
for($i = 0;$i < $users.length;$i++){
    for($j = 0;$j < $orders.length;$j++){
        // 此过程为内存操作,不涉及数据库查询。
        if($users[$i].id == $orders[$j].user_id){
            $result[] = $users[$i];
            break;
        }
    }
}

我想你已经看出来了,当order表数据很大的时候不适合用in,因为它最多会将order表数据全部遍历一次。

如:user表有10000条记录,order表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差.

再如:user表有10000条记录,order表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升.

exists关键字原理

SELECT * FROM `user` 
    WHERE exists (SELECT * FROM `order` WHERE user.id = order.user_id)

在这里,exists语句会执行user.length次,它并不会去缓存exists的结果集,因为这个结果集并不重要,你只需要返回真假即可。

exists的查询过程类似于以下过程

$result = [];
$users = "SELECT * FROM `user`";
for($i=0;$i<$users.length;$i++){
    if(exists($users[$i].id)){// 执行SELECT * FROM `order` WHERE user.id = order.user_id
        $result[] = $users[$i];
    }
}

你看到了吧,当order表比user表大很多的时候,使用exists是再恰当不过了,它没有那么多遍历操作,只需要再执行一次查询就行。

如:user表有10000条记录,order表有1000000条记录,那么exists()会执行10000次去判断user表中的id是否与order表中的user_id相等.

如:user表有10000条记录,order表有100000000条记录,那么exists()还是执行10000次,因为它只执行user.length次,可见B表数据越多,越适合exists()发挥效果.

但是:user表有10000条记录,order表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

因此我们只需要记住口诀:“外层查询表小于子查询表,则用exists,外层查询表大于子查询表,则用in,如果外层和子查询表差不多,则爱用哪个用哪个。”

 

MySQL中In与Exists的区别
1 例子
2 EXISTS和IN的介绍
2.1 exists
2.2 in
2.3 使用上的区别
3 EXISTS和IN的性能分析
4 总结
1 例子
有两个表需要关联查询,表的情况如下:

# 2759174行数据
SELECT COUNT(*) FROM tb_data t1;

# 7262行数据
SELECT COUNT(*) FROM tb_task t2;

# 执行时间为44.88s
SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);

# 执行时间为28.93s
SELECT SQL_NO_CACHE t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);

有些地方会说:如果两个表中一个表大,另一个是表小,那么IN适合于外表大而子查询表小的情况;EXISTS适合于外表小而子查询表大的情况。
但是我们根据上面的例子可以发现并不满足这个规律。 t1表有两百多万行数据,t2表只有7千行数据。它们关联关系为t1.task_id = t2.id,我在使用IN时,t2表是子查询表,并且是小表,按理来说在这种情况下使用IN应该是更加合理的方式。
然后实际情况是使用IN需要44.88s,使用EXISTS需要28.93s

2 EXISTS和IN的介绍
2.1 exists
exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录;反之,如果exists里的条件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为false
如下:

select * from user where exists (select 1);

对user表的记录逐条取出,由于子条件中的select 1永远能返回记录行,那么user表的所有记录都将被加入结果集,所以与select * from user;是一样的。

又如下:

select * from user where exists (select * from user where user_id = 0);

可以知道对user表进行loop时,检查条件语句(select * from user where user_id = 0),由于user_id永远不为0,所以条件语句永远返回空集,条件永远为false,那么user表的所有记录都将被丢弃。、

总结:如果A表有n条记录,那么exists查询就是将这n条记录逐条取出,然后判断n遍exists条件。

2.2 in
in查询相当于多个or条件的叠加,这个比较好理解,比如下面的查询:

select * from user where user_id in (1, 2, 3);

等效于

select * from user where user_id = 1 or user_id = 2 or user_id = 3;

总结:in查询就是先将子查询条件的记录全都查出来,假设结果集为B,共有m条记录,然后再将子查询条件的结果集分解成m个,再进行m次查询。

2.3 使用上的区别
in查询的子条件返回结果必须只有一个字段,例如

select * from user where user_id in (select id from B);

不能是

select * from user where user_id in (select id, age from B);

而exists就没有这个限制。

3 EXISTS和IN的性能分析
为了便于分析,我把实际上的例子简化一下。
实际:

SELECT t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
SELECT t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);

简化后:

SELECT * FROM A WHERE A.id IN (SELECT id FROM B);
SELECT * FROM A WHERE EXISTS (SELECT * from B WHERE B.id = A.id);

(1) in
假设B表的所有id为(1,2,3),查询1可以转换为:

SELECT * FROM A WHERE A.id = 1 OR A.id = 2 OR A.id = 3;

这里主要是用到了A的索引,B表如何对查询影响不大。
(2)exists
查询2可以转化以下伪代码:

for (i = 0; i < count(A); i++) {
    a = get_record(A, i); #从A表逐条获取记录
    if (B.id = a[id]) { #如果子条件成立
        result[] = a;
    }
}
return result;

这里主要用到了B表的索引,A表如何对查询的效率影响不大。

(3)实际情况
1)SELECT t1.id FROM tb_data t1 WHERE t1.task_id IN (SELECT t2.id FROM tb_task t2);
它使用的索引情况如下:

使用了t1(A)表索引

2)SELECT t1.id FROM tb_data t1 WHERE EXISTS (SELECT * FROM tb_task t2 WHERE t1.task_id = t2.id);

使用了t2(B)表索引

4 总结
《高性能MySQL》书上说,MySQL会把in的查询语句改成exists再去执行(实际上我们在没有索引情况下,他们的执行过程确实是一致的)

在《MySQL技术内幕:SQL编程》这本书中说:确实有很多DBA认为EXISTS比IN的执行效率更高,可能是当时优化器还不是很稳定和足够优秀,但是目前绝大数的情况下,IN和EXISTS都具有相同的执行计划。

1)IN查询在内部表和外部表上都可以使用到索引。
2)Exists查询仅在内部表上可以使用到索引。
3)当子查询结果集很大,而外部表较小的时候,Exists的Block Nested Loop(Block 嵌套循环)的作用开始显现,并弥补外部表无法用到索引的缺陷,查询效率会优于IN。
4)当子查询结果集较小,而外部表很大的时候,Exists的Block嵌套循环优化效果不明显,IN 的外表索引优势占主要作用,此时IN的查询效率会优于Exists。
5)表的规模不是看内部表和外部表,而是外部表和子查询结果集。

参考:
https://cloud.tencent.com/developer/article/1144244
https://cloud.tencent.com/developer/article/1144253


参考链接:https://blog.youkuaiyun.com/jinjiniao1/article/details/92666614

https://blog.youkuaiyun.com/lick4050312/article/details/4476333

https://segmentfault.com/a/1190000008709410

 

 

oracle in和exist的区别 not in 和not exist的区别

in 是把外表和内表作hash join,而exists是对外表作loop,每次loop再对内表进行查询。一般大家都认为exists比in语句的效率要高,这种说法其实是不准确的,这个是要区分环境的。

exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当 exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录,反之如果exists里的条件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为 false。

 

例如:

select * from user where exists (select 1);

对user表的记录逐条取出,由于子条件中的select 1永远能返回记录行,那么user表的所有记录都将被加入结果集,所以与 select * from user;是一样的

 

又如下

select * from user where exists (select * from user where userId = 0);

可以知道对user表进行loop时,检查条件语句(select * from user where userId = 0),由于userId永远不为0,所以条件语句永远返回空集,条件永远为false,那么user表的所有记录都将被丢弃

 

not exists与exists相反,也就是当exists条件有结果集返回时,loop到的记录将被丢弃,否则将loop到的记录加入结果集

总的来说,如果A表有n条记录,那么exists查询就是将这n条记录逐条取出,然后判断n遍exists条件 

 

 

in查询相当于多个or条件的叠加,这个比较好理解,比如下面的查询

select * from user where userId in (1, 2, 3);

等效于

select * from user where userId = 1 or userId = 2 or userId = 3;

not in与in相反,如下

select * from user where userId not in (1, 2, 3);

等效于

select * from user where userId != 1 and userId != 2 and userId != 3;

总的来说,in查询就是先将子查询条件的记录全都查出来,假设结果集为B,共有m条记录,然后在将子查询条件的结果集分解成m个,再进行m次查询

 

值得一提的是,in查询的子条件返回结果必须只有一个字段,例如

select * from user where userId in (select id from B);

而不能是

select * from user where userId in (select id, age from B);

而exists就没有这个限制

 

下面来考虑exists和in的性能:

对于以上两种情况,in是在内存里遍历比较,而exists需要查询数据库,所以当B表数据量较大时,exists效率优于in

考虑如下SQL语句

select * from A where exists (select * from B where B.id = A.id);

select * from A where A.id in (select id from B);

 

1、select * from A where exists (select * from B where B.id = A.id);

exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是其内查询语句的结果集空或者非空,空则返回false,非空则返回true。
它的查询过程类似于以下过程:

 

for ($i = 0; $i < count(A); $i++) {

  $a = get_record(A, $i); #从A表逐条获取记录

  if (B.id = $a[id]) #如果子条件成立

    $result[] = $a;

}

return $result;

 

当B表比A表数据大时适合使用exists(),因为它没有那么多遍历操作,只需要再执行一次查询就行。
如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。
如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果。
再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快。

结论:exists()适合B表比A表数据大的情况

 

 

2、select * from A where id in (select id from B);

in()只执行一次,它查出B表中的所有id字段并缓存起来。之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录。
 

它的查询过程类似于以下过程:

Array A=(select * from A);  

Array B=(select id from B);  

  

for(int i=0;i<a.length;i++) {   </a.length;i++) {  <>

   for(int j=0;j<b.length;j++) {   </b.length;j++) {  <>

      if(A[i].id==B[j].id) {  

         resultSet.add(A[i]);  

         break;  

      }  

   }  

}  

return resultSet;

 

可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次
如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差。
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升。

结论:in()适合B表比A表数据小的情况

 

当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用。

在插入记录前,需要检查这条记录是否已经存在,只有当记录不存在时才执行插入操作,可以通过使用 EXISTS 条件句防止插入重复记录。
insert into A (name,age) select name,age from B where not exists (select 1 from A where A.id=B.id);

    EXISTS与IN的使用效率的问题,通常情况下采用exists要比in效率高,因为IN不走索引。但要看实际情况具体使用:IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

 

 

下面再看not exists  not in

1、select * from A where not exists (select * from B where B.id = A.id);

2、select * from A where A.id not in (select id from B);

 

看查询1,还是和上面一样,用了B的索引;而对于查询2,可以转化成如下语句

select * from A where A.id != 1 and A.id != 2 and A.id != 3;

可以知道not in是个范围查询,这种!=的范围查询无法使用任何索引,等于说A表的每条记录,都要在B表里遍历一次,查看B表里是否存在这条记录

 

not in 和not exists:如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快,故not exists比not in效率高。 

 

in 与 =的区别 

select name from student where name in ('zhang','wang','li','zhao'); 

与 

select name from student where name='zhang' or name='li' or name='wang' or name='zhao' 

的结果是相同的。

 

 

在我们一般的观点中,总是认为使用EXISTS(或NOT EXISTS)通常将提高查询的效率,所以一般推荐使用exists来代替in。但实际情况是不是这个样子呢?我们分别在两种不同的优化器模式下用实际的例子来看一下:

 

SEIANG@seiang11g>create table wjq1 as select * from dba_objects;

Table created.

SEIANG@seiang11g>create table wjq2 as select * from dba_tables ;

Table created.

SEIANG@seiang11g>create index idx_object_name on wjq1(object_name);

Index created.

SEIANG@seiang11g>create index idx_table_name on wjq2(table_name);

Index created.

 

SEIANG@seiang11g>select count(*) from wjq1;

  COUNT(*)

----------

     86976

SEIANG@seiang11g>select count(*) from wjq2;

  COUNT(*)

----------

      2868

 

 

一、内查询结果集比较小,而外查询较大的时候的情况

 

1、在CBO模式下:

SEIANG@seiang11g>select * from wjq1 where object_name in (select table_name from wjq2 where table_name like 'M%');

 

815 rows selected.

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1638414738

 

---------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                |  1238 |   270K|   354   (1)| 00:00:05 |

|*  1 |  HASH JOIN RIGHT SEMI|                |  1238 |   270K|   354   (1)| 00:00:05 |

|*  2 |   INDEX RANGE SCAN   | IDX_TABLE_NAME |   772 | 13124 |     7   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL  | WJQ1           |  5503 |  1112K|   347   (1)| 00:00:05 |

---------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("OBJECT_NAME"="TABLE_NAME")

   2 - access("TABLE_NAME" LIKE 'M%')

       filter("TABLE_NAME" LIKE 'M%')

   3 - filter("OBJECT_NAME" LIKE 'M%')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

         17  recursive calls

          0  db block gets

       1462  consistent gets

       1256  physical reads

          0  redo size

      46140  bytes sent via SQL*Net to client

       1117  bytes received via SQL*Net from client

         56  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        815  rows processed

 

SEIANG@seiang11g>select * from wjq1 where exists (select 1 from wjq2 where wjq1.object_name=wjq2.table_name and wjq2.table_name like 'M%');

 

815 rows selected.

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1638414738

 

---------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                |  1238 |   270K|   354   (1)| 00:00:05 |

|*  1 |  HASH JOIN RIGHT SEMI|                |  1238 |   270K|   354   (1)| 00:00:05 |

|*  2 |   INDEX RANGE SCAN   | IDX_TABLE_NAME |   772 | 13124 |     7   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL  | WJQ1           |  5503 |  1112K|   347   (1)| 00:00:05 |

---------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("WJQ1"."OBJECT_NAME"="WJQ2"."TABLE_NAME")

   2 - access("WJQ2"."TABLE_NAME" LIKE 'M%')

       filter("WJQ2"."TABLE_NAME" LIKE 'M%')

   3 - filter("WJQ1"."OBJECT_NAME" LIKE 'M%')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

         13  recursive calls

          0  db block gets

       1462  consistent gets

       1242  physical reads

          0  redo size

      46140  bytes sent via SQL*Net to client

       1117  bytes received via SQL*Net from client

         56  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        815  rows processed

 

通过上面执行计划对比发现:
   在CBO模式下,我们可以看到这两者的执行计划完全相同,统计数据也相同。

 

 

我们再来看一下RBO模式下的情况,这种情况相对复杂一些。

 

 

2、在RBO模式下:

 

SEIANG@seiang11g>select /*+ rule*/ * from wjq1 where object_name in (select table_name from wjq2 where table_name like 'M%');

 

815 rows selected.

 

Elapsed: 00:00:00.01

 

Execution Plan

----------------------------------------------------------

Plan hash value: 144941173

 

--------------------------------------------------------

| Id  | Operation                    | Name            |

--------------------------------------------------------

|   0 | SELECT STATEMENT             |                 |

|   1 |  NESTED LOOPS                |                 |

|   2 |   NESTED LOOPS               |                 |

|   3 |    VIEW                      | VW_NSO_1        |

|   4 |     SORT UNIQUE              |                 |

|*  5 |      INDEX RANGE SCAN        | IDX_TABLE_NAME  |

|*  6 |    INDEX RANGE SCAN          | IDX_OBJECT_NAME |

|   7 |   TABLE ACCESS BY INDEX ROWID| WJQ1            |

--------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   5 - access("TABLE_NAME" LIKE 'M%')

       filter("TABLE_NAME" LIKE 'M%')

   6 - access("OBJECT_NAME"="TABLE_NAME")

 

Note

-----

   - rule based optimizer used (consider using cbo)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        698  consistent gets

          0  physical reads

          0  redo size

      55187  bytes sent via SQL*Net to client

       1117  bytes received via SQL*Net from client

         56  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

        815  rows processed

 

SEIANG@seiang11g>select /*+ rule*/ * from wjq1 where exists (select 1 from wjq2 where wjq1.object_name=wjq2.table_name and wjq2.table_name like 'M%');

 

815 rows selected.

 

Elapsed: 00:00:00.15

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3545670754

 

---------------------------------------------

| Id  | Operation          | Name           |

---------------------------------------------

|   0 | SELECT STATEMENT   |                |

|*  1 |  FILTER            |                |

|   2 |   TABLE ACCESS FULL| WJQ1           |

|*  3 |   INDEX RANGE SCAN | IDX_TABLE_NAME |

---------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter( EXISTS (SELECT 0 FROM "WJQ2" "WJQ2" WHERE

              "WJQ2"."TABLE_NAME"=:B1 AND "WJQ2"."TABLE_NAME" LIKE 'M%'))

   3 - access("WJQ2"."TABLE_NAME"=:B1)

       filter("WJQ2"."TABLE_NAME" LIKE 'M%')

 

Note

-----

   - rule based optimizer used (consider using cbo)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

      91002  consistent gets

       1242  physical reads

          0  redo size

      46140  bytes sent via SQL*Net to client

       1117  bytes received via SQL*Net from client

         56  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        815  rows processed

 

 通过上面两个执行计划的对比发现:
    
在这里,我们可以看到实际上,使用in效率比exists效率更高。我们可以这样来理解这种情况:
    
对于in,RBO优化器选择的内存查询的结果作为驱动表来进行nest loops连接,所以当内存查询的结果集比较小的时候,这个in的效率还是比较高的。
    
对于exists,RBO优化器则是利用外查询表的全表扫描结果集过滤内查询的结果集,当外查询的表比较大的时候,相对效率比较低。

 

 

二、内查询结果集比较大,而外查询较小的时候的情况

 

1、在CBO模式下:

 

SEIANG@seiang11g>select * from wjq2 where table_name in (select object_name from wjq1 where object_name like 'S%');

 

278 rows selected.

 

Elapsed: 00:00:00.03

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1807911610

 

--------------------------------------------------------------------------------------

| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                 |   278 |   164K|    55   (0)| 00:00:01 |

|*  1 |  HASH JOIN SEMI    |                 |   278 |   164K|    55   (0)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| WJQ2            |   278 |   146K|    31   (0)| 00:00:01 |

|*  3 |   INDEX RANGE SCAN | IDX_OBJECT_NAME |  4435 |   285K|    24   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("TABLE_NAME"="OBJECT_NAME")

   2 - filter("TABLE_NAME" LIKE 'S%')

   3 - access("OBJECT_NAME" LIKE 'S%')

       filter("OBJECT_NAME" LIKE 'S%')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

         67  recursive calls

          0  db block gets

        403  consistent gets

        446  physical reads

          0  redo size

      22852  bytes sent via SQL*Net to client

        721  bytes received via SQL*Net from client

         20  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        278  rows processed

 

SEIANG@seiang11g>

SEIANG@seiang11g>select * from wjq2 where exists (select 1 from wjq1 where wjq1.object_name=wjq2.table_name and wjq1.object_name like 'S%');

 

278 rows selected.

 

Elapsed: 00:00:00.02

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1807911610

 

--------------------------------------------------------------------------------------

| Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |                 |   278 |   164K|    55   (0)| 00:00:01 |

|*  1 |  HASH JOIN SEMI    |                 |   278 |   164K|    55   (0)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| WJQ2            |   278 |   146K|    31   (0)| 00:00:01 |

|*  3 |   INDEX RANGE SCAN | IDX_OBJECT_NAME |  4435 |   285K|    24   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("WJQ1"."OBJECT_NAME"="WJQ2"."TABLE_NAME")

   2 - filter("WJQ2"."TABLE_NAME" LIKE 'S%')

   3 - access("WJQ1"."OBJECT_NAME" LIKE 'S%')

       filter("WJQ1"."OBJECT_NAME" LIKE 'S%')

 

Note

-----

   - dynamic sampling used for this statement (level=2)

 

 

Statistics

----------------------------------------------------------

         13  recursive calls

          0  db block gets

        295  consistent gets

          2  physical reads

          0  redo size

      22852  bytes sent via SQL*Net to client

        721  bytes received via SQL*Net from client

         20  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        278  rows processed

 

通过上面两个执行计划的对比发现:
   
虽然他们的执行计划相同,但是使用exists比使用in的物理读和逻辑读明显小很多,所以使用exists效率更高一下

参考链接:https://www.cnblogs.com/UUUz/p/10118035.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值