在MySQL中查找/搜索缺失值 [英] Finding/searching for missing values in MySQL

问题描述

我使用MySQL并拥有一个名为 sales 的表。它的主键是 sales_id 。

I am using MySQL and have a table called sales. Its primary key is sales_id.

-------------------------------------
sales_id | invoice_id | 
-------------------------------------

1  |  147 
2  |  148
3  |  150

sales_id 3 invoice_id 应该是149.我想知道 invoice_id 中缺少哪些数字。我在147开始 invoice_id ，在4497开始 invoice_id 。 invoice_id 与 sales_id 无关。

For sales_id 3 the invoice_id is supposed to be 149. I want to know which numbers are missing from invoice_id. I start invoice_id at 147 and end invoice_id at 4497. The invoice_id had no relation with sales_id.

可以知道invoice_id中缺少哪些数字，查询？

Is it possible to know which numbers are missing from invoice_id, using a query?

推荐答案

我假设您有一张发票表格 - INVOICES。您可以尝试：

I presume that you have a table of invoice - INVOICES. You may try:

 SELECT invoice_id FROM INVOICES invoice
 WHERE NOT EXISTS (SELECT * FROM SALES s WHERE invoice.invoice_id = s.invoice_id)

编辑：如果您没有INVOICES表，

If you don't have the INVOICES table, you may need to take all the invoices out before checking if there's any gap.

选择DISTINCT invoice_id FROM SALES ORDER BY invoice_id ASC
SELECT MAX（invoice_id）FROM SALES

SELECT DISTINCT invoice_id FROM SALES ORDER BY invoice_id ASC SELECT MAX(invoice_id) FROM SALES

然后，通过php：

for ($i = 1; $i < $max_invoice_id; $i++)
{
  if (!in_array($i, $all_invoice_id))
  {
    $gapId[] = $i;
  }
}

这篇关于在MySQL中查找/搜索缺失值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！