第二个参数指定最多返回行数,–处理的数据

–行列转换 行转列DROP TABLE t,change,目的是将查询的结果提供给外层的SQL语句,查看与CLARK同部门的员工,–2,(效率最高),第一个参数指定返回的第一行在所有数据中的位置,第二个参数指定最多返回行数,–处理的数据,SELECT ‘a’

图片 2

核心提示: –行列转换 行转列DROP TABLE t_change_lc;CREATE TABLE
t_change_lc (card_code VARCHAR2

为方便测试,创建表emp和表dept。

MySql:

select * from TABLE_NAME limit  A,  B;

解释:

A,查询起点

B,你需要的行数

MySql:

–行列转换 行转列 DROP TABLE t_change_lc; CREATE TABLE
t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);

图片 1

Oracle:

–1:无ORDER BY排序的写法。(效率最高)
–(经过测试,此方法成本最低,只嵌套一层,速度最快!即使查询的数据量再大,也几乎不受影响,速度依然!)

SELECT *

  FROM (SELECT ROWNUM AS rowno, t.*

          FROM emp
t

         WHERE hire_date BETWEEN TO_DATE (‘20060501’, ‘yyyymmdd’)

                             AND TO_DATE (‘20060731’, ‘yyyymmdd’)

           AND ROWNUM <= 20) table_alias

 WHERE table_alias.rowno >= 10;

–2:有ORDER BY排序的写法。(效率最高)
–(经过测试,此方法随着查询范围的扩大,速度也会越来越慢哦!)

SELECT *

  FROM (SELECT tt.*, ROWNUM AS rowno

          FROM (  SELECT
t.*

                    FROM emp
t

                   WHERE hire_date BETWEEN TO_DATE (‘20060501’, ‘yyyymmdd’)

                                       AND TO_DATE (‘20060731’, ‘yyyymmdd’)

                ORDER BY create_time DESC, emp_no) tt

         WHERE ROWNUM <= 20) table_alias

 WHERE table_alias.rowno >= 10;

 

 

=================================================================================

=======================垃圾但又似乎很常用的分页写法==========================

=================================================================================

–3:无ORDER BY排序的写法。(建议使用方法1代替)
–(此方法随着查询数据量的扩张,速度会越来越慢哦!)

SELECT *

  FROM (SELECT ROWNUM AS rowno, t.*

          FROM k_task
t

         WHERE flight_date BETWEEN TO_DATE (‘20060501’, ‘yyyymmdd’)

                               AND TO_DATE (‘20060731’, ‘yyyymmdd’)) table_alias

 WHERE table_alias.rowno <= 20 AND table_alias.rowno >= 10;

–TABLE_ALIAS.ROWNO  between 10 and
100;

–4:有ORDER BY排序的写法.(建议使用方法2代替)
–(此方法随着查询范围的扩大,速度会越来越慢哦!)

SELECT *

  FROM (SELECT tt.*, ROWNUM AS rowno

          FROM ( 
SELECT *

                    FROM k_task
t

                   WHERE flight_date BETWEEN TO_DATE (‘20060501’, ‘yyyymmdd’)

                                         AND TO_DATE (‘20060531’, ‘yyyymmdd’)

                ORDER BY fact_up_time, flight_no) tt) table_alias

 WHERE table_alias.rowno BETWEEN 10 AND 20;

–5另类语法。(有ORDER BY写法)
–(语法风格与传统的SQL语法不同,不方便阅读与理解,为规范与统一标准,不推荐使用。)
WITH partdata AS

     (

        SELECT ROWNUM AS rowno, tt.*

          FROM ( 
SELECT *

                    FROM k_task
t

                   WHERE flight_date BETWEEN TO_DATE (‘20060501’, ‘yyyymmdd’)

                                         AND TO_DATE (‘20060531’, ‘yyyymmdd’)

                ORDER BY fact_up_time, flight_no) tt

         WHERE ROWNUM <= 20)

SELECT *

  FROM partdata

 WHERE rowno >= 10;

 

–6另类语法 。(无ORDER BY写法)

WITH partdata AS

     (

        SELECT ROWNUM AS rowno, t.*

          FROM k_task
t

         WHERE flight_date BETWEEN TO_DATE (‘20060501’, ‘yyyymmdd’)

                               AND TO_DATE (‘20060531’, ‘yyyymmdd’)

           AND ROWNUM <= 20)

