时间戳

首页 > 解决方案 > MySQL：使用计算的计数器列作为子查询的键

mysql - MySQL：使用计算的计数器列作为子查询的键

问题描述

抱歉，历史悠久，但需要澄清这个问题。

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

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

每个部门都有自己独特的范围[0-9]{3}。例如，会计计算机的名称以cnt302751upto开头cnt30299z，这为它们提供了最多 25 个独特的工作场所，每个工作场所最多有 35 台计算机。（IRL 大多数用户拥有一台台式电脑，很少有台式机和笔记本电脑，只有 2 或 3 名技术人员拥有不止一台笔记本电脑可供使用）。

最近在盘点一下电脑的护照（不确定一个词：a paper，对电脑来说是什么意思，对人来说护照是什么意思），发现序号有一些漏洞。例如，我们有cnt302531and 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

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

标签： mysqlauto-increment

解决方案

找到工作路径：

（起初我尝试使用存储功能）

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

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

# 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，填补了这一空白。

推荐阅读