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

 

 

 

【赛迪网－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