SELECT *

  FROM partdata

 WHERE rowno >= 10;

 

 

 

yangtingkun分析:

  — from

Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

分页查询格式:

SELECT *

  FROM (SELECT a.*, ROWNUM rn

          FROM (SELECT *

                  FROM table_name) a

         WHERE ROWNUM <= 40)

 WHERE rn >= 21

其中最内层的查询SELECT * FROM
TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >=
21控制分页查询的每页的范围。

上面给出的这个分页查询语句,在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小,将结果尽快的返回。在上面的分页查询语句中,这种考虑主要体现在WHERE ROWNUM <=
40这句上。

选择第21到40条记录存在两种方法,一种是上面例子中展示的在查询的第二层通过ROWNUM
<=
40来控制最大值,在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE
ROWNUM <=
40语句,在查询的最外层控制分页的最小值和最大值。这是,查询语句如下:

SELECT *

  FROM (SELECT a.*, ROWNUM rn

          FROM (SELECT *

                  FROM table_name) a)

 WHERE rn BETWEEN 21 AND 40

 

对比这两种写法,绝大多数的情况下,第一个查询的效率比第二个高得多。

这是由于CBO优化模式下,Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率。对于第一个查询语句,第二层的查询条件WHERE
ROWNUM <=
40就可以被Oracle推入到内层查询中,这样Oracle查询的结果一旦超过了ROWNUM限制条件,就终止查询将结果返回了。

而第二个查询语句,由于查询条件BETWEEN 21 AND
40是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义,因为最内层查询不知道RN代表什么)。因此,对于第二个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成,显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询,对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

这里就不对包含排序的查询进行说明了,下一篇文章会通过例子来详细说明。

 

下面简单讨论一下多表联合的情况。

对于最常见的等值表连接查询,CBO一般可能会采用两种连接方式NESTED
LOOP和HASH JOIN(MERGE JOIN效率比HASH
JOIN效率低,一般CBO不会考虑)。在这里,由于使用了分页,因此指定了一个返回的最大记录数,NESTED
LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层,而HASH
JOIN必须处理完所有结果集(MERGE
JOIN也是)。那么在大部分的情况下,对于分页查询选择NESTED
LOOP作为查询的连接方法具有较高的效率(分页查询的时候绝大部分的情况是查询前几页的数据,越靠后面的页数访问几率越小)。

因此,如果不介意在系统中使用HINT的话,可以将分页的查询语句改写为:

SELECT *

  FROM (SELECT a.*, ROWNUM rn

          FROM (SELECT *

                  FROM table_name) a

         WHERE ROWNUM <= 40)

 WHERE rn >= 21

 

 

MySQL数据库实现分页比较简单,提供了
LIMIT函数。一般只需要直接写到sql语句后面就行了。
LIMIT子
句可以用来限制由SELECT语句返回过来的数据数量,它有一个或两个参数,如果给出两个参数,
第一个参数指定返回的第一行在所有数据中的位置,从0开始(注意不是1),第二个参数指定最多返回行数。例如:
select * from table    LIMIT 10;    #返回前10行
select * from table    LIMIT 0,10; #返回前10行
select * from table  limit 5,10;   #返回第6-15行数据    第一个参数是指要开始的地方,第二个参数是指每页显示多少条数据;注意:第一页用0表示

–3.3.1
使用游标法进行字符串合并处理的示例。
–处理的数据
CREATE TABLE tb(col1
varchar(10),col2 int)
INSERT tb SELECT
‘a’,1
UNION ALL
SELECT ‘a’,2
UNION ALL
SELECT ‘b’,1
UNION ALL
SELECT ‘b’,2
UNION ALL
SELECT ‘b’,3

INSERT INTO t_change_lc SELECT 001 card_code, ROWNUM q,
trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM = 4
UNION SELECT 002 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100)
bal FROM dual CONNECT BY ROWNUM = 4;

表emp

SqlServer:

 

