如何通过一条SQL语句实现如此复杂的查询

[casper1]

TABLE1:
ID MC AGE
1 A 5
2 A 7
3 A -8
4 A 5
5 B 1
6 B 3
7 B -5
8 C 4
9 C 3
10 C 3
11 D 10
通过一条SQL语句得到下面的结果：
ID MC AGE
4 A 5
5 B 1
10 C 3
11 D 10
思路是先按MC分组，分组后再判断AGE取最小值（并且要大于等于0），然后再根据ID取ID较大的记录（比如(1 A 5)(4 A 5))就要取记录（4 A 5)了

[sdhdy]

select  a.* from tb a,
(select mc,min(AGE) AGE  from tb where AGE>0 group by mc) b
where a.mc=b.mc and a.age=b.age and exists(select 1 from tb where mc=a.mc and age=a.age and id>a.id)

[you_tube]

select * from tb a
where not exists(select 1 from tb where mc = a.mc and id >a.id and age >a.age)

[sdhdy]

create table tb(id int,mc varchar(5),age int)
go
insert tb select 1 ,'A' ,5 
insert tb select 2 ,'A' ,7 
insert tb select 3 ,'A' ,-8 
insert tb select 4 ,'A', 5 
insert tb select 5 ,'B' ,1 
insert tb select 6 ,'B' ,3 
insert tb select 7 ,'B' ,-5 
insert tb select 8 ,'C' ,4 
insert tb select 9 ,'C' ,3 
insert tb select 10 ,'C' ,3 
insert tb select 11 ,'D' ,10 
--modify
select  a.* from tb a,
(select mc,min(AGE) AGE  from tb where AGE>=0 group by mc) b
where a.mc=b.mc and a.age=b.age and not exists(select 1 from tb where mc=a.mc and age=a.age and id>a.id)
/*
id          mc    age         
----------- ----- ----------- 
4           A     5
5           B     1
10          C     3
11          D     10
*/

[sdhdy]

[Quote=引用 1 楼 sdhdy 的回复:]
SQL codeselect a.* from tb a,
(select mc,min(AGE) AGE from tb where AGE>0 group by mc) b
where a.mc=b.mc and a.age=b.age and exists(select 1 from tb where mc=a.mc and age=a.age and id>a.id)
[/Quote]
这个漏了个not.

[qqhmitzk]

create table ta
([id] int,
 Mc char(1),
age int
)
go
insert into ta 
select 1,'A',5
union all
select 2,'A',7
union all
select 3,'A',-8
union all
select 4,'B',5
union all
select 5,'B',1
union all
select 6,'B',3
union all
select 7,'B',-5
union all
select 8,'C',4
union all
select 9,'C',3
union all
select 10,'C',3
union all
select 11,'D',10
---
select max(id) [id],age from ta where age in (select  min(age)  from  ta where  age>=0  group by mc) group by age order by id
/*
id          age         
----------- ----------- 
4           5
5           1
10          3
11          10

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

[casper1]

[Quote=引用 1 楼 sdhdy 的回复:]
SQL codeselect a.* from tb a,
(select mc,min(AGE) AGE from tb where AGE>0 group by mc) b
where a.mc=b.mc and a.age=b.age and exists(select 1 from tb where mc=a.mc and age=a.age and id>a.id)
[/Quote]

执行结果不正确啊！！！

[sdhdy]

[Quote=引用 6 楼 casper1 的回复:]
引用 1 楼 sdhdy 的回复:
SQL codeselect a.* from tb a,
(select mc,min(AGE) AGE from tb where AGE>0 group by mc) b
where a.mc=b.mc and a.age=b.age and exists(select 1 from tb where mc=a.mc and age=a.age and id>a.id)


执行结果不正确啊！！！
[/Quote]
看第二个。

[casper1]

[Quote=引用 3 楼 sdhdy 的回复:]
SQL codecreate table tb(id int,mc varchar(5),age int)
go
insert tb select 1 ,'A' ,5
insert tb select 2 ,'A' ,7
insert tb select 3 ,'A' ,-8
insert tb select 4 ,'A', 5
insert tb select 5 ,'B' ,1
insert tb select 6 ,'B' ,3
insert tb select 7 ,'B' ,-5
insert tb select 8 ,'C' ,4
insert tb select 9 ,'C' ,3
insert tb select 10 ,'C' ,3
insert tb select 11 ,'D' ,10
--modify
selec…
[/Quote]

正确，谢谢

[qqhmitzk]

drop table ta
go
create table ta
([id] int,
 Mc char(1),
age int
)
go
insert into ta 
select 1,'A',5
union all
select 2,'A',7
union all
select 3,'A',-8
union all
select 4,'A',5
union all
select 5,'B',1
union all
select 6,'B',3
union all
select 7,'B',-5
union all
select 8,'C',4
union all
select 9,'C',3
union all
select 10,'C',3
union all
select 11,'D',10
---
select max(id) [id],mc,age from ta a where age in (select  min(age) from  ta b where  age>=0 and a.mc=b.mc   group by mc)
group by mc,age order by id
/*

id          mc   age         
----------- ---- ----------- 
4           A    5
5           B    1
10          C    3
11          D    10
*/