--========+++++++++++++++++++++++++++++++++++==========
--======= 每天都在进步，却依然追不上地球的自传=========
--======= By: zc_0101    At:2009-08-18 09:59:18=========
--========++++++++++++++++++++++++++++++++++++=========
--> 测试数据: T
if object_id('T') is not null drop table T
create table T (UserID varchar(8),flowNum int,Type varchar(1),status varchar(1))
insert into T
select 'jay',1,'a','Y' union all
select 'jolin',2,'a','2' union all
select 'jacket',3,'a','n' union all
select 'zhangsan',4,'a','1' union all
select 'cyong',5,'a','n' union all
select 'zhangsan',1,'b','2' union all
select 'cyong',2,'b','1' union all
select 'jay',3,'b','1' union all
select 'jolin',4,'b','n' union all
select 'jacket',5,'b','1' union all
select 'jay',1,'c','n' union all
select 'cyong',2,'c','1' union all
select 'zhangsan',3,'c','1' union all
select 'jolin',4,'c','n' union all
select 'jacket',5,'c','1'
 
----------------查询------------
--自定义函数
CREATE FUNCTION F_GET(@USERID VARCHAR(100),@TYPE VARCHAR(10))
    RETURNS @TEMP TABLE(
        USERID VARCHAR(100) ,FLOWNUM INT,[TYPE] VARCHAR(10),STATUS CHAR(1)
    )
AS
BEGIN

    IF EXISTS (SELECT 1 FROM T WHERE USERID=@USERID AND STATUS IN ('Y','2') AND [TYPE]=@TYPE) 
            RETURN
    ELSE
        BEGIN
            INSERT INTO @TEMP SELECT USERID,FLOWNUM,[TYPE],STATUS FROM 
            (
                SELECT *,ROW=ROW_NUMBER() OVER (ORDER BY FLOWNUM)  FROM T  WHERE  [TYPE]=@TYPE AND STATUS IN('1','N') 
            ) T  WHERE T.ROW=1 AND T.USERID=@USERID
        END    
    RETURN
END
--正式查询

--对于type='a'的情况
SELECT * from dbo.F_GET('jay','a')
SELECT * from dbo.F_GET('JOLIN','a')
SELECT * from dbo.F_GET('jacket','a')
/*
USERID    FLOWNUM    TYPE    STATUS
jacket    3    a    n
*/
SELECT * from dbo.F_GET('zhangsan','a')
SELECT * from dbo.F_GET('CYONG','a')


--对于type='b'的情况
SELECT * from dbo.F_GET('zhangsan','B')
SELECT * from dbo.F_GET('cyong','B')
/*
USERID    FLOWNUM    TYPE    STATUS
cyong    2    b    1
*/
SELECT * from dbo.F_GET('jay','B')
SELECT * from dbo.F_GET('jolin','B')
SELECT * from dbo.F_GET('jacket','B')



--对于type='c'的情况
SELECT * from dbo.F_GET('jay','C')
/*
USERID    FLOWNUM    TYPE    STATUS
jay    1    c    n
*/
SELECT * from dbo.F_GET('cyong','C')
SELECT * from dbo.F_GET('zhangsan','C')
SELECT * from dbo.F_GET('jolin','C')
SELECT * from dbo.F_GET('jacket','C')

--删除测试数据
drop table T
drop function F_get