主页 / dba / 问题 / 135213
Accepted
Gary Czychi
Asked: 2016-04-14 12:25:40 +0800 CST

非常棘手：无法更新顺序列表 SQL-92 (Filemaker)

  • 772

我有一个非常简单的问题，但解决方案非常棘手。我试图解决这个问题几个月：

有一列“SortID”需要使用从 1 开始的连续整数进行更新。该列可能如下所示（没有 ORDER BY）：

SELECT SortID FROM Beleg

2
3
.5
4
5
9
10
10.000001
10.000002
11
13

并且需要看起来像这样。

2
3
1
4
5
6
7
8
… and so on

我已经尝试了我能想到的一切，但没有运气。

我正在使用 Filemaker 中的执行 SQL 函数，我可以只使用普通的 SQL-92，没什么特别的。

例如，以下技巧都不起作用（例如：用序列号更新现有行……）。都产生语法错误：

ALTER TABLE MyTable ADD MyColumn int IDENTITY(1, 2) NOT NULL

和：

ALTER TABLE MyTable ADD MyColumn uniqueidentifier NOT NULL
    CONSTRAINT DF_MyTable_MyColumn
        DEFAULT (NEWSEQUENTIALID())

和（不允许变量）：

DECLARE @id INT 
SET @id = 0 
UPDATE X
SET @id = cn = @id + 1 
GO

和（ROW_NUMBER（）不起作用）：

update T
set cn = rn
from (
    select cn,
        row_number() over(order by (select 1)) as rn
    from TableX
    ) T

和（这很接近，但没有）：

update invoice set RecNo = (next value for seq_invoice_recno)
where invoiceid in (select top 100000 invoiceid from invoice where RecNo is null 
order by invoiceId)

. . . 但是，我更接近了一点：我可以使用 Filemaker 函数“RecordNumber”，它为每一行返回一个序号。使用此语句，我可以获得正确的顺序并获得该行的正确整数：

SELECT SortID, RecordNumber FROM Beleg
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID

.5  1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9
10  10
10.00001    11
10.00002    12

但是，当我尝试写入值时，一旦包含 ORDER BY 语句，就会出现错误：

UPDATE Beleg SET SortID = RecordNumber
WHERE YEAR ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID

=> 错误

只是，当我删除 ORDER BY 时，值被写入——但是顺序错误！

UPDATE Beleg SET SortID = RecordNumber
WHERE YEAR ( Valuta ) = 2016
AND Ursprungskonto = 1210

1
2
3
4
5
6
7
8
… and so on

SortID“1”应该写入第 3 行，“2”写入第一行，“3”写入第二行，如下所示：

2
3
1
4
5
6
7
8
… and so on

如此接近，但仍然没有运气。

有人有什么主意吗？

非常感谢！

加里

update sorting

2 个回答

  1. Best Answer

    这是另一种方法，但是，它的性能可能比其他建议更差：

    UPDATE
  Beleg
SET
  SortID = (
    SELECT
      COUNT(*)
    FROM
      Beleg AS Beleg2
    WHERE
      Year ( Beleg2.Valuta ) = Year ( Beleg.Valuta )
      AND Beleg2.Ursprungskonto = Beleg.Ursprungskonto
      AND Beleg2.SortID <= Beleg.SortID
  )
WHERE
  Year ( Valuta ) = 2016
  AND Ursprungskonto = 1210
;

    从好的方面来说，此方法不仅可以在单个步骤中重置SortID值，如果需要，它还可以一次对多个(Year(Valuta), Ursprungskonto)子集执行此操作。也就是说，您可以在主 WHERE 子句中为任一属性指定多个值，例如：

    WHERE
  Year ( Valuta ) BETWEEN 2011 AND 2016
  AND Ursprungskonto IN (1210, 2101, 1012)

    或者甚至只是完全删除 WHERE 子句——每个子集都将与其他子集分开重新编号，所有这些都使用单个查询。

    重要说明：此方法假定SortID值在任何单个(Year(Valuta), Ursprungskonto)子集中是唯一的。

    • 4

  2. 如果您能够创建一个表，并且更新允许您使用子选择的结果（所有有效的 SQL92 代码），您应该能够执行以下多步骤过程：

    CREATE TABLE sorting (sid numeric(10,10), rn int);

INSERT INTO sorting (sid, rn)
SELECT SortID, RecordNumber FROM Beleg
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210
ORDER BY SortID;

UPDATE Beleg SET SortID = (SELECT rn FROM sorting WHERE sid=Beleg.SortID)
WHERE Year ( Valuta ) = 2016
AND Ursprungskonto = 1210;

DROP TABLE sorting;

    您可能需要将 rn 转换为数字，或使用数字以外的数据类型（例如double precision）。

    • 2

相关问题