SQLServer数据库又分为 SQLServer2000和SQLServer2005。一般比较简单的方法是通过TOP函数来实现。如下: SELECT TOP 10 * FROM sql WHERE ( 
code NOT IN  (SELECT TOP 20 code  FROM TestTable  ORDER BY id)) ORDER BY ID 
这条语句,从理论上讲,整条语句的执行时间应该比子句的执行时间长,但事实相反。因为,子句执行后返回的是20条记录,而整条语句仅返回10条语句,所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I /O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQL SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。 以上语句的有一个致命的缺点,就是它含有NOT IN字样,要换成用not exists来代替not in,二者的执行效率实际上是没有区别的。 
在以上分页算法中,影响我们查询速度的关键因素有两点:TOP和NOT IN。TOP可以提高我们的查询速度,而NOT IN会减慢我们的查询速度,所以要提高我们整个分页算法的速度,就要彻底改造NOT IN,同其他方法来替代它。 我们知道,几乎任何字段,我们都可以通过max(字段)或min(字段)来提取某个字段中的最大或最小值,所以如果这个字段不重复,那么就可以利用这些不重复的字段的max或min作为分水岭,使其成为分页算法中分开每页的参照物。在这里,我们可以用操作符“>”或“<”号来完成这个使命。如:  
Select top 10 * from table1 where id>200  

于是就有了如下分页方案:  
select top 页大小 *  
from table1   
where id>  
(select max (id) from   
(select top ((页码-1)*页大小) id from table1 order by id) as T  
)       
order by id  
这种方法执行多少始终没有大的降势,后劲仍然很足。尤其对于数据量大的时候,该方法执行速度一点也不会降低。 
使用TOP要求主键必须唯一,不能是联合主键。如果是联合主键,则查询出的结果会乱序的。 
目前SQLServer2005提供了一个row_number()函数。ROW_NUMBER() 就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的
OVER(ORDER BY ReportID),其中ReportID可以是联合主键。下面,我们看看怎么具体应用这个RowNo进行分页. SELECT TOP 10 *  FROM ( 
SELECT top 10 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo 
FROM TABLE  ) AS A 
WHERE RowNo> ” + pageIndex*10 pageIndex就是我们需要数据的页数.  

 

Oracle:

–合并处理
–定义结果集表变量
DECLARE @t
TABLE(col1 varchar(10),col2 varchar(100))

SELECT * FROM t_change_lc;

图片 2

考虑mySql中的实现分页,select * from 表名  limit
开始记录数,显示多少条;就可以实现我们的分页效果。

–定义游标并进行合并处理
DECLARE tb CURSOR
LOCAL
FOR
SELECT col1,col2 FROM tb
ORDER BY 
col1,col2
DECLARE @col1_old
varchar(10),@col1
varchar(10),@col2
int,@s
varchar(100)
OPEN tb
FETCH tb INTO
@col1,@col2
SELECT @col1_old=@col1,@s=”
WHILE @@FETCH_STATUS=0
BEGIN
    IF @col1=@col1_old
        SELECT @s=@s+’,’+CAST(@col2
as varchar)
    ELSE
    BEGIN
        INSERT @t
VALUES(@col1_old,STUFF(@s,1,1,”))
        SELECT @s=’,’+CAST(@col2
as varchar),@col1_old=@col1
    END
    FETCH tb INTO
@col1,@col2
END
INSERT @t
VALUES(@col1_old,STUFF(@s,1,1,”))
CLOSE tb
DEALLOCATE tb
–显示结果并删除测试数据
SELECT *
FROM @t
DROP TABLE tb
/*–结果
col1       col2

SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0)) q1, SUM(decode(a.q,
2, a.bal, 0)) q2, SUM(decode(a.q, 3, a.bal, 0)) q3, SUM(decode(a.q, 4,
a.bal, 0)) q4 FROM t_change_lc a GROUP BY a.card_code ORDER BY 1;

表dept

但是在oracle中没有limit关键字,但是有 rownum字段


–行列转换 列转行 DROP TABLE t_change_cl; CREATE TABLE
t_change_cl AS SELECT a.card_code, SUM(decode(a.q, 1, a.bal, 0)) q1,
SUM(decode(a.q, 2, a.bal, 0)) q2, SUM(decode(a.q, 3, a.bal, 0)) q3,
SUM(decode(a.q, 4, a.bal, 0)) q4 FROM t_change_lc a GROUP BY
a.card_code ORDER BY 1;

子查询

rownum是一个伪列,是oracle系统自动为查询返回结果的每行分配的编号,第一行为1,第二行为2,以此类推。。。。

a          1,2
b          1,2,3
–*/
GO

