我刚刚使用sqlite3通过以下sql命令重现了您的场景：

CREATE TABLE tablename (
    docid INT,
    State VARCHAR(2)
);
INSERT INTO tablename VALUES (12,'MP');
INSERT INTO tablename VALUES (13,'MP');
INSERT INTO tablename VALUES (14,'MP');
INSERT INTO tablename VALUES (13,'CG');
INSERT INTO tablename VALUES (14,'CG');
INSERT INTO tablename VALUES (12,'MH');
INSERT INTO tablename VALUES (13,'MH');
INSERT INTO tablename VALUES (16,'DL');

然后，我可以查询您正在使用的内容：

SELECT mp.docid AS Docid
FROM tablename AS mp
JOIN tablename AS cg ON mp.docid = cg.docid
JOIN tablename AS mh ON cg.docid = mh.docid
WHERE mp.State='MP' AND cg.State='CG' AND mh.State='MH';

它回来了 13

这是一个关系划分问题。一种通用的方法是使用 having 计算 state 可在下表中找到：

select docid
from mytable
group by docid
having count(*) = (select count(distinct state) from mytable)

这假设没有重复 (docid, state) . 否则，您需要：

having count(distinct state) = (select count(distinct state) from mytable)

如果我对你的理解正确的话，不是一个优雅但正确的解决方案应该是：

select distinct
    t1.docid
from
    table as t1
join table as t2 on t1.docid=t2.docid
join table as t3 on t3.docid=t2.docid
where
    t1.state <> t2.state
    and t2.state<>t3.state
    and t1.state<>t3.state

我认为这可以解决你的问题：

SELECT
   DISTINCT YT.docid
FROM <YOUR_TABLE> AS YT
WHERE
  1 = 1
  AND YT.user_id = <ID_USER>
  AND YT.State IN ('MP', 'CG', 'MH')
GROUP BY
  YT.docid, YT.State

如果您想查看docid的n个最常见的值以及您想要的状态，则增加1
sql服务器

SELECT  [docid],
         COUNT([docid]) AS value_occurrence 
FROM    [SQLtable]
WHERE   [state] = "what ever state you want"
GROUP BY [docid]
ORDER BY value_occurrence DESC
FETCH FIRST 1 ROW ONLY;

并非所有数据库都支持这种语法。许多支持 LIMIT ```
SELECT [docid],
COUNT([docid]) AS value_occurrence
FROM [SQLtable]
WHERE [state] = "what ever state you want"
GROUP BY [docid]
ORDER BY value_occurrence DESC
LIMIT 1;