Finding/searching for missing values in MySQL
I am using MySQL and have a table c开发者_Python百科alled 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;
}
}
If you're just trying to find numbers out of the sequence in the example you've provided you could use something like this:
SELECT *
FROM `sales`
WHERE (
`invoice_id` - 147 - `sales_id`
) = 0
If you can provide a bigger sample set we might be able to tool a better answer for you.
set @suggest_invoice:=147;
select
sales_id,
invoice_id,
@suggest_invoice:=@suggest_invoice+1,
if(invoice_id=@suggest_invoice, 0, 1) as missing_invoice
from sales
order by sales_id;
I guess you almost getting all the records except the first two with missing_invoice=1
As you are searching for invoice_id
then make sure you have an index on that column. otherwise queries will be slow.
You can try the following code
$inv_ids=range(147,4497);
$str_inv_ids=implode(",",$inv_ids);
$query="SELECT DISTINCT invoice_id FROM sales WHERE invoice_id IN ($str_inv_ids)";
$result=mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
$inv_ids_db[]=$row['invoice_id'];
}
// It gives invoice Ids that are in $inv_ids but not in $inv_ids_db
$missing= array_diff($inv_ids,$inv_ids_db);
print_r($missing);
You can find gaps in a MySQL sequence using this query:
SELECT invoice_id+1
FROM sales s
WHERE NOT EXISTS (
SELECT NULL
FROM sales t
WHERE s.invoice_id = t.invoice_id+1
)
HAVING `invoice_id+1` < (SELECT MAX(invoice_id) FROM sales)
ORDER BY invoice_id
This will return all invoice_id
missing from the sequence, regardless of sales_id
.
精彩评论