在更新语句中排序 [英] order by in update statement

问题描述

我正在尝试写一条更新语句，它需要在其中有Order By.我知道我可能必须使用sub select语句，我尝试了几种方法但未成功.请帮忙
以下是我的代码

谢谢，

I am trying to write an update statement and It needs to have Order By in there. I know I might have to use the sub select statement, I tried several way and was not successful. Please help
Below is my code

Thanks,

Declare 
@row as int
Set @row = 0
Update
dbo.CaseAssignments
SET
dbo.CaseAssignments.Rank1 = @row,
@row = @row +1

FROM ((dbo.CaseAssignments INNER JOIN dbo.LawFirms 
ON dbo.CaseAssignments.FirmID = dbo.LawFirms.FirmID) 
INNER JOIN dbo.v_Lawyers 
ON (dbo.CaseAssignments.LawyerID = dbo.v_Lawyers.LawyerID) 
AND (dbo.LawFirms.FirmID = dbo.v_Lawyers.FirmID)) 
INNER JOIN dbo.Cases ON dbo.CaseAssignments.CaseID = dbo.Cases.CaseID
WHERE dbo.CaseAssignments.CaseID=144
And dbo.CaseAssignments.FormFilterPDC = 1

order by dbo.LawFirms.FirmName ASC,
dbo.CaseAssignments.Rank ASC;

推荐答案

SQL2005解决方案

Solution for SQL2005

;WITH cte AS
    (
    SELECT  [dbo].[CaseAssignments].[Rank1],
            [NEW_RANK] = (ROW_NUMBER() OVER( ORDER BY [dbo].[LawFirms].[FirmName], [dbo].[CaseAssignments].[Rank] )) - 1
    FROM    [dbo].[CaseAssignments]
    JOIN    [dbo].[LawFirms]
            ON [dbo].[CaseAssignments].[FirmID] = [dbo].[LawFirms].[FirmID]
    JOIN    [dbo].[v_Lawyers]
            ON  [dbo].[CaseAssignments].[LawyerID]  = [dbo].[v_Lawyers].[LawyerID]
                AND
                [dbo].[LawFirms].[FirmID]           = [dbo].[v_Lawyers].[FirmID]
    JOIN    [dbo].[Cases]
            ON [dbo].[CaseAssignments].[CaseID] = [dbo].[Cases].[CaseID]
    WHERE   [dbo].[CaseAssignments].[CaseID]        = 144
            AND
            [dbo].[CaseAssignments].[FormFilterPDC] = 1
    )
UPDATE  cte
    SET [Rank1] = [NEW_RANK]

在插入或更新语句.数据如何进入表中都没有关系.

You don''t use "order by" in insert or update statements. It doesn''t matter how the data goes into the table.

这篇关于在更新语句中排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！