存储过程中的高级选择 [英] advanced select in Stored Procedure
问题描述
我得到了这张桌子:
CREATE TABLE Test_Table (
old_val VARCHAR2(3),
new_val VARCHAR2(3),
Updflag NUMBER,
WorkNo NUMBER );
这是在我的表中:
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('1',' 20',0,0);
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('2',' 20',0,0);
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('2',' 30',0,0);
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('3',' 30',0,0);
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('4',' 40',0,0);
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('4',' 40',0,0);
现在我的桌子看起来像这样:
now my Table Looks like this:
Row Old_val New_val Updflag WorkNo
1 '1' ' 20' 0 0
2 '2' ' 20' 0 0
3 '2' ' 30' 0 0
4 '3' ' 30' 0 0
5 '4' ' 40' 0 0
6 '5' ' 40' 0 0
(如果new_val列中的值相同，则它们在一起，并且对old_val相同)
因此在上面的示例中，第1-4行在一起，而第5-6行
(if the value in the new_val column are same then they are together and the same goes to old_val)
so in the example above row 1-4 are together and row 5-6
目前我的存储过程中有一个光标:
at the moment i have in my Stored Procedure a cursor:
SELECT t1.Old_val, t1.New_val, t1.updflag, t1.WorkNo
FROM Test_Table t1
WHERE t1.New_val =
(
SELECT t2.New_val
FROM Test_Table t2
WHERE t2.Updflag = 0
AND t2.Worknr = 0
AND ROWNUM = 1
)
输出是这样的:
Row Old_val New_val Updflag WorkNo
1 1 20 0 0
2 2 20 0 0
我的问题是，我不知道如何通过一次选择获得第1至4行. (我有4个子查询的想法，但如果它的更多数据匹配在一起，则将无法工作)
my Problem is, i dont know how to get row 1 to 4 with one select. (i had an idea with 4 sub-querys but this wont work if its more data that matches together)
你们中有人有主意吗?
推荐答案
您可以使用分析来定义连续行的组:
you can use analytics to define groups of contiguous rows:
SQL> SELECT old_val, new_val, updflag, workno,
2 SUM(gap) over(ORDER BY old_val, new_val) grp
3 FROM (SELECT t.*,
4 CASE
5 WHEN new_val = lag(new_val)
6 over(ORDER BY old_val, new_val)
7 OR old_val = lag(old_val)
8 over(ORDER BY old_val, new_val)
9 THEN
10 0
11 ELSE
12 1
13 END gap
14 FROM Test_Table t);
OLD_VAL NEW_VAL UPDFLAG WORKNO GRP
------- ------- ---------- ---------- ----------
1 20 0 0 1
2 20 0 0 1
2 30 0 0 1
3 30 0 0 1
4 40 0 0 2
4 40 0 0 2
当当前行与前一行不在同一组中时，内部SELECT会构建一个等于1的"GAP"列.
The inner SELECT builds a "GAP" column that equals 1 when the current row is not in the same group as the preceeding.
外部SELECT在gap列上使用运行总计来获取组号.
The outer SELECT uses a running total over the gap column to get the group number.
由于分析功能，您不能将FOR UPDATE子句直接添加到查询中.但是，您可以直接查询基表:
You can't add the FOR UPDATE clause directly to the query because of the analytic functions. You can query the base table directly however:
SQL> WITH t_new AS (
2 SELECT t_rowid, old_val, new_val, updflag, workno,
3 SUM(gap) over(ORDER BY old_val, new_val) grp
4 FROM (SELECT t.*, t.rowid t_rowid,
5 CASE
6 WHEN new_val = lag(new_val)
7 over(ORDER BY old_val, new_val)
8 OR old_val = lag(old_val)
9 over(ORDER BY old_val, new_val)
10 THEN
11 0
12 ELSE
13 1
14 END gap
15 FROM test_table t)
16 )
17 SELECT *
18 FROM test_table
19 WHERE ROWID IN (SELECT t_rowid
20 FROM t_new
21 WHERE grp = (SELECT grp
22 FROM t_new t2
23 WHERE t2.new_val = ' 20'
24 AND t2.old_val = '1'))
25 FOR UPDATE;
OLD_VAL NEW_VAL UPDFLAG WORKNO
------- ------- ---------- ----------
1 20 0 0
2 20 0 0
2 30 0 0
3 30 0 0
这篇关于存储过程中的高级选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
