sql—选择一个表中包含另一个表中具有特定id的两条记录的所有记录

mitkmikd  于 2021-07-29  发布在  Java
关注(0)|答案(3)|浏览(399)

我有两张1:m关系的表。需要选择哪些人员记录在id为1和2的操作表中同时包含这两个记录

People
+----+------+--------------+
| id | name | phone_number |
+----+------+--------------+
| 1  | John | 111111111111 |
+----+------+--------------+
| 3  | Jane | 222222222222 |
+----+------+--------------+
| 4  | Jack | 333333333333 |
+----+------+--------------+

Action
+----+------+------------+
| id | PplId| ActionId   |
+----+------+------------+
| 1  |   1  |      1     |
+----+------+------------+
| 2  |   1  |      2     |
+----+------+------------+
| 3  |   2  |      1     |
+----+------+------------+
| 4  |   4  |      2     |
+----+------+------------+

Output
+----+------+--------------+----------
|PplId| name | Phone       |ActionId |
+-----+------+-------------+----+-----
| 1   | John | 111111111111|   1     |
+-----+------+-------------+----+-----
| 1   | John | 111111111111|   2     |
+-----+------+-------------+----+-----

返回同时具有actionid 1和actionid 2的人员的记录(具有actions中的记录)。

798qvoo8

798qvoo81#

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

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 .

uinbv5nw

uinbv5nw2#

使用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
ckx4rj1h

ckx4rj1h3#

试试这个

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

相关问题