Rechercher le numéro précédent dans la colonne qui ne manque pas dans une séquence
J'ai un tableau avec une colonne qui devrait contenir des nombres dans une séquence complète, pour plus de simplicité, nous dirons 101 à 110. Cependant, ce tableau repose sur des informations peu fiables à saisir manuellement, donc les nombres dans la séquence sont manqués. Il y a aussi une colonne de date dans le même tableau à laquelle je dois me référer, plus à ce sujet dans un instant. J'ai été mis au défi de trouver tous les numéros de séquence manquants ainsi que le numéro de séquence précédent qui a été entré, la date à laquelle il a été entré, et le numéro de séquence suivant avec la date à laquelle il a été entré. Trouver les numéros de séquence manquants est simple, c'est obtenir les enregistrements précédents et suivants pertinents avec lesquels je me bats. Donc, si mes données ressemblaient à ceci;
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>Mon jeu de résultats ressemblerait à quelque chose comme;
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>Mais sans la colonne de notes, c'est juste là pour plus de clarté.
Je peux obtenir une réponse, mais le SQL est tellement énorme et peu maniable qu'il est aussi bon qu'inutile. Merci.
Réponses
Si vous voulez une ligne par espace, vous pouvez utiliser les fonctions de fenêtre:
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
D'un autre côté, si vous avez des nombres manquants consécutifs et que vous voulez une ligne pour chacun, vous avez besoin d'une sorte de récursivité:
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