MySQL:使用计算计数器列作为子查询的键

Question

抱歉，很长的史前史，但需要澄清这个问题。

在我的组织中，计算机的名称类似于 CNT30[0-9]{3}[1-9a-z]，例如 cnt300021 或 cnt30253a .

最后一个符号是“限定符”，因此单个工作场所可能分配有相同名称的计算机，并通过该限定符进行区分。例如，cnt300021 可能表示工作场所 #002 上的台式计算机，cnt30002a 可能表示分配给同一工作场所的笔记本电脑。工作场所是“虚拟的”，它们的存在只是为了我们(IT 部门)的方便。

每个部门都有自己独特的范围[0-9]{3}。例如，会计计算机的名称从 cnt302751 开始到 cnt30299z，这为它们提供了最多 25 个独特的工作场所，每个工作场所最多有 35 台计算机。 (现实生活中，大多数用户拥有一台台式电脑，拥有台式电脑和笔记本电脑的用户就更少了，只有 2 或 3 名技术人员拥有一台以上笔记本电脑可供使用)。

最近，在对计算机的护照进行一些盘点时(不确定一个术语:一张纸，这对于计算机来说是一样的，护照对于人类来说意味着什么)，我发现其中存在一些漏洞顺序编号。例如，我们有 cnt302531 和 cnt302551，但没有 cnt302541，这意味着没有工作场所 #254。

我想做什么？我想找到这些差距，而无需手动搜索。为此，我需要一个从 1 到 MaxComp=664 的循环(尚未分配更多工作场所编号)

这就是我可以使用一些伪 SQL-BASIC 编写的内容:

for a=0 to MaxComp
  a$="CNT30"+right(a+1000,3)
'comparing only 8 leftmost characters, ignoring 9th one - the qualifier
  b$=(select name from table where left(name,8) like a$)
  print a$;b$
next a

该代码应该给我两列:可能的名称和现有的名称。

但是我不知道如何在 SQL 查询中实现它。我尝试过的:

# because of qualifier there may be several computers with same
# 8 leftmost characters
select @cnum:=@cnum+1 as CompNum, group_concat(name separator ',')
# PCs are inventoried by OCS-NG Inventory software
  from hardware
  cross join (select @cnum:=0) cnt
  where left(hardware.name,8)=concat('CNT30',right(@cnum+1000,3))
  limit 100

但是这个构造只返回一行。我不明白，如果不使用存储过程也可以，如果可以的话我做错了什么？

Answer 1

找到工作路径:

(起初我尝试使用存储函数)

CREATE FUNCTION `count_comps`(num smallint) RETURNS tinytext CHARSET utf8
BEGIN
    return (select group_concat(name separator ',')
        from hardware where left(hardware.name,8)=concat('CNT30',right(num+1000,3))
        );
END

然后我努力在子查询中复制函数的结果。我做到了!注意:内部 select 返回与函数完全相同的结果

# Starting point. May be INcreased to narrow the results list
set @cnum:=0;
select
    @cnum:=@cnum+1 as CompNum,
    concat('CNT30',right(@cnum+1000,3)) as CalcNum,
# this
    count_comps(@cnum) as hwns,
# and this gives equal results
    (select group_concat(name separator ',')
        from hardware where left(name,8)=calcnum
        ) hwn2
    from hardware
# no more dummy tables here
# Ending point. May be DEcreased to narrow the results list
  where @cnum<665;

因此，“经典”方法的错误部分是使用虚拟表，这似乎没有必要。

部分结果示例(开始 set @cnum:=479; ，结束 where @cnum<530; ):

CompNum, CalcNum, hwns, hwn2
'488', 'CNT30488', 'CNT304881', 'CNT304881'
'489', 'CNT30489', 'CNT304892', 'CNT304892'
'490', 'CNT30490', 'CNT304901,CNT304902,CNT304903', CNT304901,CNT304902,CNT304903'
'491', 'CNT30491', NULL, NULL
'492', 'CNT30492', NULL, NULL
'493', 'CNT30493', 'CNT304932', 'CNT304932'
'494', 'CNT30494', 'CNT304941', 'CNT304941'

我发现没有工作场所#491和#492。接下来添加“十月地区”部门(范围 480-529)的 PC 时，至少有两台新 PC 的名称将是 CNT304911 和 CNT304921，从而填补了这一空白。