sql - 在有计数中查找重复元素

Question

我有一个计数，我检测到重复多次的元素。

select MstDocCabId, count (*) as 'Repetition Number'
from skmov (nolock)
group by MstDocCabId
having count(MstDocCabId) > 1

因此，结果类似于：

+-------------+-------------------+
| MstDocCabId | Repetition Number |
+-------------+-------------------+
| 16513519    | 4                 |
+-------------+-------------------+

所以，如果我让select * from skmov where MstDocCabId=16513519我可以看到 4 个具有相同 mstdoccabid 的元素，例如

+----------+----------+
|  MstID   |   Col2   |
+----------+----------+
| 38600690 | 16513519 |
| 38600691 | 16513519 |
| 38600692 | 16513519 |
| 38600693 | 16513519 |
+----------+----------+

对于重复mstdoccabid的每一行，都有一个名为product的列，对于每种情况，它可能或应该具有不同的值：

+-----------+-------------+---------+
| MstID     | mstdoccabid | Product |
+-----------+-------------+---------+
| 38600690  | 16513519    | AAAA    |
| 38600691  | 16513519    | BBBB    |
| 38600692  | 16513519    | CCCC    |
| 38600693  | 16513519    | DDDD    |
+-----------+-------------+---------+

我的问题是如何检测重复mstdoccabid以及产品相同的情况（例如产品 AAAA）。

+-----------+-------------+---------+
| MstID     | mstdoccabid | Product |
+-----------+-------------+---------+
| 38600690  | 16513519    | AAAA    |
| 38600691  | 16513519    | BBBB    |
| 38600692  | 16513519    | CCCC    |
| 38600693  | 16513519    | AAAA    |
+-----------+-------------+---------+

标签： sql

Answer 1

分组Product也是如此，它将起作用：

select MstDocCabId, count (*) as 'Repetition Number'
from skmov (nolock)
group by MstDocCabId, Product
having count(MstDocCabId) > 1