窗口函数是一种方法。假设一个人的动作不重复：

select pa.*
from (select p.*, a.action, count(*) over (partition by p.id) as num_actions
      from people p join
           action a
           on p.id = a.pplid
      where a.action in (1, 2)
     ) pa
where num_actions = 2;

在我看来，获得两行的动作细节似乎是多余的-你已经知道的行动。如果你只想要人民，那么 exists 我想到：

select p.*
from people p
where exists (select 1 from actions where a.pplid = p.id and a.action = 1) and
      exists (select 1 from actions where a.pplid = p.id and a.action = 2);

使用正确的索引( actions(pplid, action) )，我希望有两个 exists 比…快 group by .

使用subquery和join尝试下面的查询

select a.Pplid, name, phone, actionid from (
select a.pplid as Pplid, name, phone_number as phone
from People P
join Action A on a.pplid= p.id
group by a.pplid, name, phone_number
having count(*)>1 )P
join Action A on a.Pplid= p.Pplid

试试这个

IF OBJECT_ID('tempdb..#People') IS NOT NULL DROP TABLE #People
CREATE TABLE #People (id INT, name VARCHAR(255), phone_number VARCHAR(50))
INSERT #People
SELECT 1, 'John', '111111111111' UNION ALL
SELECT 3, 'Jane', '222222222222' UNION ALL
SELECT 4, 'Jack', '333333333333' 
IF OBJECT_ID('tempdb..#Action') IS NOT NULL DROP TABLE #Action
CREATE TABLE #Action (id INT, PplId INT, ActionId INT)
INSERT #Action
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 2, 1 UNION ALL
SELECT 4, 4, 2
GO
SELECT      p.ID                AS PplId
            , p.name
            , p.phone_number    AS Phone
            , a.ActionId
FROM        #People p
JOIN        #Action a
ON          p.ID = a.PplId
WHERE       p.ID IN (   SELECT      PplId 
                        FROM        #Action
                        WHERE       ActionId IN (1, 2) 
                        GROUP BY    PplId
                        HAVING      COUNT(*) = 2    )
            AND a.ActionId IN (1, 2)
GO