取出特定的数据
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

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

(4 row(s) affected)

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

(4 row(s) affected)
[解决办法]
直接这样不行吗？？？干嘛要用exists
select type, min(name) as name from @t group by type
select type, max(name) as name from @t group by type
[解决办法]
exists合多列的情,比通用

然在只有2列的情下,用max和min更