取出特定的数据
CREATE   TABLE   [@t](type   int,name   varchar(20))
insert   [@t]
select   0, 'a '   union   all
select   0, 'b '   union   all
select   1, 'c '   union   all
select   1, 'd '   union   all
select   2, 'e '   union   all
select   2, 'f '   union   all
select   3, 'g '   union   all
select   3, 'h '

1,从@t里取出
0   a  
1   c
2   e
3   g

2,从表中取出
0   b
1   d
2   f
3   h

有什么好的方法?

------解决方案--------------------
1.select * from @t t where not exists(select 1 from @t where type=t.type and name <t.name)
2.select * from @t t where not exists(select 1 from @t where type=t.type and name> t.name)
------解决方案--------------------
declare @t TABLE (type int,name varchar(20))
insert @t
select 0, 'a ' union all
select 0, 'b ' union all
select 1, 'c ' union all
select 1, 'd ' union all
select 2, 'e ' union all
select 2, 'f ' union all
select 3, 'g ' union all
select 3, 'h '

select * from @t a
where not exists(select 1 from @t where type = a.type and name < a.name)

select * from @t a
where not exists(select 1 from @t where type = a.type and name > a.name)

/*
（所影响的行数为 8 行）

type name
----------- --------------------
0 a
1 c
2 e
3 g

（所影响的行数为 4 行）

type name
----------- --------------------
0 b
1 d
2 f
3 h

（所影响的行数为 4 行）
*/
------解决方案--------------------
declare @t TABLE (type int,name varchar(20))
insert @t
select 0, 'a ' union all
select 0, 'b ' union all
select 1, 'c ' union all
select 1, 'd ' union all
select 2, 'e ' union all
select 2, 'f ' union all
select 3, 'g ' union all
select 3, 'h '


select * from @t


select TYPE,min(name) name from @t
group by type

select TYPE,max(name) name from @t
group by type


TYPE name
----------- --------------------
0 a
1 c
2 e
3 g

(4 row(s) affected)

TYPE name
----------- --------------------
0 b
1 d
2 f
3 h

(4 row(s) affected)
------解决方案--------------------
CREATE TABLE t(type int,name varchar(20))
insert t
select 0, 'a ' union all
select 0, 'b ' union all
select 1, 'c ' union all
select 1, 'd ' union all
select 2, 'e ' union all
select 2, 'f ' union all
select 3, 'g ' union all
select 3, 'h '

select * from t a
where exists(select 1 from t where a.type=type and name <a.name)

select * from t a
where exists(select 1 from t where a.type=type and name> a.name)


type name