更新ROW_NUMBER()结束

Question

我得到错误"窗口函数只能出现在SELECT或ORDER BY子句中。"你能帮帮我吗?

我知道已经有一些解决这个问题的方法，但我不知道如何改变它。我是SQL的初学者。

编辑:我需要在update case中用正确的顺序更新列[BREAG_GUEST_SEQ]。每个[BREAG_BGL_ID]都有自己的序列。

UPDATE G1
SET 
G1.[BREAG_BGL_ID] = CASE WHEN G1.[BREAG_BGL_ID] <> G2.GROUP_ID THEN G2.GROUP_ID ELSE G1.[BREAG_BGL_ID] END
,G1.[BREAG_GUEST_SEQ] = CASE WHEN G1.[BREAG_BGL_ID] <> G2.GROUP_ID THEN ROW_NUMBER() OVER(ORDER BY G1.[BREAG_BRE_ID] DESC) + (SELECT ISNULL(MAX(BREAG_GUEST_SEQ), 0) FROM BOS_RESADDGUEST
                   WHERE DATEPART(YEAR, BREAG_DATEFROM) = DATEPART(YEAR, GETDATE()) AND BREAG_BGL_ID = G2.GROUP_ID)
ELSE G1.[BREAG_GUEST_SEQ] END
FROM 
BOS_RESADDGUEST G1
INNER JOIN (
SELECT 
[BRE_ID]
,MAX([BRE_DATEFROM]) AS DATEFROM
,MAX([BRE_DATETO]) AS DATETO
,MAX(BGL_ID) AS GROUP_ID
FROM 
BOS_RESERVATION
LEFT JOIN BOS_UNIT_LIST ON BUL_ID = BRE_UNIT_ID
LEFT JOIN BOS_UNITTYPE_LIST ON BUL_UNITTYPE_ID = BUT_ID
LEFT JOIN BOS_GROUP_LIST ON BGL_ID = BUT_GROUP_ID
WHERE
BRE_ID = @DIALOG_BRE_ID
GROUP BY [BRE_ID]
) AS G2
ON G2.[BRE_ID] = G1.[BREAG_BRE_ID]

Answer 1

这没有经过测试，也没有提供预期的结果或数据结构。然而，这个问题非常清楚(正如SMor在评论中指出的那样)。这不是最好的代码，缩进是可怕的(我希望它是可读的左或右滚动在这里的SO)。

你很可能不得不编辑我在最后的更新语句中加入的内容，因为没有你的进一步信息，我不得不猜测连接谓词应该是什么。我相信从现在开始你就能明白了。

; WITH cte AS
(
SELECT
[BREAG_BRE_ID]
, CASE 
WHEN x.[BREAG_BGL_ID] <> x.GROUP_ID THEN x.GROUP_ID 
ELSE x.[BREAG_BGL_ID] 
END As NewBREAGBGLID
, CASE 
WHEN x.[BREAG_BGL_ID] <> x.GROUP_ID THEN ROW_NUMBER() 
OVER (ORDER BY x.[BREAG_BRE_ID] DESC) + 
(
SELECT ISNULL(MAX(BREAG_GUEST_SEQ), 0) 
FROM BOS_RESADDGUEST
WHERE DATEPART(YEAR, BREAG_DATEFROM) = DATEPART(YEAR, GETDATE()) 
AND BREAG_BGL_ID = x.GROUP_ID
)
ELSE x.[BREAG_GUEST_SEQ] 
END AS NewBREAGGUESTSEQ
FROM
(
SELECT g2.*, g1.[BREAG_BGL_ID], g1.[BREAG_BRE_ID], g1.[BREAG_GUEST_SEQ]
FROM 
BOS_RESADDGUEST G1
INNER JOIN 
(
SELECT 
[BRE_ID]
,MAX([BRE_DATEFROM]) AS DATEFROM
,MAX([BRE_DATETO]) AS DATETO
,MAX(BGL_ID) AS GROUP_ID
FROM 
BOS_RESERVATION
LEFT JOIN BOS_UNIT_LIST ON BUL_ID = BRE_UNIT_ID
LEFT JOIN BOS_UNITTYPE_LIST ON BUL_UNITTYPE_ID = BUT_ID
LEFT JOIN BOS_GROUP_LIST ON BGL_ID = BUT_GROUP_ID
WHERE
BRE_ID = @DIALOG_BRE_ID
GROUP BY [BRE_ID]
) AS G2
ON G2.[BRE_ID] = G1.[BREAG_BRE_ID]
) AS x
)
UPDATE 
[BREAG_BGL_ID] = c.NewBREAGBGLID
, [BREAG_GUEST_SEQ] = c.NewBREAGGUESTSEQ    
FROM BOS_RESADDGUEST br
INNER JOIN cte c ON br.[BREAG_BRE_ID] =c.[BREAG_BRE_ID]

Answer 2

您需要将ROW_NUMBER移动到派生表或CTE中。你可以直接更新，不需要重新连接.

您还可以将子查询更改为窗口MAX

还要注意，日期比较不应该对列使用函数，因为这会影响性能

UPDATE G1
SET 
G1.[BREAG_BGL_ID] = CASE WHEN G1.[BREAG_BGL_ID] <> G2.GROUP_ID THEN G2.GROUP_ID ELSE G1.[BREAG_BGL_ID] END
,G1.[BREAG_GUEST_SEQ] = CASE WHEN G1.[BREAG_BGL_ID] <> G2.GROUP_ID THEN G1.rn + G1.MaxSeq ELSE G1.[BREAG_GUEST_SEQ] END
FROM (
SELECT *,
ROW_NUMBER() OVER(ORDER BY G1.[BREAG_BRE_ID] DESC) AS rn,
ISNULL(MAX(CASE WHEN WHERE G1.BREAG_DATEFROM >= DATEFROMPARTS(YEAR(GETDATE()), 1, 1) THEN G1.BREAG_GUEST_SEQ END)
OVER (PARTITION BY G1.BREAG_BGL_ID), 0) AS MaxSeq
FROM
BOS_RESADDGUEST G1
) AS G1
INNER JOIN (
SELECT 
[BRE_ID]
,MAX([BRE_DATEFROM]) AS DATEFROM
,MAX([BRE_DATETO]) AS DATETO
,MAX(BGL_ID) AS GROUP_ID
FROM 
BOS_RESERVATION
LEFT JOIN BOS_UNIT_LIST ON BUL_ID = BRE_UNIT_ID
LEFT JOIN BOS_UNITTYPE_LIST ON BUL_UNITTYPE_ID = BUT_ID
LEFT JOIN BOS_GROUP_LIST ON BGL_ID = BUT_GROUP_ID
WHERE
BRE_ID = @DIALOG_BRE_ID
GROUP BY [BRE_ID]
) AS G2
ON G2.[BRE_ID] = G1.[BREAG_BRE_ID];