没有 LIMIT 的 MySQL SELECT 查询

Question

我正在上关系数据库类(class)，更具体地说是 MySQL。我们需要为项目创建一些 SELECT 查询。该项目与音乐有关。它有代表音乐家 (musician)、乐队 (band) 和音乐家完成特定任务的能力的表格，比如唱歌或弹吉他 (act)。

表格musician包含:

id
name
stagename
startyear

表格band包含:

code
name
type ("band" or "solo")
startyear

最后，表格 act包含:

band     (foreign key to code of "band" table)
musician (foreign key to id of "musician" table)
hability (guitarist, singer, like that... and a foreign key to another table)
earnings

我对两个练习有疑问，第一个要求选择音乐家id和 stagename在类型为 solo 的乐队中参与更多表演的人.

我对第一个的解决方案是这样的:

SELECT ma.id, ma.stagename
FROM musician ma, act d, band ba
WHERE ma.id = d.musician
AND ba.code = d.band
AND ba.type = "solo"
GROUP BY ma.id, ma.stagename
HAVING COUNT(ma.id) = (SELECT COUNT(d2.musician) AS count
                        FROM act d2, band ba2
                        WHERE d2.band = ba2.code
                        AND ba2.type = "solo"
                        GROUP BY d2.musician
                        ORDER BY count DESC
                        LIMIT 1);

第二个与最后一个非常相似。我们需要为每个 startyear 选择, id和 stagename一个可以做更多表演的音乐家，具有相应的表演次数以及他的声望的最大值和最小值。这是我的解决方案:

SELECT ma.startyear, ma.id, ma.stagename, COUNT(ma.id) AS NumActs, MIN(d.earnings), MAX(d.earnings) 
FROM musician ma, act d, band ba
WHERE ma.id = d.musician
AND ba.code = d.band
AND ba.type = "solo"
GROUP BY ma.year, ma.id, ma.stagename
HAVING COUNT(ma.id) = (SELECT COUNT(d2.musician) AS count
                        FROM act d2, band ba2
                        WHERE d2.band = ba2.code
                        AND ba2.type = "solo"
                        GROUP BY d2.musician
                        ORDER BY count DESC
                        LIMIT 1);

我的虚拟数据的结果是完美的，但我的老师告诉我们应该避免使用 LIMIT选项，但这是我们获得最高数字的唯一方法，至少就我们目前所知道的而言。

我在 FROM 之后看到了很多子查询语句来解决这个问题，但是，对于这个项目，我们不能在 FROM 中使用子查询。 .如果没有 LIMIT 这真的可能吗？ ？

提前致谢。

Answer 1

这是可能的，但比 from 或 limit 中的子查询差得多。所以我永远不会在现实生活中使用它:)

好吧，长话短说，你可以这样做:

SELECT
      m.id
    , m.stagename
FROM
    musician m
    INNER JOIN act a ON (
            a.musician = m.id
    )
    INNER JOIN band b ON (
            b.code = a.band
        AND b.type = 'solo'
    )
GROUP BY
      m.id
    , m.stagename
HAVING
    NOT EXISTS (
        SELECT
            *
        FROM
            act a2
            INNER JOIN band b2 ON (
                    b2.code = a2.band
                AND b2.type = 'solo'
            )
        WHERE
            a2.musician != a.musician
        GROUP BY
            a2.musician
        HAVING
            COUNT(a2.musician) > COUNT(a.musician)
    )
;

我认为您可以从查询本身理解这个想法，因为它非常简单。但是，如果您需要解释，请告诉我。

您的限制可能略有不同，您不允许仅在主 FROM 部分中使用子查询。

附言我还使用 INNER JOIN ... ON 语法，因为这样更容易看出什么是表连接条件以及什么是 where 条件。

附言这可能是查询错误，因为我没有你的数据结构，所以无法执行查询和检查。我只检查了这个想法是否适用于我的测试表。