ID Value
1 2
1 3
1 4
2 1
2 3
3 1
3 2我要写一个 function,参数是ID, fun(@ID),判断该ID对应的所有Value是否包含在(2,3,4)之中,是返回1,否返回0
select fun(1) ==> 1
select fun(2) ==> 0
select fun(3) ==> 0请问这个方法怎么写,我觉得用游标判断太复杂了,似乎可以用 All 关键字来简化,但是我不会写。
存储过程也行,能返回正确的值就可以了。
1 2
1 3
1 4
2 1
2 3
3 1
3 2我要写一个 function,参数是ID, fun(@ID),判断该ID对应的所有Value是否包含在(2,3,4)之中,是返回1,否返回0
select fun(1) ==> 1
select fun(2) ==> 0
select fun(3) ==> 0请问这个方法怎么写,我觉得用游标判断太复杂了,似乎可以用 All 关键字来简化,但是我不会写。
存储过程也行,能返回正确的值就可以了。
WHERE [VALUE] IN(2,3,4)
GROUP BY ID HAVING COUNT(DISTINCT [VALUE])=LEN('2,3,4')-REPLACE('2,3,4',',','')+1
select 1 from tb
where id=@id and values in(2,3,4)
group by id having count(distinct value)=3
)
set @result=1
--------------------
这样描述应该清楚了吧
ID=1,Value:2、3、4 in (2,3,4) ==>1
ID=2,Value:1、3 not in (2,3,4) ==>0
ID=3,Value:1、2 not in (2,3,4) ==>0
ID=4,Value:2、3 in (2,3,4) ==>1
IF EXISTS(SELECT * FROM [tb] WHERE ID = @ID AND Value NOT IN (2,3,4))
SELECT '0'
ELSE
SELECT '1'
CREATE FUNCTION FUN_MU(@ID INT)
RETURNS INT
AS
BEGIN
DECLARE @I INT
IF (SELECT COUNT(DISTINCT [VALUE]) FROM TB WHERE ID=@ID AND [VALUE] IN (2,3,4))=3
SELECT @I=1
ELSE
SELECT @I=0
RETURN @I
END
RETURNS INT
AS
BEGIN
DECLARE @I INT
IF EXISTS(SELECT 1 FROM TB WHERE ID=@ID AND [VALUE] NOT IN (2,3,4))
SELECT @I=0
ELSE
SELECT @I=1
RETURN @I
END
id,
case when id in (select id from tb where id = value) then 1 else 0 end as flag
from
tb
group by id