谢谢帮忙写一个函数!!

[koukoujiayi]

表
f1 id flag
811 1 0
811 2 1
811 3 0
811 4 1
900 5 0
900 6 0
900 7 0

输入811想得到id是 2
判断方法是:首先在flag中找1,如果有1,则取第一个1,即id升序排序的第一个1。

输入900想得到id是 5
判断方法是:首先在flag中找1,如果没有1,则找0，取第一个0,即id升序排序的第一个0。

先谢谢了！！

[koukoujiayi]

谢谢1楼!!在线等!!

[rucypli]

create function test(@f int)
returns  int
as
begin
select top 1 @f=id
from tb
where f1=@f 
order by flag desc
return @f
end

[rucypli]

create function test(@f int)
returns int
as
begin
select top 1 @f=id
from tb
where f1=@f
order by flag desc,id asc
return @f
end

[xiaoliaoyun]

CREATE TABLE testA (f1  INT, id INT, flag BIT)
INSERT INTO testA VALUES(811,    1,      0 )
INSERT INTO testA VALUES(811,    2,      1 )
INSERT INTO testA VALUES(811,    3,      0 )
INSERT INTO testA VALUES(811,    4,      1 )
INSERT INTO testA VALUES(900,    5,      0 )
INSERT INTO testA VALUES(900,    6,      0 )
INSERT INTO testA VALUES(900,    7,      0 )
SELECT * FROM testA
GO

CREATE FUNCTION test_fun
(
    @f1     INT
)
RETURNS INT
AS
BEGIN
    DECLARE @id     INT
    
    SELECT TOP 1 @id = id FROM dbo.testA WHERE f1 = @f1 ORDER BY flag DESC,id
	
    RETURN @id
END
GO 

SELECT dbo.test_fun(811)
SELECT dbo.test_fun(900)

[bancxc]

有点慢 呵呵 不知道对不对

if object_id('tb') is not null
	drop table tb
go
create table tb(f1 int,id int,flag int)
go

--生成测试数据
insert into tb select 
811,1,  0 union all select 
811,2,  1 union all select  
811,3,  0 union all select  
811,4,  1 union all select  
900,5,  0 union all select  
900,6,  0 union all select  
900,7,  0 
go


if object_id('getID') is not null
	drop function dbo.getID
go

create function dbo.getID(@Num int)
	returns int
as
begin 
	declare @id as int
	if @num = 811 
	    select top 1 @id=id from tb where f1=@num and flag=1 order by id 
	
	if @num =900
	    select top 1 @id=id from tb where f1=@num  order by flag  desc,id

	return @id

end 
go

select distinct dbo.getID(f1) from tb

/*
----------- 结果
2
5
*/

[lunzi028]

declare @table  table (f1  int,  id  int,   flag int)

insert into @table 
select 811 ,  1  , 0 union all
select 811 ,  2  , 1 union all
select 811 ,  3  , 0 union all
select 811 ,  4  , 1 union all
select 900 ,  5  , 0 union all
select 900 ,  6  , 0 union all
select 900 ,  7  , 0 

declare @f1 int 
set @f1=811

select * from (
select *,row_number()over(partition by f1 order by flag desc,id asc) as rn
from  @table ) x
where rn=1  and f1=@f1

[7761098]

declare @tb table
(
f1 int,
id int,
flag int
)

insert into @tb
select 811  ,  1,      0 union all
select 811  ,  2 ,     1 union all
select 811  ,  3 ,     0 union all
select 811  ,  4 ,     1 union all
select 900   , 5 ,     0 union all
select 900  ,  6 ,     0 union all
select 900  ,  7  ,    0 

declare @i int
set @i = 811
select case when exists(select top 1 id from  @tb where f1 = @i and flag = 1 order by id) 
then (select top 1 id from  @tb where f1 = @i and flag = 1 order by id)
else (select top 1 id from  @tb where f1 = @i and flag = 0 order by id) end

我最蠢

[koukoujiayi]

测试了3楼,好像不对!!
5,6,7楼,将811,900写死了,这样不行!!

[-狙击手-]

CREATE TABLE testA (f1  INT, id INT, flag BIT)
INSERT INTO testA VALUES(811,    1,      0 )
INSERT INTO testA VALUES(811,    2,      1 )
INSERT INTO testA VALUES(811,    3,      0 )
INSERT INTO testA VALUES(811,    4,      1 )
INSERT INTO testA VALUES(900,    5,      0 )
INSERT INTO testA VALUES(900,    6,      0 )
INSERT INTO testA VALUES(900,    7,      0 )
 
GO

CREATE FUNCTION test_fun
(
    @f1     INT
)
RETURNS table
AS
return(
    SELECT TOP 1 id FROM dbo.testA WHERE f1 = @f1 ORDER BY flag DESC,id
)
 
GO 

SELECT * from dbo.test_fun(811)
SELECT * from dbo.test_fun(900)

drop table testA
drop function test_fun

/*
id
-----------
2

(1 行受影响)

id
-----------
5

(1 行受影响)
*/

[bancxc]

你输入811和900的取法是否一样，如果一样的话 我那个函数就不用判断了

create function dbo.getID(@Num int)
returns int
as
begin
declare @id as int
select top 1 @id=id from tb where f1=@num order by flag desc,id

return @id

end
go

[-狙击手-]

[Quote=引用 9 楼 koukoujiayi 的回复:]
测试了3楼,好像不对!!
5,6,7楼,将811,900写死了,这样不行!!
[/Quote]

那个是参数，你可以任意指定的

[bancxc]

改了一下

if object_id('tb') is not null
	drop table tb
go
create table tb(f1 int,id int,flag int)
go

--生成测试数据
insert into tb select 
811,1,  0 union all select 
811,2,  1 union all select  
811,3,  0 union all select  
811,4,  1 union all select  
900,5,  0 union all select  
900,6,  0 union all select  
900,7,  0 
go


if object_id('getID') is not null
	drop function dbo.getID
go

create function dbo.getID(@Num int)
	returns int
as
begin 
	declare @id as int 
	select top 1 @id=id from tb where f1=@num  order by flag  desc,id asc

	return @id

end 
go

SELECT distinct f1,dbo.getID(f1) id from tb
/*
f1          id          
----------- ----------- 
811         2
900         5
*/

 

[koukoujiayi]

4楼基本对了!!十分感谢!!
因为要链接一个表,如果输入一个不存在的f1, 返回还是有点问题!!
希望返回空!!
谢谢帮忙!!

[koukoujiayi]

5,6,7 楼抱歉!!
没看清!!
我会一个一个测试!!
十分感谢!!

[bancxc]

重新定义一个变量吧 看我的13楼

[koukoujiayi]

谢谢诸位!!
最后用13楼!!
再一次感谢13楼!!