这个SQL语句如何写,执行效率会高一些?

1个回答

  • /** 构建测试表及数据

    */

    set nocount on

    declare @tb table (category char(1), subclass char(2))

    insert into @tb values ('A','A1')

    insert into @tb values ('A','A2')

    insert into @tb values ('A','A3')

    insert into @tb values ('B','B1')

    insert into @tb values ('B','B2')

    insert into @tb values ('B','B3')

    insert into @tb values ('C','C1')

    insert into @tb values ('C','C2')

    insert into @tb values ('C','C3')

    select * from @tb

    -- category subclass

    -- A A1

    -- A A2

    -- A A3

    -- B B1

    -- B B2

    -- B B3

    -- C C1

    -- C C2

    -- C C3

    --

    select

    case when exists (

    select 1 from @tb b

    where b.category = a.category

    and b.subclass < a.subclass )

    then ' ' else category end as class,

    subclass

    from @tb a

    order by a.category, subclass

    -- class subclass

    -- A A1

    -- A2

    -- A3

    -- B B1

    -- B2

    -- B3

    -- C C1

    -- C2

    -- C3

    -- 若查询结果中大类的字段名称要求与原字段名相同:

    select

    case when exists (

    select 1 from @tb b

    where b.category = a.category

    and b.subclass < a.subclass )

    then ' ' else category end as category,

    subclass

    from @tb a

    order by a.category, subclass

    -- category subclass

    -- A2

    -- A3

    -- B2

    -- B3

    -- C2

    -- C3

    -- A A1

    -- B B1

    -- C C1

    -- 这样好像不理想

    select

    case when exists (

    select 1 from @tb b

    where b.category = a.category

    and b.subclass < a.subclass )

    then ' ' else category end as category,

    subclass

    from @tb a

    order by ''+a.category, subclass -- 排序原则指定原大类名称的简单计算(添加空串) ..

    -- category subclass

    -- A A1

    -- A2

    -- A3

    -- B B1

    -- B2

    -- B3

    -- C C1

    -- C2

    -- C3

    --

    效率问题么: 1. 尽量别用游标,

    2. 强烈建议在SQL内仅实现数据处理(按类排序及其它业务逻辑), 数据展现效果问题拿到前台应用中处理.