mysql缺失值处理_在MySQL中查找/搜索缺失值

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

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.

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

解决方案

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)

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

SELECT DISTINCT invoice_id FROM SALES ORDER BY invoice_id ASC

SELECT MAX(invoice_id) FROM SALES

then, by php:

for ($i = 1; $i < $max_invoice_id; $i++)

{

if (!in_array($i, $all_invoice_id))

{

$gapId[] = $i;

}

}