教你快速掌握SQL语句各种写法的效率问题

最新推荐文章于 2024-09-24 11:45:07 发布
转载 最新推荐文章于 2024-09-24 11:45:07 发布 · 1.5k 阅读 · 1
标签
#sql #insert #null #sql server #table
#pivot
本文探讨了SQL中多种操作的效率对比，包括数据插入、变量赋值、条件判断等，并介绍了高级功能如PIVOT和UNPIVOT的使用方法。

 

 

 

 

【赛迪网－IT技术报道】问题1：一次插入多条数据时下面这两种方法,哪种方法效率高?

CREATE TABLE tb(ID int, 名称 NVARCHAR(30), 备注 NVARCHAR(1000))

INSERT tb SELECT 1,'DDD',1

UNION ALL SELECT 1,'5100','D'

UNION ALL SELECT 1,'5200','E'

也可以这样写:

CREATE TABLE tb1(ID int, 名称 NVARCHAR(30), 备注 NVARCHAR(1000))

INSERT TB1 (ID,名称,备注)VALUES(1,'DDD',1)

INSERT TB1 (ID,名称,备注)VALUES(1,'5100','D')

INSERT TB1 (ID,名称,备注)VALUES(1,'5200','E')

解答：

第1种好一些, 但也得有个量的控制, 因为第1种的union all是作为一个语句整体, 查询优化器会尝试做优化, 同时, 也要先算出这个结果再插入的。

问题2：

赋值时:

SELECT @a=N'aa'

SET @a=N'aa'

上面两种方法,哪种方法效率高?

解答：

如果是单个赋值, 没有什么好比较的话.

不过, 如果是为多个变量赋值, 经测试， SELECT 一次性赋值, 比用SET 逐个赋值效率好..

问题3：取前几条数据时

set ROWCOUNT 2 select * from tb order by fd

select Top 2 * from tb order by fd

上面两种方法,哪种方法效率高?

答：

SET ROWCOUNT和TOP 是一样的, 包括执行的计划等都是一样的

问题4：条件判断时:

