MySQL查询连接，在两列上选择，在另一列上不相等

Question

我的工作查询如下。但是，该查询的结果将在名称列上产生重复项和非重复项。我希望能够仅显示两个选择查询中的名称列不同的结果

select t.*
from tbl_user_tmp t JOIN
(select activity, class, count(*) as NumDuplicates
from tbl_user_tmp
where user = 'bignadad2'
group by activity, class
having NumDuplicates > 1)
tsum ON t.activity = tsum.activity and t.class = tsum.class

列按此顺序排列

id、名称、事件、类别、activity_id

我只想显示事件、类(class)匹配和名称不匹配的这些结果。

2059    lg_lmk          com.lge.lmk    com.lge.lmk.activities.LmkMainActivity   48255
3668    task_manager    com.lge.lmk    com.lge.lmk.activities.LmkMainActivity   48255

这些是我不想看到的其他结果

2690    phone   com.modoohut.dialer    com.modoohut.dialer.DialActivity    54700
2694    phone   com.modoohut.dialer    com.modoohut.dialer.DialActivity    54700

Answer 1

我忘了你只需要一些结果

SELECT * FROM tbl_user_tmp AS t1
INNER JOIN (
    SELECT activity, class, COUNT(1) AS cnt FROM tbl_user_tmp
    WHERE user = 'first'
    GROUP BY activity, class
    HAVING cnt > 1
) AS t2
ON t1.activity = t2.activity AND t1.class = t2.class

WHERE user = 'first' -- remove records of the other users
GROUP BY t1.name, t1.activity, t1.class -- select distinct records

SQLFiddle

如果class在activity中是唯一的，那么您可以从GROUP BY语句中删除activity。