PostgreSQL，高级交换

问题描述

我在交换同一表的行中的某些数据时得到了一些帮助。

不幸的是，我无法在实践中应用这些解决方案，因为我所代表的问题太弱了，因此使用所提供的解决方案无法获得预期的结果。

为此，我改进了示例，使其非常易于使用，并同时尝试了我的具体情况，希望此帖子不会被视为重复或令人反感。

I get some help on swapping certain data in rows of same table here.
Unfortunately I can't apply those solution in practice because I represent a problem too weak so with offered solutions I can't get expected results.
For that I improve examples and make it very easy to use and for try at the same time more likely my concrete situation with hope that this post will not be treated as duplicate or offensive.

创建表：

DROP TABLE IF EXISTS kalksad1;

CREATE TABLE kalksad1(
kalk_id     int PRIMARY KEY,
brkalk      integer, 
brred       integer, 
description text
);

INSERT INTO kalksad1 VALUES
  (12, 2, 5, 'text index 12 doc 2 row 5'),
  (26, 2, 1, 'text index 26 doc 2 row 1'),
  (30, 2, 2, 'text index 30 doc 2 row 2'),
  (32, 4, 1, 'text index 32 doc 4 row 1'),
  (36, 1, 1, 'text index 36 doc 1 row 1'),
  (37, 1, 2, 'text index 37 doc 1 row 2'),
  (38, 5, 1, 'text index 38 doc 5 row 1'),
  (39, 5, 2, 'text index 39 doc 5 row 2'),
  (42, 2, 3, 'text index 42 doc 2 row 3'),
  (43, 2, 4, 'text index 43 doc 2 row 4'),
  (46, 3, 1, 'text index 46 doc 3 row 1'),
  (47, 3, 2, 'text index 47 doc 3 row 2');

需要什么？

要

这两个查询都在外部通过程序定义。

例如，在程序中， brred和 brkalk都在外部定义。目的是将brkalk = 2，brred = 3。

这意味着我们只应在brkalk = 2时在行中交换brred值。

To make a query which will swap numbers only in column 'brred' of same 'brkalk'.
Both, 'brred' and 'brkalk' are defined externally, through program.
For example purpose we will take brkalk=2, brred=3.
That mean we should swap brred value only in rows WHEN brkalk=2.

以下是两个提供的解决方案，可以作为参考。

两种解决方案都可以使用，如果它们有用的话。

第一个是因为它可以交换行而不管顺序和距离如何，第二个是因为它只与最常见的需求上下交换第一行。
第二个解决方案的问题是我不知道它交换了什么，而是交换了第一行和最后一行，而不是交换了第3和2行。

应该修复。

Here are two offered solution's which may be taken as reference.
Both solutions can be useful if they would work.
First one because it can swap rows no matter of order and distance and second because it swap with only first row upper or lower what is most common need.Problem with second solution is that I don't know what it swaps but swaps first and last row instead of row 3 and 2.
That should be repaired.

在新情况下，第一个查询根本不起作用，所以我希望有人能修复它。对于通过外部参数交换方向（例如交换第4行和第1行）而言，它对于交换行很有用。

First query don't work at all in new circumstances so I would like if anyone could repair it. It can be useful for swapping rows no matter of "direction" by external arguments, say swap rows 4 and 1.

当我说交换行时，请澄清一下我的意思是只交换属于相同 brkalk（在本例中为2）的 brred列中的值。

Just to clarify, when I say "swap row" I mean to swap ONLY values in 'brred' column which belongs to same 'brkalk' (in this case 2).

第一个查询：

    UPDATE kalksad1 dst 
        SET brred=src.brred 
        FROM kalksad1 src 
        WHERE src.brkalk='2' 
        AND dst.kalk_id IN(2,3) 
        AND src.kalk_id IN(2,3) 
        AND dst.kalk_id <> src.kalk_id;

第二次查询

    WITH cte1 AS (
        SELECT row_number() OVER(ORDER BY kalk_id ASC) AS row_num, kalk_id, brred 
        FROM kalksad1
        WHERE kalk_id >= 3 ORDER BY kalk_id LIMIT 2
        ) 
    UPDATE kalksad1 AS t 
        SET brred = COALESCE(c2.brred, t.brred) 
        FROM cte1 AS c1 
        LEFT OUTER JOIN cte1 AS c2 ON c2.row_num <> c1.row_num 
        WHERE t.kalk_id = c1.kalk_id AND brkalk='2';

要查看数据，最好使用：

To view data it is best to use:

SELECT * FROM kalksad1 WHERE brkalk='2' ORDER BY brred;

我希望有人根据说明的需求修复上层查询以使其可行，或者提供新的解决方案

I would like that someone repair upper queries to become workable according to described needs or offer new solution which may be usable for that kind of swapping.

因此，多亏了Roman和wildplasser，我得到了这个...

So, thanks to Roman and wildplasser I get this...

Private Function swap_row(ByVal doc_num As Integer, ByVal src_row As Integer, ByVal dest_row As Integer) As Integer

    Dim affected As Integer = 0
    Dim conn As NpgsqlConnection = getConnection()
    Dim t As NpgsqlTransaction = conn.BeginTransaction()

    Using cmd As New NpgsqlCommand( _
        "UPDATE " & myKalkSadTable & " AS dst SET brred = src.brred " & _
        "FROM " & myKalkSadTable & " AS src " & _
        "WHERE(src.brkalk = " & doc_num.ToString & ") " & _
        "AND dst.brkalk = " & doc_num.ToString & " " & _
        "AND dst.brred IN (" & src_row.ToString & "," & dest_row.ToString & ") " & _
        "AND src.brred IN (" & src_row.ToString & "," & dest_row.ToString & ") " & _
        "AND src.kalk_id <> dst.kalk_id", conn)

        affected = CInt(cmd.ExecuteNonQuery())
        cmd.Dispose()
    End Using

    If affected = 2 then t.Commit()
    t.Dispose()
    conn.Close()
    conn.Dispose()

    Return affected
End Function

Private Sub DataGridView2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView2.KeyDown

    If e.Control And e.KeyCode = Keys.Left Then
        swap_row(kalkbr, selected_row, selected_row - 1)
        Refreshlist(kalkbr)
    End If

    If e.Control And e.KeyCode = Keys.Right Then
        swap_row(kalkbr, selected_row, selected_row + 1)
        Refreshlist(kalkbr)
    End If

... etc ...

...etc ...

推荐答案

对于第一个，您必须同时在dst和src上过滤brkalk：

for first one you have to filter brkalk on both dst and src:

update kalksad1 as dst set
    brred = src.brred 
from kalksad1 as src 
where
    src.brkalk = 2 and dst.brkalk = 2 and
    dst.brred in (2,3) and
    src.brred in (2,3) and
    src.kalk_id <> dst.kalk_id;

我认为第二个过于复杂，我在创建时就创建了它以为您想交换具有确切ID和下一个ID的行

I think second one is too complicated, I've created it when I thought that you want to swap row with exact ID and next one

这篇关于PostgreSQL，高级交换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！