mysql查找与匹配值不匹配的匹配键
问题描述:
我正在处理有一些重复数据的数据库,并试图编写一个查询来解决问题数据。该表看起来像:mysql查找与匹配值不匹配的匹配键
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。没有任何我尝试似乎工作。我怎样才能做到这一点?
答
你试过
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
真棒,谢谢。我仍然有很多要学习与MySQL ... – baiano