SELECT * FROM t_change_cl;

子查询是一条查询语句,它是嵌套在其他SQL语句中的,目的是将查询的结果提供给外层的SQL语句。

第一种:

/*==============================================*/

SELECT t.card_code, t.rn q, decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4,
t.q4) bal FROM (SELECT a.*, b.rn FROM t_change_cl a, (SELECT ROWNUM
rn FROM dual CONNECT BY ROWNUM = 4) b) t ORDER BY 1, 2;

查看谁的工资高于CLARK的工资?

代码如下:

–3.3.2
使用用户定义函数,配合SELECT处理完成字符串合并处理的示例
–处理的数据
CREATE TABLE tb(col1
varchar(10),col2 int)
INSERT tb SELECT
‘a’,1
UNION ALL
SELECT ‘a’,2
UNION ALL
SELECT ‘b’,1
UNION ALL
SELECT ‘b’,2
UNION ALL
SELECT ‘b’,3
GO

–行列转换 行转列 合并 DROP TABLE t_change_lc_comma; CREATE TABLE
t_change_lc_comma AS SELECT card_code,quarter_||q AS q FROM
t_change_lc;

SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE
ename=’CLARK’)

SELECT * FROM
(
    SELECT A.*, ROWNUM RN
    FROM (SELECT * FROM TABLE_NAME) A
    WHERE ROWNUM <= 40
)
WHERE RN >= 21

–合并处理函数
CREATE FUNCTION
dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
    DECLARE @re
varchar(100)
    SET @re=”
    SELECT @re=@re+’,’+CAST(col2
as varchar)
    FROM tb
    WHERE col1=@col1
    RETURN(STUFF(@re,1,1,”))
END
GO

SELECT * FROM t_change_lc_comma;

查看与CLARK同部门的员工?

其中最内层的查询SELECT * FROM
TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >=
21控制分页查询的每页的范围。

–调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb
GROUP BY col1
–删除测试
DROP TABLE tb
DROP FUNCTION
f_str
/*–结果
col1       col2

SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;)), 2) q
FROM (SELECT a.card_code, a.q, row_number() over(PARTITION BY
a.card_code ORDER BY a.q) rn FROM t_change_lc_comma a) t1 START WITH
t1.rn = 1 CONNECT BY t1.card_code = PRIOR t1.card_code AND t1.rn – 1 =
PRIOR t1.rn GROUP BY t1.card_code;

SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE
ename=’CLARK’) AND ename<>’CLARK’

上面给出的这个分页查询语句,在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小,将结果尽快的返回。在上面的分页查询语句中,这种考虑主要体现在WHERE
ROWNUM <= 40这句上。


–行列转换 列转行 分割 DROP TABLE t_change_cl_comma; CREATE TABLE
t_change_cl_comma AS SELECT t1.card_code,
substr(MAX(sys_connect_by_path(t1.q, ;)), 2) q FROM (SELECT
a.card_code, a.q, row_number() over(PARTITION BY a.card_code ORDER BY
a.q) rn FROM t_change_lc_comma a) t1 START WITH t1.rn = 1 CONNECT BY
t1.card_code = PRIOR t1.card_code AND t1.rn – 1 = PRIOR t1.rn GROUP BY
t1.card_code;

查看高于公司平均工资的员工?

选择第21到40条记录存在两种方法,一种是上面例子中展示的在查询的第二层通过ROWNUM
<=
40来控制最大值,在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE
ROWNUM <= 40语句,在查询的最外层控制分页的最小值和最大值。

a          1,2
b          1,2,3
–*/
GO

SELECT * FROM t_change_cl_comma;

SELECT ename,sal FROM emp WHERE sal>(SELECT AVG(sal) FROM emp)

第二种:

/*==============================================*/

SELECT t.card_code, substr(t.q, instr(; || t.q, ;, 1, rn), instr(t.q ||
;, ;, 1, rn) – instr(; || t.q, ;, 1, rn)) q FROM (SELECT a.card_code,
a.q, b.rn FROM t_change_cl_comma a, (SELECT ROWNUM rn FROM dual
CONNECT BY ROWNUM = 100) b WHERE instr(; || a.q, ;, 1, rn) 0) t ORDER BY
1, 2;

