sql - View 、存储过程或表定义函数

Question

此查询按原样执行得很好。但是，SQL Management Studio 不会将其保存为 View ，因为我定义了一个变量。

DECLARE @HighestTransaction intSET @HighestTransaction = (SELECT     MAX(CardID)                            FROM          dbo.Transactions)SELECT Uploads.*, Transactions.*FROM   Uploads LEFT OUTER JOIN       dbo.Transactions ON dbo.Uploads.Code = dbo.Transactions.CardIDWHERE  (Uploads.Code > CASE WHEN           @HighestTransaction IS NULL THEN -1  ELSE @HighestTransaction END)

我还没有真正使用过存储过程或用户定义的函数，所以我不确定执行此操作的最佳方法。或者，如果有更好的写法，我也愿意接受建议。

Answer 1

• 您可以将 MAX 隔离成 CTE
• 没有 GROUP BY 的 MAX 给出一行，所以你在那里使用 ISNULL

有点像...

WITh cHighestCard AS
(
   SELECT ISNULL(MAX(CardID), -1) AS MaxCard FROM dbo.Transactions
)
SELECT     STAUpload.*, Transactions.*
FROM         dbo.STAUpload LEFT OUTER JOIN
                      dbo.Transactions ON dbo.STAUpload.Code = dbo.Transactions.CardID
WHERE     dbo.STAUpload.Code > MaxCard --edit, error spotted by martin

编辑:不需要 CTE:它混合了集合和标量。糟糕。

SELECT     STAUpload.*, Transactions.*
FROM         dbo.STAUpload LEFT OUTER JOIN
                      dbo.Transactions ON dbo.STAUpload.Code = dbo.Transactions.CardID
WHERE     dbo.STAUpload.Code >
            (SELECT ISNULL(MAX(CardID), -1) AS MaxCard
                   FROM dbo.Transactions)