英文:
looping string list and get no record count from table
问题
我有从一个表中使用listagg(column, ',')获取的字符串值，
所以我想循环这个字符串列表，并将其设置为另一个表的where子句，
然后当表中没有任何记录时，我想要获取计数（没有记录的次数）
我正在在PL/SQL过程中编写这个。
| order_id | name |
|---|---|
| 10 | test1 |
| 20 | test2 |
| 22 | test3 |
| 25 | test4 |
| col_id | product | order_id |
|---|---|---|
| 1 | pro1 | 10 |
| 2 | pro2 | 30 |
| 3 | pro2 | 38 |
预期结果：在第二个表中计数（没有记录的次数）
计数 = 3
因为第二个表中没有20、22、25订单ID的任何记录，
只有订单ID - 10有记录。
我的查询
SELECT listagg(ord.order_id, ',')
into wk_orderids
from orders ord
where ord.id_no = wk_id_no;
loop
-- 做我的事情
end loop
wk_orderids的值 =（'10'，'20'，'22'，'25'）
我想循环这个值（wk_orderids），并将它一个一个地设置为select查询的where子句，
然后想要获取没有记录的次数。
英文:
I have string values get from a table using listagg(column,',')
so I want to loop this string list and set into where clause for another table
then I want to get a count when no any records in the table (Number of times with no record)
I'm writing this inside the plsql procedure
| order_id | name |
|---|---|
| 10 | test1 |
| 20 | test2 |
| 22 | test3 |
| 25 | test4 |
| col_id | product | order_id |
|---|---|---|
| 1 | pro1 | 10 |
| 2 | pro2 | 30 |
| 3 | pro2 | 38 |
expected result : count(Number of times with no record) in 2nd table
count = 3
because there is no any record of 20,22,25 order ids in 2nd table
only have record for order_id - 10
my queries
SELECT listagg(ord.order_id,',')
into wk_orderids
from orders ord,
where ord.id_no = wk_id_no;
loop
-- do my stuff
end loop
wk_orderids values = ('10','20','22','25')
I want to loop this one(wk_orderids) and set it one by one into a select query where clause
then want to get the count Number of times with no record
答案1
得分: 0
如果你想在第二张表中计算那些在第一张表的ORDER_ID列中不存在的ORDER_ID，那么你目前的方法看起来就像是被分配了一个以最复杂的方式来完成这个任务的任务。聚合数值，循环遍历它们，将值添加到where子句中（这将需要动态SQL）...好的，但是 - 为什么？为什么不简单地：
select count(*)
from (select order_id from first_table
minus
select order_id from second_table
);
英文:
If you want to count ORDER_IDs in the 2nd table that don't exist in ORDER_ID column of the 1st table, then your current approach looks as if you were given a task to do that in the most complicated way. Aggregating values, looping through them, adding values into a where clause (which then requires dynamic SQL) ... OK, but - why? Why not simply
select count(*)
from (select order_id from first_table
minus
select order_id from second_table
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论