sqlite - 使用sqlite查找连续重复的数量

Question

我有一个数据库表，其中包含与每个条目名称相关联的8个数字的条目。因此，我需要编写一个查询来获取联系人姓名的总数，该联系人姓名具有与每个联系人姓名相关的重复号码。
我有一个sqlite表，如下图所示


姓名| Num0 | Num1 | Num2 | Num3 | Num4 | Num5 | Num6 |数字7


假设我有6000个条目，并且有100个条目具有重复的编号，这些编号可以落在从TelNum0到TelNum8开始的任何列中。

例如：如下所示，我有一个数据库，其中有5个条目，共有5个名称，例如：

名称Num0 Num1 Num2 Num3 Num4 Num5 Num6 Num7
1）约翰1234 1234 1234

2）Hebbar 234

3）吉姆9876 9876 9876

4）金111111111

5）凯特666


现在，当我运行查询时，由于约翰，吉姆和金与他们有重复的条目，因此我应该将结果数设为3。

在此先多谢

Answer 1

首先，正确规范化数据：

SELECT Name, Num0 AS Num FROM MyTable WHERE Num0 IS NOT NULL
UNION ALL
SELECT Name, Num1        FROM MyTable WHERE Num1 IS NOT NULL
UNION ALL
SELECT Name, Num2        FROM MyTable WHERE Num2 IS NOT NULL
UNION ALL
SELECT Name, Num3        FROM MyTable WHERE Num3 IS NOT NULL
UNION ALL
SELECT Name, Num4        FROM MyTable WHERE Num4 IS NOT NULL
UNION ALL
SELECT Name, Num5        FROM MyTable WHERE Num5 IS NOT NULL
UNION ALL
SELECT Name, Num6        FROM MyTable WHERE Num6 IS NOT NULL
UNION ALL
SELECT Name, Num7        FROM MyTable WHERE Num7 IS NOT NULL;

Name   Num------ ----John   1234John   1234John   1234Hebbar 234Jim    9876Jim    9876Jim    9876Kim    111Kim    111Kim    111Kate   666

Then just count the duplicates:

SELECT Name, Num, COUNT(*)
FROM (SELECT Name, Num0 AS Num FROM MyTable WHERE Num0 IS NOT NULL
      UNION ALL
      SELECT Name, Num1        FROM MyTable WHERE Num1 IS NOT NULL
      UNION ALL
      SELECT Name, Num2        FROM MyTable WHERE Num2 IS NOT NULL
      UNION ALL
      SELECT Name, Num3        FROM MyTable WHERE Num3 IS NOT NULL
      UNION ALL
      SELECT Name, Num4        FROM MyTable WHERE Num4 IS NOT NULL
      UNION ALL
      SELECT Name, Num5        FROM MyTable WHERE Num5 IS NOT NULL
      UNION ALL
      SELECT Name, Num6        FROM MyTable WHERE Num6 IS NOT NULL
      UNION ALL
      SELECT Name, Num7        FROM MyTable WHERE Num7 IS NOT NULL)
GROUP BY Name, Num
HAVING COUNT(*) > 1;