mysql - 如何按类型计算唯一 ID 的数量(按整组中的第三个字段)

Question

我正在努力解决以下问题:http://sqlfiddle.com/#!2/b6a22/13

我有一个数据集:

| ID | NAME | TYPE | ACC_FK |                              TS |
|----|------|------|--------|---------------------------------|
|  1 |  qwe |    3 |    123 | November, 19 2013 00:43:33+0000 |
|  2 |  asd |    3 |    234 | November, 19 2013 00:43:33+0000 |
|  3 |  zxc |    2 |    123 | November, 19 2013 00:43:33+0000 |
|  4 |  wer |    2 |    345 | November, 19 2013 00:43:33+0000 |

我需要按类型计算唯一(通过整组中的 acc_fk)id 的数量。因此结果应基于以下数据集(不包括“zxc”或“qwe”)，例如:

| ID | NAME | TYPE | ACC_FK |                              TS |
|----|------|------|--------|---------------------------------|
|  1 |  qwe |    3 |    123 | November, 19 2013 00:43:33+0000 |
|  2 |  asd |    3 |    234 | November, 19 2013 00:43:33+0000 |
|  4 |  wer |    2 |    345 | November, 19 2013 00:43:33+0000 |

“acc_fk 在整个集合中是唯一的”意味着我不想在任何类型组中对相同的 acc_fk 计数两次。查询的结果应该是:

+------------------+
| Type | COUNT(id) |
+------+-----------+
|  2   |    1      |
|  3   |    2      |
+------------------+

因此，对于类型 2，我想看到“wer”，对于类型 3，我想看到:“qwe”和“asd”。

非常感谢任何帮助。

Answer 1

• @Mosty Mostacho :

  It is clear that there are only 3 different acc_fk in the whole table. Now, why are you counting one of them for type 2 and two of them for trype 3? Why not the other way around?

• @Horoshiy :

  You're right, other way would be ok also. I've updated question. Makes sense?

这是一个非常奇怪的问题，因为它会导致不确定的结果。

SELECT   type, COUNT(*)
FROM     (SELECT acc_fk, type FROM t1 GROUP BY acc_fk) t
GROUP BY type

查看 sqlfiddle .