我需要帮助处理这个标量值函数。
我想做的是返回我在max(Value) AS MaxValue
行中得到的值。
这个查询可以工作,并且只有在ItemId
和ListPropertyId
存在时才会返回一个值,但我无法创建一个函数来实现它。
CREATE FUNCTION GetLpivMax
(
-- Add the parameters for the function here
@ItemId int,
@ListPropertyId int
)
RETURNS int
AS
BEGIN
DECLARE @output INT;
WITH U AS (
SELECT i.Id AS ItemId,
lpiv.Value,
lp.Id AS ListPropertyId
FROM ListPropertyItemValues lpiv
JOIN ListPropertyItems lpi ON lpi lpi.Id = lpiv.ListPropertyItemId
JOIN ListProperties lp ON lp.Id = lpi.ListPropertyId
JOIN Items i ON i.Id = lpiv.ItemId)
SELECT @output = MAX(u.value)
FROM U u
WHERE u.listpropertyid = @ListPropertyId
AND u.itemid = @ItemId
GROUP BY u.listpropertyid, u.itemid
RETURN @output
END
GO