/** 构建测试表及数据
*/
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内仅实现数据处理(按类排序及其它业务逻辑), 数据展现效果问题拿到前台应用中处理.