[mysql]要从表中检索下一个可用 ID 的 SQL 查询

标签: sql MySQL
发布时间: 2017/1/9 22:07:33
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有一个表,其中包含名为 ticket_id 的列和它包含的值,如下所示︰

ticket_id
STK0000000001
STK0000000002
STK0000000001
STK0000000003
STK0000000002
STK0000000001

Ticket_id 值将重复在某些行,所以它不是唯一。

我使用此查询来获得下一个可用 id,但我不能得到它的工作。它始终返回 STK0000000002

任何帮助感谢 !

SQL:

SELECT
    CONCAT('STK', LPAD(seq, 10, '0')) AS nextID
FROM
    (SELECT
        @seq:=@seq+1 AS seq,
        num
    FROM
        (SELECT
            CAST(SUBSTR(ticket_id, 4) AS UNSIGNED) AS num
        FROM
            sma_support_tickets
        UNION ALL
        SELECT
            MAX(CAST(SUBSTR(ticket_id, 4) AS UNSIGNED))+2 AS num
        FROM 
            sma_support_tickets
        ORDER BY
            num) AS ids
    CROSS JOIN 
        (SELECT @seq:=0) AS init 
    ) AS pairs
WHERE
    seq!=num
LIMIT 1

解决方法 1:

也许我失去了一些东西在你的问题,但似乎这应该做到︰

SELECT CONCAT('STK', 
              LPAD(MAX(SUBSTRING(ticket_id, 4)) + 1, 
                   10, 
                   '0')
             ) 
FROM sma_support_tickets;
赞助商