在DDL中使用子查询,可以将一个子查询的结果集当做表快速创建出来。

 代码如下:

–3.3.3
使用临时表实现字符串合并处理的示例
–处理的数据
CREATE TABLE tb(col1
varchar(10),col2 int)
INSERT tb SELECT
‘a’,1
UNION ALL
SELECT ‘a’,2
UNION ALL
SELECT ‘b’,1
UNION ALL
SELECT ‘b’,2
UNION ALL
SELECT ‘b’,3

— 实现一条记录根据条件多表插入 DROP TABLE t_ia_src; CREATE TABLE
t_ia_src AS SELECT a||ROWNUM c1, b||ROWNUM c2 FROM dual CONNECT BY
ROWNUM=5; DROP TABLE t_ia_dest_1; CREATE TABLE t_ia_dest_1(flag
VARCHAR2(10) , c VARCHAR2(10)); DROP TABLE t_ia_dest_2; CREATE TABLE
t_ia_dest_2(flag VARCHAR2(10) , c VARCHAR2(10)); DROP TABLE
t_ia_dest_3; CREATE TABLE t_ia_dest_3(flag VARCHAR2(10) , c
VARCHAR2(10));

CREATE TABLE employee AS 

select * from (select e.*,rownum  r from  (select * from emp order by sal desc)
e ) e1 where e1.r>21 and
e1.r<=40;

–合并处理
SELECT col1,col2=CAST(col2
as varchar(100))
INTO #t FROM tb
ORDER BY
col1,col2
DECLARE @col1
varchar(10),@col2
varchar(100)
UPDATE #t SET
    @col2=CASE
WHEN @col1=col1
THEN @col2+’,’+col2
ELSE col2 END,
    @col1=col1,
    col2=@col2
SELECT *
FROM #t
/*–更新处理后的临时表
col1       col2

SELECT * FROM t_ia_src; SELECT * FROM t_ia_dest_1; SELECT * FROM
t_ia_dest_2; SELECT * FROM t_ia_dest_3;

SELECT e.empno,e.ename,e.sal,e.job,d.deptno,d.dname,d.loc FROM emp
e,dept d WHERE e.deptno=d.deptno(+)

红色部分:按照工资降序排序并查询所有的信息。


INSERT ALL WHEN (c1 IN (a1,a3)) THEN INTO t_ia_dest_1(flag,c)
VALUES(flag1,c2) WHEN (c1 IN (a2,a4)) THEN INTO t_ia_dest_2(flag,c)
VALUES(flag2,c2) ELSE INTO t_ia_dest_3(flag,c)
VALUES(flag1||flag2,c1||c2) SELECT c1,c2, f1 flag1, f2 flag2 FROM
t_ia_src;

在使用子查询创建表的时候,查询多少个字段则创建的表就有多少个字段,字段名与查询的字段

棕色部分:得到红色部门查询的值,并查询出系统的rownum并指定上别名。这一句就比较关键,起了一个过渡的作用,首先要算出rownum来对红色部分指定上序号,也可以为蓝色外面部分用到这个变量。指定上查询的开始记录数和结束记录的条件。

a          1
a          1,2
b          1
b          1,2
b          1,2,3
–*/
–得到最终结果
SELECT col1,col2=MAX(col2)
FROM #t GROUP
BY col1
/*–结果
col1       col2

— 如果存在就更新,不存在就插入用一个语句实现 DROP TABLE t_mg;
CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10));

名字一致(不含有表别名),类型,长度也一致。

蓝色部分:指定记录从第几条开始到第几条结束,取出棕色部门的值来作为查询条件的变量


SELECT * FROM t_mg;

但是需要注意的是,若查询的字段是函数或者表达式,那么这个字段必须指定别名,并且生成

总结:绝大多数的情况下,第一个查询的效率比第二个高得多。

a          1,2
b          1,2,3
–*/
–删除测试
DROP TABLE
tb,#t
GO

MERGE INTO t_mg a USING (SELECT the code code, the name NAME FROM dual)
b ON (a.code = b.code) WHEN MATCHED THEN UPDATE SET a.NAME = b.NAME WHEN
NOT MATCHED THEN INSERT (code, NAME) VALUES (b.code, b.NAME);

