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

我知道这个问题在StackOverFlow中问过几次.我尝试了其中的几个,但我很走运.

我有一个MySQL表,其中有一个字段(orders_id),该字段可能会随机出现在表中(不按顺序),我需要找出表中缺少哪些ID.

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

预期答案：

假设orders_id从1000开始.

orders_id

1000

1001

1002

1005

1006

1008

......

我已经在“ SqlFiddle”上表上方创建了,大家可以使用它.

我尝试过的SQL：

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

我已经从下面的答案中选择了SQL：

我也尝试过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)

有人知道吗？我如何找出丢失的订单ID.

解决方法:

这是执行此操作的另一个解决方案：

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;

这应该给您orders_ids：

ID

1001

1002

1005

1006

1008

1009

1010

1011

1012

1013

1014

1015

1016

1017

1018

1019

1021

1022

1023

1024

请注意：我创建了一个TEMP表,其值从0到9,以便用于生成包含1到10000值的锚表,而不是您发布的查询中的while循环.您可以根据表中的值控制此表中的值.如果您的值超过10000 CROSS JOIN TEMP表一次.

更新：如果orders_id的数据类型为varchar,则将其转换为INTEGER,如下所示：

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;

标签：sql,mysql,database

来源： https://codeday.me/bug/20191031/1977450.html