如何生成3位二进制的可能二进制组合?

iyzzxitl  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(662)

我写了下面的函数。更多细节请参考函数中的注解。

DROP FUNCTION dbo.[combination]
 GO
CREATE FUNCTION [dbo].[combination] (
    @Fruits VARCHAR(200),  --comma separated value
    @Vegetables VARCHAR(200),
    @Junkfood NVARCHAR(max)
) RETURNS @FinalTable table (
    result int

)
AS BEGIN
    DECLARE @countisFruit int    DECLARE @countisVegetable int     DECLARE @countisJunkfood int DECLARE @test int
    DECLARE @isFruitAvailable int DECLARE @isVegetableAvailable int DECLARE @isJunkFoodAvailable int
    SELECT @isFruitAvailable = LEN(REPLACE(@Fruits, CHAR(44), ''))
    SELECT @isVegetableAvailable = LEN(REPLACE(@Vegetables, CHAR(44), ''))
    SELECT @isJunkFoodAvailable = LEN(REPLACE(@Junkfood, CHAR(44), ''))

    BEGIN

    IF @countisFruit > 0
        BEGIN 
        SELECT @isFruitAvailable = 1
        END
    IF @countisVegetable > 0
        BEGIN 
        SELECT @isVegetableAvailable = 1
        END
    IF @countisJunkfood > 0
        BEGIN 
        SELECT @isJunkFoodAvailable = 1
        END

    --<.......Here I want to have if conditions based on what kinds of food is available 
    -- i can achieve it  by having if conditions like 
    --if (@countisFruit > 0 AND @countisVegetable >0 AND @countisJunkfood> 0 )
    --if (@countisFruit = 0 AND @countisVegetable >0 AND @countisJunkfood> 0 ) and so on possible conditions 
    -- I am thinking reading from @count<X> in every if statement might slow down my function 
    --- so I can represent possible combinations in binary  like 000, 101, 111 ... for that I will have to do some logic on @countisFruit and rest @count<X> variables.
    -- Please suggest how can i achieve that

RETURN
END
GO

请建议我如何在sql函数中实现这些组合或类似的东西。我正在尝试优化这个函数中涉及的许多ifs。

wd2eg0qa

wd2eg0qa1#

注意,解释和参数没有任何关系。如果要在逗号分隔的列表中查找元素数:假设这是针对sql server的,下面是一种查找计数的方法。

declare @input1 varchar(555) ='23,ABC,67, PQR5,267,XYZ236,Emp1, Emp2', @countInput1 int

SELECT @countInput1 = SUM(LEN(@input1) - LEN(replace(@input1, ',', '')) +1)  
SELECT @countInput1 as countInput1

因此,您也可以对其他2个进行相同的操作,您可以找到insert的值。

相关问题