where 0<(select count(*) from tb where ……）

where exists(select * from tb where ……）

上面两种方法,哪种方法效率高?

答：

这个一般是exists快, 当然, 具体还要看你后面的子查询的条件, 是否会引用外层查询中的对象的列.

exists检查到有值就返回, 而且不返回结果集, count需要统计出所有满足条件的, 再返回一个结果集, 所以一般情况下exists快.

问题5：

(5)NULLIF的使用----->同理它的反函数ISNULL的使用

update tb set fd=case when fd=1 then null else fd end

update tb set fd=nullif(fd,1)

上面两种方法,哪种方法效率高?

答：

应该是一样的

问题6：从字符串中取子字符串时

substring('abcdefg',1,3)

left('abcderg',3)_

上面两种方法,哪种方法效率高?

答：

基本上是一样的

问题7：EXCEPT和Not in的区别?

答：

except会去重复, not in 不会(除非你在select中显式指定)

except用于比较的列是所有列, 除非写子查询限制列, not in 没有这种情况

问题8：INTERSECT和UNION的区别?

答：intersect是两个查询都有的非重复值(交集), union是两个查询结果的所有不重复值(并集)

 

通过两个例子讲解PIVOT/UNPIVOT的用法

 

 

 

发布时间：2008.03.13 04:58     来源：赛迪网    作者：ChenJaYi

 

【赛迪网－IT技术报道】使用过SQL Server 2000的人都知道，要想实现行列转换,必须综合利用聚合函数和动态SQL，具体实现起来需要一定的技巧，而在SQL Server 2005中，使用新引进的关键字PIVOT/UNPIVOT，则可以很容易的实现行列转换的需求。

在本文中我们将通过两个简单的例子详细讲解PIVOT/UNPIVOT的用法。

PIVOT的用法：

首先创建测试表，然后插入测试数据 

create table test(id int,name varchar(20),quarter int,profile int) 
insert into test values(1,'a',1,1000)
insert into test values(1,'a',2,2000)
insert into test values(1,'a',3,4000)
insert into test values(1,'a',4,5000)
insert into test values(2,'b',1,3000)
insert into test values(2,'b',2,3500)
insert into test values(2,'b',3,4200)
insert into test values(2,'b',4,5500)
 
select * from test
id name quarter profile
----------- -------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500
 
(8 row(s) affected)
 
使用PIVOT将四个季度的利润转换成横向显示:
 
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
 
id name 一季度 二季度 三季度 四季度
-------- --------- ----------- -------- ------- -------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
 
(2 row(s) affected)

UNPIVOT的用法： 

首先建立测试表，然后插入测试数据
 
drop table test
 
create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
 
insert into test values(1,'a',1000,2000,4000,5000)
insert into test values(2,'b',3000,3500,4200,5500)
 
 
select * from test
 
id name Q1 Q2 Q3 Q4
-------- ------- --------- --------- -------- --------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
 
(2 row(s) affected)
 
使用UNPIVOT，将同一行中四个季度的列数据转换成四行数据：
 
select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
) 
as unpvt
 
id name quarter profile
----------- ----------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500
 
(8 row(s) affected)

 

用一个实例讲解GROUP BY CEIL的使用方法

 

 

 

发布时间：2008.01.31 05:07     来源：赛迪网    作者：孙诗涵

 

GROUP BY CEIL的使用方法： 

SQL> WITH A AS (SELECT 'A' CD FROM DUAL
2 UNION
3 SELECT 'B' CD FROM DUAL
4 UNION
5 SELECT 'C' CD FROM DUAL
6 UNION
7 SELECT 'D' CD FROM DUAL
8 UNION
9 SELECT 'E' CD FROM DUAL
10 UNION
11 SELECT 'F' CD FROM DUAL
12 UNION
13 SELECT 'G' CD FROM DUAL
14 UNION
15 SELECT 'H' CD FROM DUAL
16 UNION
17 SELECT 'I' CD FROM DUAL
18 )
19 select max(decode(mod(rownum, 5), 1, CD, null)) ID1,
20 max(decode(mod(rownum, 5), 2, CD, null)) ID2,
21 max(decode(mod(rownum, 5), 3, CD, null)) ID3,
22 max(decode(mod(rownum, 5), 4, CD, null)) ID4,
23 max(decode(mod(rownum, 5), 0, CD, null)) ID5
24 from a
25 group by ceil(rownum / 5)
26 ;
 
ID1 ID2 ID3 ID4 ID5
--- --- --- --- ---
A B C D E
F G H I 
 
 
例二：
 
with a as (select '01' ym from dual
union
select '02' ym from dual
union
select '03' ym from dual
union
select '04' ym from dual
union
select '05' ym from dual
union
select '06' ym from dual
union
select '07' ym from dual
union
select '08' ym from dual
union
select '09' ym from dual
union
select '10' ym from dual
union
select '11' ym from dual
union
select '12' ym from dual
)
select 
max(decode(mod(rownum, 6), 1, ym, null)) ID1,
max(decode(mod(rownum, 6), 2, ym, null)) ID2,
max(decode(mod(rownum, 6), 3, ym, null)) ID3,
max(decode(mod(rownum, 6), 4, ym, null)) ID4,
max(decode(mod(rownum, 6), 5, ym, null)) ID5,
max(decode(mod(rownum, 6), 0, ym, null)) ID6
from a
group by ceil(rownum / 6)
 
ID1 ID2 ID3 ID4 ID5 ID6
--- --- --- --- --- ---
01 02 03 04 05 06
07 08 09 10 11 12
Python实用技巧：比普通的赋值方式效率更高的二元赋值
web开发与Python
10-19 1391
python支持类似于a += 3这种二元表达式。比如： 1 a += 3 -> a = a + 3 2 a -= 3 -> a = a - 3 3 a *= 3 -> a = a * 3 4 ... 在python中的某些情况下，这种二元赋值表达式可能比普通的赋值方式效率更高些。原因有二： 二元赋值表达式中，a可能会是一个表达式，它只需计算评估一次，而a = a + 3...
SQL Server快速生成SQL增删改查语句
思想上移，行动下移
07-24 4303
你还在手敲代码生成SQL语句吗？你还在为因为马虎出错的SQL语句而感到无语吗？你还在为不知怎样表达复杂的SQL语句而纠结吗？如果你的回答为“是”，那你就OUT啦，快来试试应用SQL Server资源管理器快速生成SQL语句吧。
sql的行转列(PIVOT)与列转行(UNPIVOT)
weixin_30858241的博客
02-23 2263
在做数据统计的时候，行转列，列转行是经常碰到的问题。case when方式太麻烦了，而且可扩展性不强，可以使用 PIVOT，UNPIVOT比较快速实现行转列，列转行，而且可扩展性强 一、行转列 1、测试数据准备 CREATE TABLE [StudentScores] ( [UserName] NVARCHAR(20), ...
itpub论坛回复: 直接赋值与select from dual的效率对比
cong870624的博客
05-24 988
问题: (MountLion ) 直接赋值与select from dual的效率对比 在pl/sql中，可以直接给变量赋值，也可以使用select ... into var from dual，这两种方式效率有差别吗？ ...
oracle 11g新特性，UNPIVOT 性能测试
老农民挖数据
08-29 3521
根据业务需要，我们有900列转为3列900行数据的需求。 亲们，你们测试过piovt与 unpivot的性能吗？确定没有bug吗？ 等测试完后我再去Metalink寻找一下。 下面是我测试的性能。 --200 列  SELECT substr(dt.y,1,INSTR(dt.y,',')-1) x,substr(dt.y,INSTR(dt.y,',')+1) y,dt.z FROM
explain分析sql
徐哈哈的博客
09-11 607
性能分析explain MySql Query Optimizer是MySql中专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的系统信息，为客户端请求的Query提供他认为最优的执行计划(系统认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间) MySQL常见瓶颈 CPU: CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候 IO: 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候 服务器硬件的性能瓶颈：top，free, iostat和v..
提高SQL语句执行效率的8个方法
万猫学社
11-27 3632
索引是数据库中用于提高数据检索速度的数据结构。它可以帮助快速定位到表中特定的行，合理使用索引能够显著提高查询效率。全表扫描是指数据库需要检查表中的每一行数据来找到查询结果，这是一种非常低效的查询方式。我们应该尽量避免全表扫描。JOIN是SQL中的一种操作，它可以将两个或多个表的数据组合在一起。但是如果JOIN操作涉及的数据量过大，那么它可能会导致查询效率降低。分页查询是指将查询结果分成多个部分返回，每个部分包含一定数量的记录。分页查询可以减少单次查询返回的数据量，提高查询效率。
20种sql语句优化方案
最新发布
weixin_45836587的博客
09-24 4730
大家写sql语句中，是不是常有死锁、超时等问题出现，那就说明你的索引、查询语句规范并不完美。以下是小主整列20种sql语句优化方案，可以让你的sql语句，查询删除更加快速。当然看起来更加容易理解！
码匠 × OpenAI ：快速生成 SQL 语句，提升开发效率！
weixin_39545889的博客
02-24 1485
通过使用 OpenAI，可以快速生成 SQL 语句，无需具备 SQL 编程的经验，节省了编写 SQL 语句的时间和精力。此外，与手动编写 SQL 语句相比，使用 OpenAI 生成的 SQL 语句也相对更准确和稳定，可以帮助减少错误和漏洞的出现。这为码匠用户提供了一个更加高效和安全的数据处理方式，使用户可以更加专注于页面搭建和应用开发。
每日学习心得：SQL查询表的行列转换/小计/统计（with rollup，with cube，pivot解析）...
weixin_33898876的博客
08-23 401
原文 http://www.cnblogs.com/Olive116/p/3271706.html 2013-8-20 1.    SQL查询表的行列转换/小计/统计（with  rollup，with cube，pivot解析） 在实际的项目开发中有很多项目都会有报表模块，今天就通过一个小的SQL查询统计来讲解一下实际开发中比较常用的行列转换/小计/统计等报表统计相关的常用知识点。 ...
自从oracle有了PIVOT和UNPIVOT 减少了多少工作量
F2TP的专栏
03-24 424
绑定变量赋值
dnil27295的博客
07-19 171
之前一直有个疑问：当where条件中中使用 绑定变量，该怎么给变量赋值。如 where n1:= v1;sql语句的执行过程中 根本没机会输入v1值。在程序的最开始，我们可以定义变量v1 并且给v1赋值 非常简单！ 转载于:https://www.cnblogs.com/iyoume2008/p/4659873.html...
SQL Server SQL性能优化之--pivot行列转换减少扫描计数优化查询语句
weixin_34221073的博客
10-06 327
原文出处：http://www.cnblogs.com/wy123/p/5933734.html     先看常用的一种表结构设计方式：   那么可能会遇到一种典型的查询方式，主子表关联，查询子表中的某些（或者全部）Key点对应的Value，横向显示（也即以行的方式显示）   这种查询方式很明显的一个却显示多次对字表查询（暂时抛开索引）   相比这种查询方式很多人都遇...
SQL中PIVOT的用法
热门推荐
weixin_43715545的博客
10-19 2万+
PIVOT 提供的语法比一系列复杂的 SELECT…CASE 语句中所指定的语法更简单和更具可读性。 以下是带批注的 PIVOT 语法： SELECT <非透视的列>, [第一个透视的列] AS <列名称>, [第二个透视的列] AS <列名称>, ... [最后一个透视的列] AS <列名称>, FROM (...
SQLServer行列转换PIVOT函数中聚合函数的使用意义及选择
Wikey_Zhang的博客
08-07 1万+
PIVOT函数中必须要包含一个聚合函数，聚合函数分别是：COUNT、SUM、MAX、MIN、AVG，针对不同的使用场景，该选择哪个聚合函数，意义都是不一样的，下面还是举例说明吧！ 我建了如下一张销售表并且插入一下测试值： CREATE TABLE SalesOrder ( 门店 VARCHAR(50), 商品类型 VARCHAR(50), 销售额 INT ) INSERT INTO Sa
SQL Server行转列
weixin_33682790的博客
10-09 1094
语法： PIVOT用于将列值旋转为列名（即行转列），在SQL Server2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法： table_source PIVOT( 聚合函数（value_column） FOR pivot_column IN(<column_list&...
GROUP BY CEIL 的用法
cuanmo1272的博客
11-17 274
SQL> WITH A AS (SELECT 'A' CD FROM DUAL2 UNION3 SELECT 'B' CD FROM DUAL4 UNION5 SELECT 'C' CD FROM DUAL6 UNION7...
set filter to的使用方法；sql语句中&amp; 宏引用的使用；
weixin_41721560的博客
06-16 1343
InteractiveChange事件：当用户用键盘或鼠标改变对象的值时发生。set filter to 后接逻辑表达式 例：c_zd=alltrim(thisform.combo1.value) c_val=alltrim(thisform.text1.value) set filter to &amp;c_zd&gt;=c_val &amp;&amp;text1框作为字符型set...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值