去掉重复记录问题

原记录为： 

  a    b    c
-----------------
 1    1     a
 1    2     b
 1    3     c
 2    4     d
 1    5     e
 2    6     f
 2    7     g
 3    8     h

要求结果为：
  a    b    c
-----------------
 1    1     a
      2     b
      3     c
      5     d
 2    4     e
      6     f
      7     g
 3    8     h

答案1：

declare @t table(a int,b int,c varchar(20))
insert @t
select 1,    1,     'a' union all
select 1,    2,     'b' union all
select 1,    3,     'c' union all
select 2,    4,     'd' union all
select 1,    5,     'e' union all
select 2,    6,     'f' union all
select 2,    7,     'g' union all
select 3,    8,     'h'

----查询
select
a = case
when b = (select top 1 b from @t where a = x.a order by b)
then cast(a as varchar(10))
else '' end,b,c
from @t as x
order by a*1,b

---结果
a          b           c                   
---------- ----------- --------------------
1          1           a
           2           b
           3           c
           5           e
2          4           d
           6           f
           7           g
3          8           h

（所影响的行数为 8 行）

如果
order by a,b

sql的优化器会认为按照结果的第一个字段排序，这样所有空的都跑到最前面去了
order by a*1,b
就是指明先按a字段排序，加了运算(*1)表示必须选择字段
也可以：
declare @t table(a int,b int,c varchar(20))
insert @t
select 1,    1,     'a' union all
select 1,    2,     'b' union all
select 1,    3,     'c' union all
select 2,    4,     'd' union all
select 1,    5,     'e' union all
select 2,    6,     'f' union all
select 2,    7,     'g' union all
select 3,    8,     'h'

----查询
select
a1 = case
when b = (select top 1 b from @t where a = x.a order by b)
then cast(a as varchar(10))
else '' end,b,c
from @t as x
order by a,b

不过前端也许就不方便了

答案2：

declare @t table(a int,b int,c varchar(20))
insert @t
select 1,    1,     'a' union all
select 1,    2,     'b' union all
select 1,    3,     'c' union all
select 2,    4,     'd' union all
select 1,    5,     'e' union all
select 2,    6,     'f' union all
select 2,    7,     'g' union all
select 3,    8,     'h'

----方法1
select
d = case   /*取个新列名,防止与a同名,以使a能用于排序*/
when b = (select top 1 b from @t where a = x.a order by b)
then cast(a as varchar(10))
else '' end,b,c
from @t as x
order by a,b     /*按照a列原值排序,再按b列原值排序*/

----方法2:使用排序后的子查询作为查询的数据源(效率要低些)
select
a = case
when b = (select top 1 b from @t where a = x.a order by b)
then cast(a as varchar(10))
else '' end,b,c
from (select top 100 percent * from @t order by a,b) as x    /*子查询表示对源数据进行排序*/

这样的sql不是循环也不是嵌套,只是一种相关查询,体现在下面这行代码中:
when b = (select top 1 b from @t where a = x.a order by b)
即源表中每扫描一行都判断"b = (select top 1 b from @t where a = x.a order by b)"这个条件是否成立,关联之处在于子查询中的a = x.a