SQL - 我的查询不起作用

I have 3 tables:

我有3张桌子:

Art (oid,subject,type)
Artist (aid, name)

艺术家(援助,姓名)

Artistic (oart (as a foreign key to oid), author (as a foreign key to aid))

艺术(oart(作为oid的外键),作者(作为援助的外键))

So, i have to find the subjects and the number of artists for every type of art with the minimum number of artists.

因此,我必须找到每种类型艺术家的艺术家的主题和艺术家数量。

I wrote the following but apparently it's not working. Can anybody help me?

我写了以下内容,但显然它不起作用。有谁能够帮我?

SELECT o.subject, COUNT(au.autor)  count
FROM Artistic au, Art o
WHERE o.oid=au.autor
GROUP BY o.type
HAVING count = (SELECT MIN(count) FROM Artistic au);

2 个解决方案

#1

just use the calculated column, not the ALIAS.
use also proper join syntax

只使用计算列,而不是ALIAS。使用正确的连接语法

SELECT o.subject, 
       COUNT(au.autor) count
FROM   Artistic au
       INNER JOIN Art o
          ON o.oid = au.autor
GROUP  BY o.type, o.subject
HAVING COUNT(au.autor) = (SELECT MIN(count) FROM Artistic au)

#2

Your problem is: "I have to find the subjects and the number of artists for every type of art with the minimum number of artists".

你的问题是:“我必须找到每种类型艺术家的艺术家的主题和艺术家人数”。

This type of query is a bit of a pain in MySQL. You need to aggregate by type and subject, then choose the subjects that have the minimum count. That requires additional join operations:

这种类型的查询在MySQL中有点痛苦。您需要按类型和主题进行聚合,然后选择具有最小计数的主题。这需要额外的连接操作:

SELECT ts.`type`, ts.subject, ts.count
FROM (SELECT a.`type`, a.subject, COUNT(au.autor) as `count`
      FROM Artistic au join
           Art a
           on a.oid = au.autor
      GROUP BY a.`type`, a.subject
     ) ts join
     (select a.`type`, min(`count`) as mincount
      from (SELECT a.type, a.subject, COUNT(au.autor) as `count`
            FROM Artistic au join
                 Art a
                 on a.oid = au.autor
            GROUP BY a.type, a.subject
           ) ts
      group by a.`type`
     ) t
     on t.`type` = ts.`type`
ORDER BY `count` desc;

#1