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