多了一个字段 status
表中:
num numType status num为int numType为char
2 a y
3 a n
6 a n1 b y
5 b n
2 b n4 c n
3 c n
5 c n想得到的结果是
num numType status
3 a n
2 b n
3 c n即输出在不同的numType中status不为Y的最小的值
表中:
num numType status num为int numType为char
2 a y
3 a n
6 a n1 b y
5 b n
2 b n4 c n
3 c n
5 c n想得到的结果是
num numType status
3 a n
2 b n
3 c n即输出在不同的numType中status不为Y的最小的值
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'
from tb k
where not exists(select * from tb where k.numType=numType and k.id>ID and status<>'Y')
and status<>'Y'
from 表
group by numType, status
where status ='n'
----------------------------------------------------------------
-- 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 行)
*/
where not exists
(
select 1 from tb where numType=a.numType
and NUMTYPE=a.NUMTYPE
and STATUS<>'Y'
)
a.STATUS<>'Y'