Temukan nomor sebelumnya di kolom yang tidak hilang dari sebuah urutan
Saya memiliki tabel dengan kolom yang harus berisi angka-angka dalam urutan lengkap, untuk sederhananya kita akan mengatakan 101 hingga 110. Namun, tabel ini mengandalkan informasi yang tidak dapat diandalkan untuk dimasukkan secara manual, sehingga angka-angka dalam urutan tersebut terlewatkan. Ada juga kolom tanggal di tabel yang sama yang perlu saya rujuk, lebih banyak lagi nanti. Saya telah ditantang untuk menemukan semua nomor urut yang hilang bersama dengan nomor urut sebelumnya yang dimasukkan, dan tanggal itu dimasukkan, dan nomor urut berikutnya dengan tanggal itu dimasukkan. Menemukan nomor urut yang hilang itu mudah, itu mendapatkan catatan relevan sebelumnya dan berikutnya yang saya perjuangkan. Jadi jika data saya terlihat seperti ini;
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}<html>
<body>
<table>
<tr>
<th>Seq No</th>
<th>Date Input</th>
</tr>
<tr>
<td>101</td>
<td>01-JAN-20</td>
</tr>
<tr>
<td>102</td>
<td>05-JAN-20</td>
</tr>
<tr>
<td>104</td>
<td>07-JAN-20</td>
</tr>
<tr>
<td>105</td>
<td>08-JAN-20</td>
</tr>
<tr>
<td>106</td>
<td>09-JAN-20</td>
</tr>
<tr>
<td>108</td>
<td>10-JAN-20</td>
</tr>
<tr>
<td>109</td>
<td>11-JAN-20</td>
</tr>
<tr>
<td>110</td>
<td>12-JAN-20</td>
</tr>
</table>
</body>
</html>Kumpulan hasil saya akan terlihat seperti;
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}<html>
<body>
<table>
<tr>
<th>Missing Seq No</th>
<th>Previous Date</th>
<th>Next Date</th>
<th>Notes</th>
</tr>
<tr>
<td>103</td>
<td>05-JAN-20</td>
<td>07-JAN-20</td>
<td>Dates from found seq nos 102 and 104</td>
</tr>
<tr>
<td>107</td>
<td>09-JAN-20</td>
<td>10-JAN-20</td>
<td>Dates from found seq nos 106 and 108</td>
</tr>
</table>
</body>
</html>Tetapi tanpa kolom catatan, itu hanya untuk kejelasan.
Saya bisa mendapatkan jawaban, tetapi sql sangat besar dan berat itu sama saja tidak berguna. Terima kasih.
Jawaban
Jika Anda menginginkan satu baris per celah, maka Anda dapat menggunakan fungsi jendela:
select
lag_seq_no last_sequence_number,
lag_date_input last_date_input,
seq_no next_sequence_number,
date_input next_date_input
from (
select
t.*,
lag(seq_no) over(order by date_input) lag_seq_no,
lag(date_input) over(order by date_input) lag_date_input
from mytable t
) t
where seq_no > lag_seq_no + 1
Di sisi lain, jika Anda memiliki nomor yang hilang berturut-turut dan Anda menginginkan satu baris untuk masing-masingnya, maka Anda memerlukan semacam rekursi:
with
data(seq_no, date_input, lag_seq_no, lag_date_input) as (
select
t.*,
lag(seq_no) over(order by date_input) lag_seq_no,
lag(date_input) over(order by date_input) lag_date_input
from mytable t
),
cte (seq_no, date_input, lag_seq_no, lag_date_input) as (
select seq_no, date_input, lag_seq_no + 1, lag_date_input
from data
where seq_no > lag_seq_no + 1
union all
select seq_no, date_input, lag_seq_no + 1, lag_date_input
from cte
where seq_no > lag_seq_no + 1
)
select
lag_seq_no missing_seq_no,
lag_date_input last_date_input,
date_input next_date_input
from cte