SQL特别用法

转载于 2010-03-23 15:02:00 发布 · 365 阅读

文章标签：

#sql #insert #microsoft #table #null #sql server

SqlServer 专栏收录该内容

1 篇文章

订阅专栏

本文深入探讨了SQL中的EXCEPT操作符的特性，包括NULL值的处理，以及如何利用字符串解决复杂的数据问题。同时，介绍了OVER()子句在聚合函数中的高效应用，以及使用字符串替代子查询在特定场景下的优势。此外，还讲解了如何通过聚合实现字符串拼接，并展示了利用TOP+ORDERBY求取中值的方法，最后讨论了将OR转换为AND以提高查询性能的策略。

详见博客地址:http://blog.csdn.net/feixianxxx/archive/2010/03/21/5402391.aspx

SQL code

   
    
    /*
    ----------------------------------------------------------------------
*auther:Poofly
*date:2010.3.14
*VERSION:
    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
    Jul  19 2008 
    Copyright (c) 1988-2008 Microsoft Corporation
    Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )
*转载请注明出处
*更多精彩内容,请进http://blog.csdn.net/feixianxxx
------------------------------------------------------------------------
    */
    

    --
    收集一些东西来写写

--1.EXCEPT
注意点:2个NULL值对于EXCEPT是相等的,不同于一般的筛选器.

SQL code

   
    
    --
    环境
    

    create
     
    table
     test_1 (a 
    int
     ,b 
    int
    )

    create
     
    table
     test_2 (c 
    int
    , d 
    int
    )

    insert
     test_1 
    select
     
    1
    ,
    2
     
    union
     
    all
     
    select
     
    1
    ,
    null
     
    union
     
    all
     
    select
     
    3
    ,
    4
    

    insert
     test_2 
    select
     
    1
    ,
    2
     
    union
     
    all
     
    select
     
    1
    ,
    null
    

    --
     except 
    

    select
     
    *
     
    from
     test_1 

    except
     

    select
     
    *
     
    from
     test_2

    /*
    
a           b
----------- -----------
3           4

    */
    

    --
     not exists
    

    select
     
    *
     
    from
     test_1

    where
     
    not
     
    exists
    (
    select
     
    *
     
    from
     test_2 
    where
     a
    =
    c 
    and
     b
    =
    d)

    /*
    
a           b
----------- -----------
1           NULL               --这条记录对于test_1来说是唯一的
3           4

    */

ps:因为现有版本不支持 except all 所以EXCEPT使用时候会有一个排序的阶段,效率一般不太好

--2.OVER()子句
注意点:OVER()子句在多聚合情况下比在select 下的子查询效率 or GROUP BY 高很多

SQL code

   
    
    --
    环境
    

    create
     
    table
     test_3
(
    id 
    int
    ,
    value 
    int
    
)

    insert
     test_3 
    values
    (
    1
    ,
    12
    )

    insert
     test_3 
    values
    (
    1
    ,
    1
    )

    insert
     test_3 
    values
    (
    1
    ,
    3
    )

    insert
     test_3 
    values
    (
    1
    ,
    2
    )

    insert
     test_3 
    values
    (
    1
    ,
    6
    )

    insert
     test_3 
    values
    (
    2
    ,
    1
    )

    insert
     test_3 
    values
    (
    2
    ,
    2
    )

    insert
     test_3 
    values
    (
    2
    ,
    4
    )

    insert
     test_3 
    values
    (
    2
    ,
    3
    )

    go
    

    --
    OVER
    

    SELECT
     ID,

    [
    SUM
    ]
    =
    SUM
    (VALUE) 
    OVER
    (),

    [
    AVG
    ]
    =
    AVG
    (VALUE) 
    OVER
    (),

    [
    COUNT
    ]
    =
    COUNT
    (VALUE) 
    OVER
    (),

    [
    MAX
    ]
    =
    MAX
    (VALUE) 
    OVER
    ()

    FROM
     test_3 

    --
    子查询
    

    select
     id,

    [
    SUM
    ]
    =
    (
    select
     
    SUM
    (VALUE) 
    from
     test_3 
    where
     l.id
    =
    id),

    [
    AVG
    ]
    =
    (
    select
     
    AVG
    (VALUE) O 
    from
     test_3 
    where
     l.id
    =
    id),

    [
    COUNT
    ]
    =
    (
    select
     
    COUNT
    (VALUE)  
    from
     test_3 
    where
     l.id
    =
    id),

    [
    MAX
    ]
    =
    (
    select
     
    MAX
    (VALUE)  
    from
     test_3 
    where
     l.id
    =
    id)

    FROM
     test_3 l

    --
    group by 
    

    SELECT
     ID,

    [
    SUM
    ]
    =
    SUM
    (VALUE),

    [
    AVG
    ]
    =
    AVG
    (VALUE) ,

    [
    COUNT
    ]
    =
    COUNT
    (VALUE) ,

    [
    MAX
    ]
    =
    MAX
    (VALUE) 

    FROM
     test_3 

    group
     
    by
     id

