继续刚才那个问题

[qinjingfeng]

多了一个字段 status
表中:
num numType status num为int numType为char
2 a y
3 a n
6 a n

1 b y
5 b n
2 b n

4 c n
3 c n
5 c n

想得到的结果是
num numType status
3 a n
2 b n
3 c n

即输出在不同的numType中status不为Y的最小的值

[guguda2008]

SELECT * FROM TB T1
WHERE NOT EXISTS(
SELECT 1 FROM TB T2 WHERE T2.STATUS<>'Y' AND T2.NUM<T1.NUM AND T2.NUMTYPE=T1.NUMTYPE
) AND T1.STATUS<>'Y'

[guguda2008]

先抢SF再看写没写对

[feixianxxx]

select *
from tb k
where not exists(select * from tb where  k.numType=numType and k.id>ID and status<>'Y')
and status<>'Y'

[guguda2008]

应该是对了

[xuam]

select min(num), numType, status
from 表
group by numType, status
where status ='n'

[SQL77]

SELECT * FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB WHERE T.numType=numType  AND ID>T.ID AND status<>'Y')AND status<>'Y'

[黄_瓜]

[Quote=引用 2 楼 guguda2008 的回复:]
先抢SF再看写没写对
[/Quote]

[--小F--]

--验证鸭子的正确性
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-17 16:55:04
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([num] int,[numType] varchar(1),[status] varchar(1))
insert [tb]
select 2,'a','y' union all
select 3,'a','n' union all
select 6,'a','n' union all
select 1,'b','y' union all
select 5,'b','n' union all
select 2,'b','n' union all
select 4,'c','n' union all
select 3,'c','n' union all
select 5,'c','n'
--------------开始查询--------------------------
SELECT * FROM TB T1
WHERE NOT EXISTS(
SELECT 1 FROM TB T2 WHERE T2.STATUS<>'Y' AND T2.NUM<T1.NUM AND T2.NUMTYPE=T1.NUMTYPE
) AND T1.STATUS<>'Y'
----------------结果----------------------------
/*num         numType status 
----------- ------- ------ 
3           a       n
2           b       n
3           c       n

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

[黄_瓜]

select * from tb a
where not exists
(
select 1 from tb where numType=a.numType  
and NUMTYPE=a.NUMTYPE
and STATUS<>'Y'
)
a.STATUS<>'Y'

[SQL77]

SELECT * FROM (SELECT * FROM TB WHERE status<>'Y')AS T WHERE NOT EXISTS(SELECT 1 FROM (SELECT * FROM TB WHERE status<>'Y')AS T1 WHERE T.numType=T1.numType  AND T1.ID>T.ID)