ค้นหาหมายเลขก่อนหน้าในคอลัมน์ที่ไม่ขาดหายไปจากลำดับ
ฉันมีตารางที่มีคอลัมน์ที่ควรมีตัวเลขในลำดับที่สมบูรณ์เพื่อความง่ายเราจะพูดว่า 101 ถึง 110 อย่างไรก็ตามตารางนี้อาศัยข้อมูลที่ไม่น่าเชื่อถือที่จะป้อนด้วยตนเองดังนั้นตัวเลขในลำดับจึงพลาด นอกจากนี้ยังมีคอลัมน์วันที่ในตารางเดียวกันกับที่ฉันต้องการอ้างถึงอีกสักครู่ ฉันได้รับคำท้าให้ค้นหาหมายเลขลำดับที่หายไปทั้งหมดพร้อมกับหมายเลขลำดับก่อนหน้าที่ป้อนและวันที่ที่ป้อนและหมายเลขลำดับถัดไปพร้อมวันที่ที่ป้อน การค้นหาหมายเลขลำดับที่ขาดหายไปนั้นตรงไปตรงมามันคือการรับบันทึกก่อนหน้าและถัดไปที่เกี่ยวข้องที่ฉันกำลังดิ้นรน ดังนั้นหากข้อมูลของฉันมีลักษณะเช่นนี้
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>ชุดผลลัพธ์ของฉันจะมีลักษณะดังนี้
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>แต่ถ้าไม่มีคอลัมน์บันทึกนั่นก็เพื่อความชัดเจน
ฉันได้รับคำตอบ แต่ sql นั้นใหญ่มากและเทอะทะมันก็ดีเหมือนไร้ประโยชน์ ขอบคุณ.
คำตอบ
หากคุณต้องการหนึ่งแถวต่อช่องว่างคุณสามารถใช้ฟังก์ชันหน้าต่าง:
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
ในทางกลับกันหากคุณมีตัวเลขที่ขาดหายไปติดต่อกันและคุณต้องการหนึ่งแถวสำหรับแต่ละแถวคุณต้องมีการเรียกซ้ำบางประเภท:
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