CTRL+L 可以发现 over的开销明显小于后者..其中子查询效率极差

--3.利用字符串解决带附加属性的问题
注意点:当需要附加属性来解决问题时候,一般会想到子查询,但是因为子查询需要良好的索引设计,所以不太好用.可以使用字符串来解决这个问题

SQL code

   
    
    --
    环境
    

    create
     
    table
     test_4 (id 
    int
    , a 
    int
    , b 
    int
     , c 
    int
    )

    insert
     test_4  
    select
     

    1
    ,
    2
    ,
    3
    ,
    4
     
    union
     
    all
     
    select
     

    1
    ,
    3
    ,
    5
    ,
    4
     
    union
     
    all
     
    select
     

    1
    ,
    3
    ,
    7
    ,
    4
     
    union
     
    all
     
    select
     

    1
    ,
    3
    ,
    7
    ,
    8
     
    union
     
    all
     
    select
     

    2
    ,
    2
    ,
    3
    ,
    4
     
    union
     
    all
     
    select
     

    2
    ,
    5
    ,
    3
    ,
    8
     
    union
     
    all
     
    select
     

    2
    ,
    5
    ,
    3
    ,
    8
     
    union
     
    all
     
    select
     

    2
    ,
    7
    ,
    3
    ,
    8
     
    union
     
    all
     
    select
     

    2
    ,
    1
    ,
    9
    ,
    9
     

    go
    

    --
    字符串
    

    select
     
ID,
a
    =
    SUBSTRING
    (COL,
    1
    ,
    5
    ),
b
    =
    SUBSTRING
    (COL,
    6
    ,
    5
    ),
c
    =
    SUBSTRING
    (COL,
    11
    ,
    5
    )

    from
     (

    select
     ID,

    MAX
    (
    CAST
    (a 
    as
     
    char
    (
    5
    ))
    +
    CAST
    (b 
    as
     
    char
    (
    5
    ))
    +
    CAST
    (c 
    as
     
    char
    (
    5
    ))) 
    as
     col

    from
     test_4 

    group
     
    by
     ID
) l


    --
    子查询
    

    select
     
    *
     

    from
     test_4  k

    where
     
    not
     
    exists
    (
    select
     
    *
     
    from
     test_4 

    where
     id
    =
    k.id 
    and
     (a
    >
    k.a 
    or
     a
    =
    k.a 
    and
     b
    >
    k.b 
    or
     a
    =
    k.a 
    and
     b
    =
    k.b 
    and
     c
    >
    k.c))

    /*
    
ID          a          b          c
----------- ---------- ---------- ----------
1           3          7          8    
2           7          3          8    

    */

这个字符串优点是无论是否有好的索引,因为它只扫描一次(这里数据量太小)

--4.利用聚合实现字符串拼接
注意:不用XML、函数、临时表、游标去实现字符串的拼接

SQL code

   
    
    --
    环境
    

    create
     
    table
     test_5(empid 
    int
    , name 
    varchar
    (
    10
    ))

    insert
     test_5 
    select
     

    1
    ,
    '
    a
    '
    union
     
    all
     
    select
     

    1
    ,
    '
    b
    '
     
    union
     
    all
     
    select
     

    1
    ,
    '
    c
    '
    union
     
    all
     
    select
     

    1
    ,
    '
    d
    '
    union
     
    all
     
    select
     

    2
    ,
    '
    a
    '
     
    union
     
    all
     
    select
     

    2
    ,
    '
    t
    '
    union
     
    all
     
    select
     

    2
    ,
    '
    v
    '
      

    select
     
