查找具有相同id的记录并放在同一行

Question

我有三个简单的表格，分别是Actor、Film和Film_actor。 Film_actor 表格：

 Column     | Type                        | Modifiers
------------+-----------------------------+----------
actor_id    | smallint                    | not null
film_id     | smallint                    | not null

演员表：

 Column     | Type                        | Modifiers
------------+-----------------------------+----------
actor_id    | integer                     | not null 
first_name  | character varying(45)       | not null
last_name   | character varying(45)       | not null

电影演员表：

 Column     | Type                        | Modifiers
------------+-----------------------------+----------
film_id     | integer                     | not null
title       | character varying(255)      | not null

我的目标是找到一组演员，他们一起出演电影最多，并列出这些电影的标题。将结果按字母顺序排序。此外，第一个演员的actor_id应小于第二个演员的actor_id。所需输出如下：

first_actor     | second_actor  | title
----------------+---------------+---------------
Daniel Craig    | Eva Green     | Casino Royale

我能想到的最好的方法就是创建一个新的派生表，然后尝试获取数据，但这个查询会返回很多重复的行：

SELECT
     CASE
        WHEN ac.actor_id < te.actor_id THEN ac.first_name
        ELSE te.first_name
     END
   , CASE
        WHEN ac.actor_id > te.actor_id THEN te.first_name
        ELSE ac.first_name
     END
   , fi.title
   , fi.film_id
FROM Film_Actor as fa
INNER JOIN Actor as ac ON fa.actor_id = ac.actor_id
INNER JOIN Film as fi ON fa.film_id = fi.film_id
INNER JOIN ( SELECT a_c.first_name, a_c.last_name, a_c.actor_id  FROM 
Film_Actor as f_a 
    INNER JOIN Actor as a_c ON f_a.actor_id = a_c.actor_id) te
    ON te.actor_id=fa.actor_id

大家好，请帮忙查询一下，因为我只有基本的SQL知识。提前感谢！

这不是作业，只是一个我需要做的简单例子。真正的表格不是电影和演员。我的知识可以进行连接，但我不知道如何将相同的演员放在一行中。现在我受限于我的知识，但我正在努力学习！

- Learner

---

似乎是作业。你应该尝试解决它并展示你的工作。 - Marichyasana

@Marichyasana 这不是作业，只是我需要做的一个简单示例。真正的表格不是“Films”和“Actors”。我的知识可以进行连接，但我不知道如何将相同的演员放在一行中。如果您能，请澄清一下。 - Learner

我认为你可以使用CTE按演员ID进行分区（https://sqlbelle.com/2014/09/24/deleting-duplicates-using-cte-and-row_number-ranking-function/），然后使用Pivot将它们放在同一行中（https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0ahUKEwjss4TdzrfYAhUrw4MKHRMLCdIQFggpMAA&url=https%3A%2F%2Ftechnet.microsoft.com%2Fen-us%2Flibrary%2Fms177410(v%3Dsql.105).aspx&usg=AOvVaw3Wbvadcb9mhVndwXPEPh5y）。 - Miniver Cheevy

Answer 1

您可以尝试这个查询。

;WITH TwoActors AS (
    SELECT   
        ac1.actor_id first_actor_id
        , ac1.first_name + ' ' + ac1.last_name   first_actor
        , ac2.actor_id second_actor_id
        , ac2.first_name + ' ' + ac2.last_name second_actor
        ,count(*) FilmCount
    FROM
        Actor as ac1 
        INNER JOIN Film_Actor as fa1 ON fa1.actor_id = ac1.actor_id
        INNER JOIN Film_Actor as fa2 ON fa1.film_id = fa2.film_id
        INNER JOIN Actor as ac2 ON fa2.actor_id = ac2.actor_id and fa1.actor_id < ac2.actor_id
        INNER JOIN Film as fi ON fa1.film_id = fi.film_id
    GROUP BY 
        ac1.actor_id
        , ac1.first_name
        , ac1.last_name   
        , ac2.actor_id
        , ac2.first_name
        , ac2.last_name

)
SELECT * FROM TwoActors
CROSS APPLY ( SELECT fi.title , fi.film_id 
                FROM  Film_Actor fa 
                INNER JOIN Film as fi ON fa.film_id = fi.film_id 
                WHERE fa.actor_id IN ( TwoActors.first_actor_id, TwoActors.second_actor_id )
                GROUP BY fi.title , fi.film_id
                HAVING COUNT(*) > 1 )AS F
ORDER BY FilmCount DESC

- Serkan Arslan

Answer 2

希望这对您有用。

逐个组合所有的演员，然后得到他们一起放置的文件。

CREATE TABLE #Film_actor(actor_id INT,film_id INT )
CREATE TABLE #Actor(actor_id INT,first_name VARCHAR(100),last_name VARCHAR(100))
CREATE TABLE #film(film_id INT,title VARCHAR(255))
INSERT INTO #Actor(actor_id,first_name,last_name)
SELECT 1,'Daniel','Craig' UNION ALL
SELECT 2,'Eva','Green' UNION ALL
SELECT 3,'John','White' 
INSERT INTO #film(film_id,title)
SELECT 1,'Casino Royale' UNION ALL
SELECT 2,'Windows' UNION ALL
SELECT 3,'Film3' UNION ALL
SELECT 4,'Film4' UNION ALL
SELECT 5,'Film5' 
INSERT INTO #Film_actor(actor_id,film_id)
SELECT 1,1 UNION ALL
SELECT 2,1 UNION ALL
SELECT 3,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 1,3 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,5

SELECT a1.first_name+' '+a1.last_name AS first_actor,
       a2.first_name+' '+a2.last_name AS second_actor,
       f.title
       ,COUNT(0)OVER(PARTITION BY a1.actor_id,a2.actor_id) TotalFileCount
FROM #Actor AS a1 INNER JOIN #Actor AS a2 ON a1.actor_id!=a2.actor_id
INNER JOIN #Film_actor AS fa1 ON fa1.actor_id=a1.actor_id
INNER JOIN #Film_actor AS fa2 ON fa2.film_id=fa1.film_id AND fa2.actor_id=a2.actor_id
LEFT JOIN #film AS f ON f.film_id=fa1.film_id

第一演员    第二演员    电影标题    文件总数
丹尼尔·克雷格    伊娃·格林    《赌场 Royale》   1
丹尼尔·克雷格    约翰·怀特    《赌场 Royale》   2
丹尼尔·克雷格    约翰·怀特    Windows 2
伊娃·格林    丹尼尔·克雷格    《赌场 Royale》   1
伊娃·格林    约翰·怀特    《赌场 Royale》   1
约翰·怀特    丹尼尔·克雷格    Windows 2
约翰·怀特    丹尼尔·克雷格    《赌场 Royale》   2
约翰·怀特    伊娃·格林    《赌场 Royale》   1

- Nolan Shang

---

谢谢！您能否请澄清一下PARTITION BY a1.actor_id,a2.actor_id这一行的含义是什么？ - StepUp