我写了下面的函数。更多细节请参考函数中的注解。
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。
1条答案
按热度按时间wd2eg0qa1#
注意,解释和参数没有任何关系。如果要在逗号分隔的列表中查找元素数:假设这是针对sql server的,下面是一种查找计数的方法。
因此,您也可以对其他2个进行相同的操作,您可以找到insert的值。