mysql - 使用子查询的SQL

我想针对特定事件生成这样的列表，其中当前注册的志愿者数量低于所需数量。我正在使用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

这给了我每个团队目前的志愿者数量。我不知道的一点是如何只选择志愿者人数少于最大人数的团队。

任何帮助表示感谢

最佳答案

请尝试以下...

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;