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   

视频(标题，导演，胶体)；

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

租金(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

但我错过了最后一步，我不能适当地使用组/拥有。即使有子查询也没有成功。我一直在尝试使用一种不同的方法，但我没有什么好的，所以任何提示都会被感激。

谢谢。

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

解决方案:由于你的技巧，我找到了一个解决方案(老实说，这是部分的，因为它释放了所有的影片，其中没有录影带，至少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

Answer 2

尝尝这个

SELECT 
-- if you want to add colloc, uncomment below
-- r.colloc as COLLOC
m.title as TITLE,
m.director as DIRECTOR,
COUNT(r.colloc) as VIDEOSNUMBER

FROM movie m
LEFT JOIN video v ON m.title = v.title
LEFT JOIN rent r ON v.colloc = r.colloc
GROUP BY TITLE

Answer 3

测试了它：

SELECT m.TITLE, m.DIRECTOR, COALESCE(rs.Rentnumber, 0) AS VIDEOSNUMBER
FROM Movie AS m
LEFT JOIN 
(
    SELECT 
        --r.colloc, -- if you want to see r.colloc
        v.TITLE, 
        v.DIRECTOR, 
        COUNT(v.TITLE) AS Rentnumber
    FROM Video AS v
    LEFT JOIN Rent as r on v.colloc = r.colloc 
    GROUP BY r.colloc, v.TITLE, v.DIRECTOR
) AS rs ON m.title = rs.title AND m.director = rs.director
Group by m.title, m.director