empid,
name
    =
    MAX
    (
    case
     
    when
     rn
    =
    1
     
    then
     name  
    else
     
    ''
     
    end
    )
    +
    MAX
    (
    case
     
    when
     rn
    =
    2
     
    then
     
    '
    ,
    '
    +
    name 
    else
     
    ''
     
    end
    )

    +
    MAX
    (
    case
     
    when
     rn
    =
    3
     
    then
     
    '
    ,
    '
    +
    name 
    else
     
    ''
     
    end
    )
    +
    MAX
    (
    case
     
    when
     rn
    =
    4
     
    then
     
    '
    ,
    '
    +
    name 
    else
     
    ''
      
    end
    ) 

    from
    (

    select
     empid,name,
(
    select
     
    COUNT
    (
    *
    ) 
    from
     test_5 
    where
     k.empid
    =
    empid 
    and
     k.name
    >=
    name) 
    as
     rn 

    from
     test_5 k )z 

    group
     
    by
     empid


    --
    xml
    

    select
     empid,
name
    =
    stuff
    ((
    select
     
    '
    ,
    '
    +
    name 
    as
     
    [
    text()
    ]
     
    from
     test_5 
    where
     k.empid
    =
    empid 
    order
     
    by
     name 
    for
     XML PATH(
    ''
    )),
    1
    ,
    1
    ,
    ''
    )

    from
     test_5 k

    group
     
    by
     empid

    /*
    
empid       name
----------- -------------------------------------------
1           a,b,c,d
2           a,t,v

    */

ps:该方法前提是name不能在同一组里出现重复,且每组内最大记录数不是很大。通过执行发现该方法效率优于XML
适合用于SQL2000中想实现字符串拼接但是又不想函数的情况下.

5.TOP + ORDER BY 求中值
注意点:可以利用TOP + ORDER BY 巧妙解决取中值的问题,而且在SQL2000也可以方便使用

SQL code

   
    
    --
    环境
    

    create
     
    table
     test_6(rq 
    varchar
    (
    8
    ), ddsj 
    int
    )

    insert
     
    into
     test_6

    select
    

    '
    200805
    '
    ,
    30
     
    union
     
    all
     
    select
    

    '
    200805
    '
    ,
    40
     
    union
     
    all
     
    select
     

    '
    200805
    '
    ,
    50
     
    union
     
    all
     
    select
     

    '
    200805
    '
    ,
    20
     
    union
     
    all
     
    select
     

    '
    200806
    '
    ,
    250
     
    union
     
    all
     
    select
    

    '
    200806
    '
    ,
    200
     
    union
     
    all
     
    select
    

    '
    200806
    '
    ,
    310
     
    union
     
    all
     
    select
    

    '
    200806
    '
    ,
    100
     
    union
     
    all
     
    select
    

    '
    200806
    '
    ,
    130
    

    go
    

    --
    TOP + ORDER BY 求中值(NTILE同样的效果)
    

    select
     rq,
(
    (
        
    select
     
    MAX
    (ddsj) 
    as
     ddsj
        
    from
     (
    select
     
    top
     
    50
     
    percent
     ddsj 
    from
     test_6 
    where
     rq
    =
    k.rq 
    order
     
    by
     ddsj ) k 
    )
    +
    
    (
        
    select
     
    MIN
    (ddsj) 
    as
     ddsj
        
    from
     (
    select
     
    top
     
    50
     
    percent
     ddsj 
    from
     test_6 
    where
     rq
    =
    k.rq 
    order
     
    by
     ddsj 
    desc
    ) k 
    )
)
    /
    2
     
    as
     ddsj

    from
     test_6 k

    group
     
    by
     rq 

    --
    利用位置的收尾呼应~
    

    ;
    with
     cte 
    as
    
(

    select
     
    *
    ,ROW_NUMBER() 
    over
    (partition 
    by
     rq 
    order
     
    by
     ddsj ) 
    as
     rn1,
         ROW_NUMBER() 
    over
    (partition 
    by
     rq 
    order
     
    by
     ddsj 
    desc
     ) 
    as
     rn2    

    from
     test_6 
)

    select
     rq,ddsj
    =
    AVG
    (ddsj) 

    from
    
    cte 

    where
     
    abs
    (rn1
    -
    rn2)
    <=
    1
    

    group
     
    by
     rq 


    /*
    
rq       ddsj
-------- -----------
200805   35
200806   200


    */

在适用缺点是没有合适的索引将会很慢

