MySQL:将具有相似不重复内容的行计为一

Question

我正在使用 Codeigniter 及其查询生成器类，其中有一个包含 ID 和名称的表。
这些名称看起来像 1234_1a 或 2345_2a，其中 1a 可以是 1b 或 2a、2b、3a...等等。

现在我想计算所有这些“1234”和“2345”，但将它们写为一种类型。

到目前为止我尝试过:

$this->db->like('names', '1a', 'before');
$this->db->or_like('names', '1b', 'before');
return $this->db->count_all_results('table');

但是问题是:
如果有 3456_2a 但没有 3456_1a，那它就不再起作用了...

id  name
2   1212_1a
3   1243_1a
7   3142_1a
24  1243_2a
30  3142_2b
80  2315_2b
136 1243_3b
512 8562_1a

这就是我想要的:

Result:
name    count
1212    1
1243    1
3142    1
2315    1
8562    1

Answer 1

如果我们总是想返回 count 值 1，那么当行数计数超过 1 时...那么我们实际上并没有返回计数。

名称的模式是什么...它们以数字和字母结尾，还是下划线字符也很重要？

如何处理诸如 12345a 或 5678_b2 或 11_22_3b 之类的名称值？该如何处理？

在我看来，我们想要使用这样的 SQL 查询:

SELECT SUBSTRING_INDEX(t.name,'_',1)  AS `foo`
     , 1                              AS `count`
  FROM ( 
         SELECT 2 AS `id`, '1212_1a' AS `name`
         UNION ALL SELECT 3, '1243_1a'
         UNION ALL SELECT 7, '3142_1a'
         UNION ALL SELECT 24, '1243_2a'
         UNION ALL SELECT 30, '3142_2b'
         UNION ALL SELECT 80, '2315_2b'
         UNION ALL SELECT 136, '1243_3b'
         UNION ALL SELECT 512, '8562_1a'
       ) t
 GROUP BY `foo`
 ORDER BY `foo`

内联 View (派生表)在查询中进行测试。将其替换为表格引用:

SELECT SUBSTRING_INDEX(t.name,'_',1)  AS `foo`
     , 1                              AS `count`
  FROM mytable t
 GROUP BY `foo`
 ORDER BY `foo`

可能需要调整 foo 的表达式，以获得不遵循常规模式的值的所需行为。考虑没有下划线、有多个下划线、结尾不是数字的名称值。我们还可以包含一个 WHERE 子句来排除不遵循该模式的行，

WHERE t.name REGEXP '_[0-9][a-z]$'

(仅限以下划线、数字、小写字母结尾的 name 值)。

如果没有经过测试的 SQL 查询，我不知道要在 CodeIgniter 查询生成器中实现什么。