mysql 查询 id 缺失,MySQL：找出缺失的订单ID

i Know this question asked several times in StackOverFlow. i have tried few of them but i am out of luck.

i have a MySQL table where there is a field(orders_id) which is might appear randomly in the table (Not sequentially) and i need to find out which ids are missing from the table.

orders_id product_name qty

1007 Wireless Mouse 1

1000 Laptop 1

1004 Wireless Mouse 3

1020 PC 3

1003 Wireless Mouse 4

1025 IPAD 4

1026 iphone 1

Expected Answer:

Assume orders_id start from 1000.

orders_id

1000

1001

1002

1005

1006

1008

......

i have already created above table at "SqlFiddle" , you guys can use it.

**the SQL i have tried: **

declare @id int

declare @maxid int

set @id = 1

select @maxid = max(`orders_id`) from orders

create temporary table IDSeq

(

id int

)

while @id < @maxid

begin

insert into IDSeq values(@id)

set @id = @id + 1

end

select

s.id

from

idseq s

left join orders t on

s.id = t.`orders_id`

where t.`orders_id` is null

drop table IDSeq

I have taken above SQL from the following Answer:

i have also tried the ANSI SQL:

SELECT a.orders_id+1 AS start, MIN(b.orders_id) - 1 AS end

FROM orders AS a, orders AS b

WHERE a.orders_id < b.orders_id

GROUP BY a.orders_id

HAVING start < MIN(b.orders_id)

Anyone has any idea?? how can i find out the missing orders id.

解决方案

Here is another solution to do this:

CREATE TABLE TEMP

(n int);

INSERT INTO Temp VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

SET @maxid = (SELECT MAX(orders_id) FROM orders);

SELECT s.id

FROM

(

SELECT id

FROM

(

SELECT t4.n * 1000 + t3.n * 100 + t2.n * 10 + t1.n + 1 AS id

FROM TEMP AS t1

CROSS JOIN TEMP AS t2

CROSS JOIN TEMP AS t3

CROSS JOIN TEMP AS t4

) t

WHERE id BETWEEN 1000 AND @maxid

) s

LEFT JOIN orders t ON s.id = t.`orders_id`

WHERE t.`orders_id` IS NULL;

This should give you the orders_ids:

ID

1001

1002

1005

1006

1008

1009

1010

1011

1012

1013

1014

1015

1016

1017

1018

1019

1021

1022

1023

1024

Note that: I created a TEMP table with values from 0 to 9, in order to use to generate an anchor table containing values from 1 to 10000 instead of the while loop that you had in the query you posted. You can control the values from this table depending on the values in your table. If you had values that exceed 10000 CROSS JOIN the TEMP table one more time.

Update: If the orders_id is of datatype varchar just cast it to INTEGER like so:

SELECT s.id

FROM

(

SELECT id

FROM

(

SELECT t4.n * 1000 + t3.n * 100 + t2.n * 10 + t1.n + 1 AS id

FROM TEMP AS t1

CROSS JOIN TEMP AS t2

CROSS JOIN TEMP AS t3

CROSS JOIN TEMP AS t4

) t WHERE id between 1000 AND @maxid

) s

LEFT JOIN

(

SELECT CONVERT(orders_id, UNSIGNED INTEGER) AS orders_id

FROM orders

) t ON s.id = t.`orders_id`

WHERE t.`orders_id` IS NULL;