--6 OR 转变成AND
注意点:大家都知道OR在where条件出现时候,一般来说意味着索引的失效,只要筛选列上有一个没有索引的话.相比较而言,AND是有个索引就有个效果.所以我们有必要在能将OR转成AND的时候就转化.

SQL code

   
    
    --
    环境
    

    create
     
    table
     test_7
(
    id 
    int
    ,
    col1 
    int
    ,
    col2 
    int
    
)

    create
     
    index
     in_col1 
    on
     test_7(col1)

    insert
     test_7 
    values
    (
    1
    ,
    1
    ,
    3
    )

    insert
     test_7 
    values
    (
    1
    ,
    1
    ,
    2
    )

    insert
     test_7 
    values
    (
    1
    ,
    2
    ,
    3
    )

    go
     
    100000
    

    insert
     test_7 
    values
    (
    1
    ,
    3
    ,
    3
    )

    insert
     test_7 
    values
    (
    1
    ,
    3
    ,
    1
    )

    insert
     test_7 
    values
    (
    2
    ,
    2
    ,
    3
    )

    insert
     test_7 
    values
    (
    2
    ,
    2
    ,
    5
    )

    insert
     test_7 
    values
    (
    2
    ,
    4
    ,
    9
    )

    go
    

    --
    or
    

    select
     
    *
    

    from
     test_7

    where
     col1
    <
    1
     
    or
     (col1
    =
    1
     
    and
     col2
    <
    3
    )

    --
    and
    

    select
     
    *
    

    from
     test_7 

    where
     col1
    <=
    1
     
    and
     (col1
    <
    1
     
    or
     col2
    <
    3
    )

通过执行计划可以看到带AND的开销比OR少了倍,它用到了COL1上的索引

--7 利用计算表达式改变IDENTITY
注意:使用select into 表的时候如果有自增列会把自增的属性也复制过去,可以通过计算表达式去去除这个自增属性

SQL code

   
    
    --
    环境
    

    create
     
    table
     test_8(id 
    int
     
    identity
    (
    1
    ,
    1
    ),a 
    int
    )

    insert
     test_8 
    select
     
    1
    

    insert
     test_8 
    select
     
    2
    

    insert
     test_8 
    select
     
    3
    

    insert
     test_8 
    select
     
    7
    

    insert
     test_8 
    select
     
    9
    

    go
    

    select
     ID
    +
    0
     
    as
     id ,A 
    into
     #
    1
     
    from
     test_8 

    insert
     #
    1
    (a) 
    select
     
    8
    

    select
     
    *
     
    from
     #
    1
     

    /*
    
id          A
----------- -----------
NULL        8                 ---没有自增
1           1
2           2
3           3
4           7
5           9


    */

--8.set 中的连等
注意:在更新时也可以使用连续等于 @i=col=@i+1 <===> @i加后赋值给COL字段

SQL code

   
    
    --
    环境
    

    CREATE
     
    TABLE
     test_9
(
  id 
    INT
            
    NOT
     
    NULL
    ,
  col 
    VARCHAR
    (
    5
    ) 
    NOT
     
    NULL
    
);


    INSERT
     
    INTO
     test_9 
    VALUES
    (
    0
    , 
    '
    A
    '
    );

    INSERT
     
    INTO
     test_9 
    VALUES
    (
    0
    , 
    '
    B
    '
    );

    INSERT
     
    INTO
     test_9 
    VALUES
    (
    0
    , 
    '
    C
    '
    );

    INSERT
     
    INTO
     test_9 
    VALUES
    (
    0
    , 
    '
    C
    '
    );

    INSERT
     
    INTO
     test_9 
    VALUES
    (
    0
    , 
    '
    C
    '
    );

    INSERT
     
    INTO
     test_9 
    VALUES
    (
    0
    , 
    '
    B
    '
    );

    INSERT
     
    INTO
     test_9 
    VALUES
    (
    0
    , 
    '
    A
    '
    );

    go
    

    DECLARE
     
    @i
     
    AS
     
    INT
    ;

    SET
     
    @i
     
    =
     
    0
    ;

    UPDATE
     test_9 
    SET
     
    @i
     
    =
     id 
    =
     
    @i
     
    +
     
    1
    ;

    go
    

    select
     
    *
     
    from
     test_9 

    /*
    
id          col
----------- -----
1           A
2           B
3           C
4           C
5           C
6           B
7           A

    */