PostgreSQL,将某些列的数据交换为两行 [英] PostgreSQL, Swap data of certain column in two rows
问题描述
我具有复杂的功能来交换来自不同行中相同名称列的数据。我首先读取数据,将其存储在本地临时变量中,然后从第二行读取数据,如果满足某些条件(最小/最大),将其保存到第一行,然后将临时变量保存到第二行,这是缓慢且容易出错的操作
所以我认为也许仅使用SQL即可达到相同的结果。
以下是示例数据:
创建温度表tbl(
id int PRIMARY KEY,
doc_num整数,
doc_text文字
);
插入tbl值
(1,1,'第一栏文字1'),
(2,2,'第一栏文字2'),
(4 ,3,第一列文字3),
(7、4,第一列文字4);
Piont仅按所需方向交换'doc_num'列数据,可能向上或向下我使用单独的函数。
如果我可以用英语写一个简单的查询,听起来像这样:
第一个查询:
在第2行中用DOC_NUM交换DOC_NUM如果第3行中的DOC_NUM IS <= MAX(DOC_NUM);则在第3行中
第二个查询:
交换第3行中的DOC_NUM,第2行中的DOC_NUM,如果第2行中的DOC_NUM IS == MIN(DOC_NUM);
那些查询可以用PostgreSQL编写以及如何写?
这是来自真实程序的真实代码(很丑陋),可以完成工作并需要改进。
私有子DataGridView2_KeyDown(ByVal发送者作为对象,ByVal e作为System.Windows.Forms.KeyEventArgs)处理DataGridView2.KeyDown
'SWAP -------------- ------------------------
如果e.Control和e.KeyCode = Keys.Left,则
Debug.Print(向左/向下交换)
Dim target_nrow作为整数
Dim target_index作为整数
Dim selected_nrow作为整数
Dim selected_index作为整数
Dim target_row作为整数= selected_row-1
使用conn作为新NpgsqlConnection(String.Format( Server = {0}; Port = {1}; User Id = {2}; Password = {3}; Database = {4}; ,dbServer,dbPort,dbUser,dbPass,mydatabase))
conn.Open()
Dim t As NpgsqlTransaction = conn.BeginTransaction()
使用cmd作为新的NpgsqlCommand(_
SELECT cur_id,doc_num,nrow& _
FROM& mytable& & _
WHERE doc_num =’& active_doc.ToString& ’AND nrow =’& selected_row.ToString& ',conn)
使用dr作为NpgsqlDataReader = cmd.ExecuteReader()
而dr.Read()
selected_index = CInt(dr(0))
selected_nrow = CInt(dr(2))
结束时
结束时使用
结束时使用
使用cmd作为新的NpgsqlCommand(_
SELECT cur_id ,doc_num,nrow& _
FROM& mytable&& _
WHERE doc_num ='& active_doc.ToString&'AND nrow ='& ; target_row.ToString&',conn)
使用dr作为NpgsqlDataReader = cmd.ExecuteReader()
而dr.Read()
target_index = CInt(dr( 0))
target_nrow = CInt(dr(2))
结束而
结束使用
结束使用
Di m Updated_t As Integer = 0
使用cmd作为新的NpgsqlCommand(_
UPDATE& mytable& SET& _
nrow =& selected_nrow& & _
WHERE_ID =& target_index.ToString,conn)
Updated_t = CInt(cmd.ExecuteNonQuery())
cmd.Dispose()
使用
结束使用
Dim Updated_s As整数= 0
使用cmd作为新的NpgsqlCommand(_
UPDATE& mytable& SET& _
nrow =& target_nrow& _
WHERE cur_id =& selected_index.ToString,conn)
Updated_s = CInt(cmd.ExecuteNonQuery())
cmd.Dispose()
最终使用
如果updated_s> 0,并且update_t> 0然后
t.Commit()
Else
t.Rollback()
End if
t.Dispose()
conn。 Close()
conn.Dispose()
使用
结尾
刷新列表(active_doc)
End如果
如果e.Control和e .KeyCode = Keys.Right然后
Debug.Print(向右交换)
'类似的代码再次交换
End if
整个故事都在讨论如何使它更短,更快和更优雅?
示例:将doc_num替换为ID 2和4:
UPDATE tbl dst
SET doc_num = src.doc_num
来自tbl src
dst.id IN(2,4)
和src。 id IN(2,4)
和dst.id< src.id-不要在家尝试!
;
选择* FROM tbl
按ID排序;
结果:
id | doc_num | doc_text
---- + --------- + --------------------
1 | 1 |第一栏文字1
2 | 3 |第一栏文字2
4 | 2 |第一栏文字3
7 | 4 |第一列文字4
(4行)
I have complex function to swap data from same name column in different rows. I read data from first, store it in local temporary variables, read data from second row and if certain conditions are met (min/max), save it to first row and then save temporary variables to second row what is slow and error prone operation.
So I thought that maybe same result could be achieved with SQL only.
Here is sample data:
CREATE TEMP TABLE tbl(
id int PRIMARY KEY,
doc_num integer,
doc_text text
);
INSERT INTO tbl VALUES
(1, 1, 'First column text1'),
(2, 2, 'First column text2'),
(4, 3, 'First column text3'),
(7, 4, 'First column text4');
Piont is to swap only 'doc_num' column data in desired direction which may be to up or down what I do with separate functions.
If I can write a simple query in english that will sound like this:
First query:
SWAP DOC_NUM in row 2 with DOC_NUM in row 3 IF DOC_NUM in row 3 IS <= MAX(DOC_NUM);
Second query:
SWAP DOC_NUM in row 3 with DOC_NUM in row 2 IF DOC_NUM in row 2 IS >= MIN(DOC_NUM);
Is those queries possible to write with PostgreSQL and how?
Here is real code (which is ugly) from a real program which "do a job" and need improvements.
Private Sub DataGridView2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles DataGridView2.KeyDown
'SWAP --------------------------------------
If e.Control And e.KeyCode = Keys.Left Then
Debug.Print("Swap left/down")
Dim target_nrow As Integer
Dim target_index As Integer
Dim selected_nrow As Integer
Dim selected_index As Integer
Dim target_row As Integer = selected_row - 1
Using conn As New NpgsqlConnection(String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", dbServer, dbPort, dbUser, dbPass, mydatabase))
conn.Open()
Dim t As NpgsqlTransaction = conn.BeginTransaction()
Using cmd As New NpgsqlCommand( _
"SELECT cur_id, doc_num, nrow " & _
"FROM " & mytable & " " & _
"WHERE doc_num='" & active_doc.ToString & "' AND nrow='" & selected_row.ToString & "'", conn)
Using dr As NpgsqlDataReader = cmd.ExecuteReader()
While dr.Read()
selected_index = CInt(dr(0))
selected_nrow = CInt(dr(2))
End While
End Using
End Using
Using cmd As New NpgsqlCommand( _
"SELECT cur_id, doc_num, nrow " & _
"FROM " & mytable & " " & _
"WHERE doc_num='" & active_doc.ToString & "' AND nrow='" & target_row.ToString & "'", conn)
Using dr As NpgsqlDataReader = cmd.ExecuteReader()
While dr.Read()
target_index = CInt(dr(0))
target_nrow = CInt(dr(2))
End While
End Using
End Using
Dim updated_t As Integer = 0
Using cmd As New NpgsqlCommand( _
"UPDATE " & mytable & " SET " & _
"nrow=" & selected_nrow & " " & _
"WHERE cur_id=" & target_index.ToString, conn)
updated_t = CInt(cmd.ExecuteNonQuery())
cmd.Dispose()
End Using
Dim updated_s As Integer = 0
Using cmd As New NpgsqlCommand( _
"UPDATE " & mytable & " SET " & _
"nrow=" & target_nrow & " " & _
"WHERE cur_id=" & selected_index.ToString, conn)
updated_s = CInt(cmd.ExecuteNonQuery())
cmd.Dispose()
End Using
If updated_s > 0 And updated_t > 0 Then
t.Commit()
Else
t.Rollback()
End If
t.Dispose()
conn.Close()
conn.Dispose()
End Using
Refreshlist(active_doc)
End If
If e.Control And e.KeyCode = Keys.Right Then
Debug.Print("Swap right/up")
'similar code to swap up again
End If
Whole story is in question on how to make this shorter, faster and more elegant?
Example: swap doc_num for ids 2 and 4:
UPDATE tbl dst
SET doc_num = src.doc_num
FROM tbl src
WHERE dst.id IN(2,4)
AND src.id IN(2,4)
AND dst.id <> src.id -- don't try this at home!
;
SELECT * FROm tbl
ORDER BY id;
Result:
id | doc_num | doc_text
----+---------+--------------------
1 | 1 | First column text1
2 | 3 | First column text2
4 | 2 | First column text3
7 | 4 | First column text4
(4 rows)
这篇关于PostgreSQL,将某些列的数据交换为两行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!