mysql - 关系划分

Question

我应该为这条语句写一个查询:

List the names of customers, and album titles, for cases where the customer has bought the entire album (i.e. all tracks in the album)

我知道我应该使用除法。

这是我的答案，但我遇到了一些无法解决的奇怪语法错误。

SELECT 
     R1.FirstName
     ,R1.LastName
     ,R1.Title
FROM (Customer C, Invoice I, InvoiceLine IL, Track T, Album Al) AS R1
WHERE 
     C.CustomerId=I.CustomerId 
     AND I.InvoiceId=IL.InvoiceId
     AND T.TrackId=IL.TrackId 
     AND Al.AlbumId=T.AlbumId
     AND NOT EXISTS ( 
          SELECT 
               R2.Title
           FROM (Album Al, Track T) AS R2
           WHERE 
                T.AlbumId=Al.AlbumId
                AND R2.Title NOT IN (
                    SELECT R3.Title
                    FROM (Album Al, Track T) AS R3
                    WHERE 
                        COUNT(R1.TrackId)=COUNT(R3.TrackId)
                )
     );

错误:滥用聚合函数 COUNT()

您可以找到数据库的架构 here

Answer 1

您不能为表列表添加别名，例如 (Album Al, Track T)，这是 (Album Al CROSS JOIN Track T) 的过时语法。您可以为表格起别名，例如Album Al 或子查询，例如(SELECT * FROM Album CROSS JOIN Track) AS R2。

所以首先你应该把你的连接弄直。我不认为您正在学习那些旧的逗号分隔连接，而是从一些旧书或网站上获得的？请改用适当的显式连接。

那么你就不能使用 WHERE COUNT(R1.TrackId) = COUNT(R3.TrackId)。 COUNT 是一个聚合函数，聚合在WHERE 之后完成。

至于查询:比较轨道数是个好主意。那么让我们一步一步来吧。

查询以获取每张专辑的轨道数:

select albumid, count(*)
from track
group by albumid;

查询以获取每个客户和专辑的轨道数:

select i.customerid, t.albumid, count(distinct t.trackid)
from track t
join invoiceline il on il.trackid = t.trackid
join invoice i on i.invoiceid = il.invoiceid
group by i.customerid, t.albumid;

完成查询:

select c.firstname, c.lastname, a.title
from
(
  select i.customerid, t.albumid, count(distinct t.trackid) as cnt
  from track t
  join invoiceline il on il.trackid = t.trackid
  join invoice i on i.invoiceid = il.invoiceid
  group by i.customerid, t.albumid
) bought
join
(
  select albumid, count(*) as cnt
  from track
  group by albumid
) complete on complete.albumid = bought.albumid and complete.cnt = bought.cnt
join customer c on c.customerid = bought.customerid
join album a on a.albumid = bought.albumid;