mysql - 使用子查询的 SQL

Question

我想为特定事件制作一个这样的列表，其中当前注册的志愿者数量低于所需的数量。我正在使用 MySQL:

Volunteer Role
==============
Stewarding
Video

(急救未出现，因为该团队已有 2 人报名)

我的表格是:

Delegate
========
OrderId   Name      volunteerOption
1         Tim       
1         Jane      First Aid
1         Mary      First Aid
1         Jo        Stearding


VolunteerRole
===========
eventId   volunteerName   quantityNeeded
1         First Aid       2
1         Stewarding      10


Orders
======
Id        eventId
1         1

Event
=====
id      name
1       Fun Run

我认为我需要使用带有连接的子查询，但我不知道如何链接它们。

我有两个疑问是:

SELECT roleName, quantity as size
FROM VolunteerRole
WHERE eventId = 1

这给了我团队名称和最大规模

SELECT DISTINCT count(volunteerOption) as volunteers, volunteerOption
FROM delegate
JOIN orders on delegate.orderId = orders.id
WHERE orders.eventId = 1 and volunteerOption <> '' 
GROUP BY volunteerOption

这给了我每个团队当前志愿者的数量。我不明白的是如何只选择志愿者数量小于最大值的团队。

感谢任何帮助

Answer 1

请尝试以下操作...

SELECT volunteerName AS 'Volunteer Role'
FROM ( SELECT volunteerName AS volunteerName,
              quantityNeeded AS quantityNeeded,
              COUNT( volunteerOption ) AS volunteersCount
       FROM Delegate
       JOIN Orders ON Delegate.OrderId = Orders.Id
       RIGHT JOIN VolunteerRole ON Orders.eventId = VolunteerRole.eventId
                               AND Delegate.volunteerOption = VolunteerRole.volunteerName
       WHERE Orders.eventId = 1
       GROUP BY volunteerName,
                quantityNeeded
     ) AS volunteersCountFinder
WHERE quantityNeeded > volunteersCount
GROUP BY volunteerName;

该语句首先执行 INNER JOIN之间Delegate和Orders ，为我们提供了分配给每个订单以及每个事件的代表列表。

然后将该列表右连接到 VolunteerRole ，为我们提供分配给每个事件以及该事件中每个角色的代表列表。一个RIGHT JOIN执行而不是 INNER JOIN这样，即使没有分配代表，事件中的角色仍然会列出。

请注意 RIGHT JOIN与 LEFT JOIN 非常相似。您使用哪一个取决于 JOIN 的哪一侧具有应保留不匹配记录的表。

然后，两个连接产生的数据集将被细化为仅具有 eventId 的记录。的1通过WHERE条款。

然后，精炼后的数据集按 volunteerName 分组。 。按 quantityNeeded 分组不能有效地细化或扩大 volunteerName 的分组因为 volunteerName 的每个值将只有一个对应的值 quantityNeeded ，但是GROUP BY要求您使用不是由聚合函数生成的所有字段进行分组。

每个非 NULL 的计数volunteerOption 的值然后计算。 COUNT()将返回0角色没有分配代表的情况，即遇到 NULL 的情况值而不是非NULL值为 volunteerOption (不要与这里的 volunteerName 混淆，它在每条记录中总是有一个值)。

子查询然后返回一个由每个 volunteerName 组成的列表。及其对应的quantityNeeded和volunteersCount (为我们的计数指定的别名)。

然后，主查询将子查询的数据集细化为所需数量大于分配的志愿者数量和小组结果 volunteerName 的数据集。 。 volunteerName 的每个值然后语句返回该组中的内容。

如果您有任何问题或意见，请随时发表评论。

附录

如果您希望扩展此语句以列出所有角色未充分填补的事件以及每个未充分填补的角色，您可以使用...

SELECT eventId,
       volunteerName AS 'Volunteer Role'
FROM ( SELECT Orders.eventId AS eventId,
              volunteerName AS volunteerName,
              quantityNeeded AS quantityNeeded,
              COUNT( volunteerOption ) AS volunteersCount
       FROM Delegate
       JOIN Orders ON Delegate.OrderId = Orders.Id
       RIGHT JOIN VolunteerRole ON Orders.eventId = VolunteerRole.eventId
                               AND Delegate.volunteerOption = VolunteerRole.volunteerName
       GROUP BY Orders.eventId,
                volunteerName,
                quantityNeeded
     ) AS volunteersCountFinder
WHERE quantityNeeded > volunteersCount
GROUP BY eventId,
         volunteerName;