提问人：Raghu 提问时间：11/15/2023 更新时间：11/16/2023 访问量：32
如何根据该行的条件在表格行的每个给定数字之间获取下一个可用数字
How to get next available number between given numbers for each of the table row based on condition of that row
问：
对于从 Oracle DB 表中获取的每个 Product 记录，我需要在给定的数字范围之间生成下一个可用数字。在生成下一个数字时，只要给定数字范围内有未使用的号码可用，就不涉及条件检查。当它已经达到给定数字范围内的最后一个数字时，它应该从头开始重复，但从这个时候开始，它应该根据条件检查生成它。
我将尝试以一种简单的方式表达我的要求。
要使用的序列号范围：1-3（1 到 3）
PRODUCT 表中的数据：
Name Start Date End Date
P1 1-Nov
P2 1-Nov 5-Nov
P3 1-Nov
P4 2-Nov
p2 2-Nov
假设没有结束日期的产品是有效产品，具有结束日期的产品是非活动产品。
预期输出：
Name Start Date End Date SequenceNumber Comment
P1 1-Nov 1 No conditional check
P2 1-Nov 5-Nov 2 No conditional check
P3 1-Nov 3 No conditional check
P4 2-Nov 2 Sequence should start from 1 again but with condition check while generating sequence. Condition: If the generated number is used in any other active product, move on to next number
p2 2-Nov In this case, all 1, 2, 3 are mapped to active products. so no sequence is generated.
问题： 如何使用 select 语句获得所需的输出？这将是有帮助的，你可以就此提出一些建议。谢谢。
答：
0赞
d r
11/16/2023
#1
嗯，它看起来像行间 - 列间处理，这意味着您可以使用 Oracle 的 MODEL 子句。它的工作原理很像处理单个单元格的 excel 工作表。
WITH -- S a m p l e D a t a :
tbl AS
( Select 'P1' "A_NAME", Date '2023-11-01' "START_DATE", Null "END_DATE" From Dual Union All
Select 'P2' "A_NAME", Date '2023-11-01' "START_DATE", Date '2023-11-05' "END_DATE" From Dual Union All
Select 'P3' "A_NAME", Date '2023-11-01' "START_DATE", Null "END_DATE" From Dual Union All
Select 'P4' "A_NAME", Date '2023-11-02' "START_DATE", Null "END_DATE" From Dual Union All
Select 'P2' "A_NAME", Date '2023-11-02' "START_DATE", Null "END_DATE" From Dual
)
-- M a i n S Q L :
SELECT A_NAME, START_DATE, END_DATE, SEQUENCE_NUMBER
FROM ( Select ROWNUM "RN", A_NAME, START_DATE, END_DATE, 0 "SEQUENCE_NUMBER"
From tbl
)
-- RowNum (RN) used to uniquely identify rows
MODEL Dimension By ( RN )
Measures ( A_NAME, START_DATE, END_DATE, SEQUENCE_NUMBER, 0 "SEQUENCES" )
RULES
( SEQUENCE_NUMBER[ANY] = Case When Case When Nvl(Count(START_DATE)[RN < CV()], 0) + 1 <= 3
Then Nvl(Count(START_DATE)[RN < CV()], 0) + 1
Else 0 End = 0
Then 0
Else Case When Nvl(Count(START_DATE)[RN < CV()], 0) + 1 <= 3
Then Nvl(Count(START_DATE)[RN < CV()], 0) + 1 -- this gives non conditional sequences (1 - 3)
Else 0 End
End,
--
-- added colummn SEQUENCES to control the sequence numbers used before the current row
SEQUENCES[ANY] = Nvl(Max(SEQUENCE_NUMBER)[RN < CV(RN)], 0),
--
SEQUENCE_NUMBER[ANY] = Case When SEQUENCE_NUMBER[CV()] = 0
Then Case When SEQUENCES[CV()] = 3 And
Min(SEQUENCE_NUMBER)[RN < CV()] = 1 And
Max(SEQUENCES)[RN < CV()] < 3
Then Min(SEQUENCE_NUMBER)[RN < CV()] + 1 -- this gives conditional sequence when first is used (used + 1)
End -- no Else part (same as Else Null) gives Null when (used + 1) is also done
Else SEQUENCE_NUMBER[CV()]
End
)
/* R e s u l t :
A_NAME START_DATE END_DATE SEQUENCE_NUMBER
------ ---------- --------- ---------------
P1 01-NOV-23 1
P2 01-NOV-23 05-NOV-23 2
P3 01-NOV-23 3
P4 02-NOV-23 2
P2 02-NOV-23 */
评论