SQL Server 2008中标量函数的帮助

Question

我需要帮助处理这个标量值函数。

我想做的是返回我在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

- Mickel

---

+1：使用表别名非常好，但请不要在SO上发布制表符分隔的代码 - 这确实会破坏输出并使其更正成为一种痛苦。有关markdown语法的更多信息，请参见：http://stackoverflow.com/editing-help - OMG Ponies

Answer 1

我已经点赞了你的问题，但我不喜欢任何答案。你应该把你的代码改成内联表值函数。每次执行这段代码都会被视为过程性的，而且只是一个单一的查询。

我在这篇文章中写到了它：http://msmvps.com/blogs/robfarley/archive/2009/12/05/dangers-of-begin-and-end.aspx，但我会快速解释一下你应该怎么做：

CREATE FUNCTION GetLpivMax  
( 
    -- Add the parameters for the function here 
    @ItemId int, 
    @ListPropertyId int 
) 
RETURNS TABLE AS 
RETURN (
SELECT MAX(lpiv.Value) as LpivMax
    FROM ListPropertyItemValues lpiv 
    JOIN ListPropertyItems lpi ON lpi.Id = lpiv.ListPropertyItemId  
    JOIN ListProperties lp ON lp.Id = lpi.ListPropertyId 
    JOIN Items i ON i.Id = lpiv.ItemId 
   WHERE lp.id = @ListPropertyId  
     AND i.id = @ItemId 
GROUP BY lp.id, i.id 
);

现在可以像这样使用它：

SELECT ip.*, m.LpivMax
FROM ItemsAndProperties ip
CROSS APPLY
dbo.GetLpivMax(ip.ItemID, ip.ListPropertyID) m
;

如果您不想从数据集中删除行，可以使用OUTER APPLY。您可以添加额外的聚合函数，如果您不引用它们，它们将被完全忽略。但最重要的是，查询优化器会简化您的查询，所以如果它可以避免执行大量工作，它就会这样做。

希望这可以帮助您...

- Rob Farley

Answer 2

使用：

DECLARE @output INT

BEGIN
  WITH U AS (
    SELECT i.Id AS ItemId,
           lpiv.Value,
           lp.Id AS ListPropertyId
      FROM ListPropertyItemValues lpiv
      JOIN ListPropertyItems lpi ON 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

SELECT @output = MAX(u.value)将值赋给@output变量，然后使用RETURN语法返回它。根据您的需要，另一种选择是设置一个输出参数。

我还将您的JOIN语法从ANSI-89转换为ANSI-99。这种语法与旧语法具有相同的性能，但更好地支持LEFT JOIN，并且在其他数据库上得到广泛支持（如果需要，更具可移植性）。

另外，请尝试：

  SELECT @output = MAX(lpiv.Value)
    FROM ListPropertyItemValues lpiv
    JOIN ListPropertyItems lpi ON lpi.Id = lpiv.ListPropertyItemId 
    JOIN ListProperties lp ON lp.Id = lpi.ListPropertyId
    JOIN Items i ON i.Id = lpiv.ItemId
   WHERE lp.id = @ListPropertyId 
     AND i.id = @ItemId
GROUP BY lp.id, i.id

我认为你不需要使用CTE...

- OMG Ponies

---

+1 不过，如果 BEGIN 是函数的开始，那么 @output 变量的声明不应该放在它后面吗？ - David Hall

@David：那只是个人口味的问题。@output变量的范围在BEGIN中是可访问的，我只是出于表明正在发生的事情的目的，将我的变量声明放在了BEGIN之外。 - OMG Ponies

谢谢Ponies，但现在我遇到了错误 An expression of non-boolean type specified in a context where a condition is expected, near 'lpi'.。我已经用你建议的代码更新了我的第一篇帖子，你能看出问题在哪里吗？ - Mickel

@Mickel：已修复 - 我在 LISTPROPERTYITEMS 连接中写成了 ON lpi lpi.id ...。 - OMG Ponies

Answer 3

将以下代码添加到您的程序中，它应该可以正常工作：

将此代码添加到开头（在BEGIN之后）

DECLARE @output int

把这个添加到结尾（在END前面）。

RETURN @output

把它们组合起来就像这样：

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

- Gabriel McAdams

Answer 4

SCALAR VALUED FUNCTION EXAMPLE BY RAJESH

CREATE FUNCTION GETMYDATE(@YEAR INT)
RETURNS DATETIME 
AS
BEGIN
    DECLARE @DATE DATETIME
    IF @YEAR>2000
        SET @DATE=GETDATE()
    ELSE
        SET @DATE=NULL

    RETURN @DATE
END

- rajesh kumar jena