mysql 找到匹配的键与不匹配的值

Question

我正在使用一个包含一些重复数据的数据库，并尝试编写一个查询来提取问题数据。该表如下所示:

lpID, pID, pName, etc...

其中 lpID 是该表的唯一 ID，pID 是整个数据库的共享 ID。我用这个来提取匹配的 pID 值 (16k):

SELECT lp.pID, group_concat(lp.lpID) as lpIDs, group_concat(lp.pName) as names
FROM lp
INNER JOIN (
  SELECT pID
  FROM lp
  GROUP BY pID
  HAVING count(pID) > 1
) dup ON lp.pID = dup.pID
group by lp.pID

这有效，但现在我正在尝试扩展它，以便它只提取 pName 值不匹配的 938。我尝试的一切似乎都不起作用。我怎样才能做到这一点？

Answer 1

你试过吗

SELECT lp.pID, group_concat(lp.lpID) as lpIDs, group_concat(lp.pName) as names
FROM lp
INNER JOIN (
  SELECT pID, COUNT(distinct pName) AS Names
  FROM lp
  GROUP BY pID
  HAVING count(pID) > 1 AND Names > 1
) dup ON lp.pID = dup.pID
group by lp.pID