主页 / dba / 问题 / 289093
Accepted
Franck White
Asked: 2021-04-02 05:11:16 +0800 CST

如何查找序列中缺失的值？

  • 772

我想从我的表中的一系列值中找到缺失的行。例如，在这张照片documentno 'YD4BC2006008'中是缺失的。

我想通过c_order_id和找到缺失的行documentno。

我试过了，但没有返回预期的结果：

select * from
(SELECT distinct c_order_id, documentno,cast(right(documentno,3) as integer) as no
FROM adempiere.c_order
ORDER BY documentno, c_order_id) as f

在此处输入图像描述

在此示例中，缺少许多行： from'DGPOS2003030'到'DGPOS2003068'。

如何编写查询来检索它们？

在此处输入图像描述

postgresql postgresql-9.5

2 个回答

  1. 一种简单的方法是生成所有潜在documentnos 的列表并从中删除所有存在的 s：

    select 'YD4BC' || generate_series(2006004, 2006015) as missing_documentno
except
select documentno from documents;
    • 1
  2. Best Answer

    一种更动态的方法是，获取最低的 Number 和最高的 Number，然后从那里构建缺少的 m。

    如果您没有示例 2006004 中的前导 2，则需要填充生成的部分

    CREATE TABLE documents (
  "order_id" VARCHAR(22),
  "documentno" VARCHAR(12)
);

INSERT INTO documents
  ("order_id", "documentno")
VALUES
  ('100001110', 'VD4BC2006004'),
  ('100001114' ,'VD4BC2006005'),
  ('100001135' ,'YD4BC2006006'),
  ('100001166' ,'YD4BC2006007'),
  ('100001215' ,'YD4BC2006009'),
  ('100001256' ,'YD4BC2006010'),
  ('100001289' ,'YD4BC2006011'),
  ('100001332' ,'VD4BC2006013'),
  ('100001334' ,'VD4BC2006014'),
  ('100001432' ,'VD4BC2006015'),
  ('100000616' ,'DGP0S2003028'),
  ('100000617' ,'DGP0S2003029'),
  ('100001034' ,'DGP0S2003069');

    WITH cte as (
SELECT LEFT("documentno",5) lpart , MIN(RIGHT("documentno",7)) minpart, MaX(RIGHT("documentno",7)) maxpart FROM documents
groUP BY LEFT("documentno",5))
sELECT lpart || generate_series(minpart::INTEGER, maxpart::INTEGER) as mdocumentno FROM cte
except
select documentno from documents
ORDER bY mdocumentno;

    | 文档号 |
| :----------- |
| DGP0S2003030 |
| DGP0S2003031 |
| DGP0S2003032 |
| DGP0S2003033 |
| DGP0S2003034 |
| DGP0S2003035 |
| DGP0S2003036 |
| DGP0S2003037 |
| DGP0S2003038 |
| DGP0S2003039 |
| DGP0S2003040 |
| DGP0S2003041 |
| DGP0S2003042 |
| DGP0S2003043 |
| DGP0S2003044 |
| DGP0S2003045 |
| DGP0S2003046 |
| DGP0S2003047 |
| DGP0S2003048 |
| DGP0S2003049 |
| DGP0S2003050 |
| DGP0S2003051 |
| DGP0S2003052 |
| DGP0S2003053 |
| DGP0S2003054 |
| DGP0S2003055 |
| DGP0S2003056 |
| DGP0S2003057 |
| DGP0S2003058 |
| DGP0S2003059 |
| DGP0S2003060 |
| DGP0S2003061 |
| DGP0S2003062 |
| DGP0S2003063 |
| DGP0S2003064 |
| DGP0S2003065 |
| DGP0S2003066 |
| DGP0S2003067 |
| DGP0S2003068 |
| VD4BC2006006 |
| VD4BC2006007 |
| VD4BC2006008 |
| VD4BC2006009 |
| VD4BC2006010 |
| VD4BC2006011 |
| VD4BC2006012 |
| YD4BC2006008 |

    db<>在这里摆弄

    • 0

相关问题