mysql - SQL分组查询

Question

我被 SQL 查询卡住了。我知道要做什么，但不知道怎么做。所以，这里是需要的方案:

电影(片名、导演、年份、类型、评分)；

TITLE         DIRECTOR     YEAR      GENRE      RATE 
Fight club    Fincher      1999      Action     4.5
Vertigo       Hitchock     1958      Drama      5
Donnie darko  Kelly        2001      Thriller   3.5   

Video(title, director, colloc);

TITLE          DIRECTOR    COLLOC
Fight club     Fincher     3877
Fight club     Fincher     3878
Vertigo        Hitchcock   5431
Vertigo        Hitchcock   5432
Donnie darko   Kelly       9986

Rent(colloc, dateRent, customer, dateReturn);

  COLLOC      DATERENT     CUSTOMER     DATERETURN 
  3877        2016-05-02   324          2016-05-04
  3877        2016-05-20   365          2016-05-20
  3878        2016-04-11   876          2016-04-12
  3878        2016-06-06   112          2016-06-08
  ...         ...          ...          ...
  ...         ...          ...          ...
  9986        2016-02-24   443          2016-02-28

这里是查询:

列出，对于每部电影，至少有多少视频被租借了两次。

(注意:商店通常为每部电影提供更多视频 - dvds、vhs 等)。

我的方法如下:我将从这个简单的查询开始

SELECT colloc, title, director, COUNT(colloc) AS rentNumber
FROM Rent
NATURAL JOIN Video
GROUP BY colloc

要显示这样的内容:

COLLOC    TITLE         DIRECTOR    RENTNUMBER
3877      Fight club    Fincher     2
3878      Fight club    Fincher     2
5432      Vertigo       Hitchcock   2
5431      Vertigo       Hitchcock   1
9986      Donnie darko  Kelly       1 

为了做到这一点:

TITLE          DIRECTOR      VIDEOSNUMBER
Fight club     Fincher       2
Vertigo        Hitchcock     1
Donnie darko   Kelly         0

但是我错过了这最后一步，我不能适本地使用 GROUP BY/HAVING。即使使用子查询也没有成功。我一直在尝试使用不同的方法，但结果一无所获，因此我们将不胜感激。

谢谢。

编辑:注意，无需在查询中使用表 Movie。我把它放在那里只是为了解释一部电影可以有多个视频。添加了表格示例。主键以粗体显示。

解决方案:多亏了你的提示，我找到了一个解决方案(老实说它是部分解决方案，因为它放出了所有没有租借至少 2 次的视频的电影)。无论如何，这里是:

SELECT title, director, COUNT(title) AS videosNumber
FROM (SELECT colloc, title, director, COUNT(colloc) AS rentNumber
      FROM rent NATURAL JOIN video
      GROUP BY colloc
      ) X
WHERE rentNumber > 1
GROUP BY title

Answer 1

试试这个查询:

SELECT t1.TITLE,
       t1.DIRECTOR,
       COALESCE(t2.VIDEOSNUMBER, 0) AS VIDEOSNUMBER
FROM Movie t1
LEFT JOIN
(
    SELECT v.TITLE, COUNT(DISTINCT r.COLLOC) AS VIDEOSNUMBER
    FROM Rent r
    INNER JOIN Video v
        ON r.COLLOC = v.COLLOC
    GROUP BY v.TITLE
) t2
   ON t1.TITLE = t2.TITLE
ORDER BY COALESCE(t2.VIDEOSNUMBER, 0) DESC