的表对应的该字段名就是这个别名。

SqlServer:

/*==============================================*/

— 抽取/删除重复记录 DROP TABLE t_dup; CREATE TABLE t_dup AS
SELECT code_||ROWNUM code, dbms_random.string(z,5) NAME FROM dual
CONNECT BY ROWNUM=10; INSERT INTO t_dup SELECT code_||ROWNUM code,
dbms_random.string(z,5) NAME FROM dual CONNECT BY ROWNUM=2;

DML中使用子查询

分页方案一:(利用Not In和SELECT TOP分页)

–3.3.4.1 每组 <=2 条记录的合并
–处理的数据
CREATE TABLE tb(col1
varchar(10),col2 int)
INSERT tb SELECT
‘a’,1
UNION ALL
SELECT ‘a’,2
UNION ALL
SELECT ‘b’,1
UNION ALL
SELECT ‘b’,2
UNION ALL
SELECT ‘c’,3

SELECT * FROM t_dup;

将CLARK所在部门的员工工资上浮10%

语句形式:

–合并处理
SELECT col1,
    col2=CAST(MIN(col2)
as varchar)
        +CASE
            WHEN COUNT(*)=1 THEN

            ELSE ‘,’+CAST(MAX(col2)
as varchar)
        END
FROM tb
GROUP BY col1
DROP TABLE tb
/*–结果
col1       col2     

SELECT * FROM t_dup a WHERE a.ROWID (SELECT MIN(b.ROWID) FROM t_dup b
WHERE a.code=b.code);

UPDATE emp SET sal=sal*1.1 WHERE deptno=(SELECT deptno FROM emp WHERE
ename=’CLARK’)

代码如下:


SELECT b.code, b.NAME FROM (SELECT a.code, a.NAME, row_number()
over(PARTITION BY a.code ORDER BY a.ROWID) rn FROM t_dup a) b WHERE
b.rn 1;

子查询根据查询结果分为:

SELECT TOP 10 *  FROM TestTable WHERE (ID NOT IN (SELECT TOP 20 id FROM
TestTable ORDER BY id)) ORDER BY ID 

a          1,2
b          1,2
c          3
–*/

— IN/EXISTS的不同适用环境 — t_orders.customer_id有索引 SELECT a.*
FROM t_employees a WHERE a.employee_id IN (SELECT b.sales_rep_id
FROM t_orders b WHERE b.customer_id = 12);

单行单列,多行单列,多行多列子查询

SELECT TOP 页大小 * FROM TestTable WHERE (ID NOT IN (SELECT TOP
页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID

–3.3.4.2 每组 <=3 条记录的合并
–处理的数据
CREATE TABLE tb(col1
varchar(10),col2 int)
INSERT tb SELECT
‘a’,1
UNION ALL
SELECT ‘a’,2
UNION ALL
SELECT ‘b’,1
UNION ALL
SELECT ‘b’,2
UNION ALL
SELECT ‘b’,3
UNION ALL
SELECT ‘c’,3

SELECT a.* FROM t_employees a WHERE EXISTS (SELECT 1 FROM t_orders b
WHERE b.customer_id = 12 AND a.employee_id = b.sales_rep_id);

其中单列子查询常用在过滤条件中多列子查询常被当做表使用。

分页方案二:(利用ID大于多少和SELECT TOP分页)

–合并处理
SELECT col1,
    col2=CAST(MIN(col2)
as varchar)
        +CASE
            WHEN COUNT(*)=3 THEN
‘,’
                +CAST((SELECT col2
FROM tb WHERE
col1=a.col1 AND col2
NOT IN(MAX(a.col2),MIN(a.col2)))
as varchar)
            ELSE ”
        END
        +CASE
            WHEN COUNT(*)>=2 THEN
‘,’+CAST(MAX(col2)
as varchar)
            ELSE ”
        END
FROM tb a
GROUP BY col1
DROP TABLE tb
/*–结果
col1       col2

— t_employees.department_id有索引 SELECT a.* FROM t_employees a
WHERE a.department_id = 10 AND EXISTS (SELECT 1 FROM t_orders b WHERE
a.employee_id = b.sales_rep_id);

对于多行单列子查询在进行过滤判断时,需要配合IN,ANY,ALL使用。

语句形式: