MYSQL——查询未列出正在使用的三个表之一的所有记录

Question

我有 3 个表。 TBL_C=成员(member)名字和姓氏列表 TBL_B=每个成员(member)提供的团体类(class)列表 TBL_C=成员(member)付款和当前成员(member)状态列表。我需要输出成员的名字和姓氏 (TBL_A) 以及他们根据 groupTheme 选择 (TBL_A) 提供的所有组。除了列表之外，如果它们的状态为“当前”(TBL_B)，我还需要在名称前加上“*”。

我尝试了以下方法，但它只打印该特定 groupTheme 的第一条记录。例如，如果有人在不同的日子 (TBL_A) 教 2 个小组，我想同时看到这两个小组。

 SELECT a.mID, a.ageGroup, a.groupTheme, firstName, lastName, a.dayAvail, a.startTime, a.endTime, b.memberType, MAX( b.paymentDate ) , b.id
    FROM referrals a
    LEFT JOIN members c ON a.mID = c.id
    INNER JOIN membership b ON a.mID = b.memberID
    WHERE a.ageGroup ='preSchool'
    GROUP BY a.mID
    ORDER BY lastName

我相信 GROUPBY 函数不允许超过一条记录输出，而它只是从 TBL_B 选择最新的付款记录，因此是当前状态。

谢谢。

Answer 1

从您的问题来看，您似乎正在尝试使用 MAX(b.paymentDate) 提取成员(member)的最近付款日期。你说得对，你的 GROUP BY 消除了重复项。但是你的问题并没有很清楚地解释这个特定的要求，所以我要猜测。

如果您使用此查询而不是您提供的查询会怎样？

    SELECT a.mID, a.ageGroup, a.groupTheme, 
           c.firstName, c.lastName, 
           a.dayAvail, a.startTime, a.endTime
      FROM referrals AS a
 LEFT JOIN members   AS c ON a.mID = c.id
     WHERE a.ageGroup ='preSchool'
  ORDER BY lastName, a.groupTheme

您的结果集中的行数是否正确？ (显然，您没有从 membership 表中获取列；我们没有将其包含在查询中)。

如果是这样，您就有了一个好的开始。下一步是针对您的 membership 表编写摘要查询(聚合查询)。此查询需要为每个成员返回一行。您没有告诉我们关于该表的任何信息，所以我猜，但这是一个可能的子查询。我猜你在 membership 中有一行表示成员(member)支付的每一笔款项。所以我们需要去找到最新付款对应的行。

我们正在做的是在membership 表中为每个成员寻找最大的id 编号。我猜测该表中的 id 列是一个 AUTO_INCREMENT 列，这意味着最高的 id 值是最新的条目。所以这是一个查询，让我们了解每个成员的小事实。这个小查询为每个成员提供一行。

  SELECT MAX(id) AS id, memberID
    FROM membership
   GROUP BY memberId

接下来，我们需要将该查询加入到您的整体查询中以获取最新信息。

看起来像这样:

    SELECT a.mID, a.ageGroup, a.groupTheme, 
           c.firstName, c.lastName, 
           a.dayAvail, a.startTime, a.endTime
      FROM referrals AS a
 LEFT JOIN members   AS c ON a.mID = c.id
INNER JOIN (    SELECT MAX(id) AS id, 
                       memberID
                  FROM membership
              GROUP BY memberId
            ) AS latestId ON a.mID = latestId.id
     WHERE a.ageGroup ='preSchool'
  ORDER BY lastName, a.groupTheme

你看到这里的技巧了吗？这是有效使用 SQL 的关键。我们加入了一个子查询，就好像它是一个表一样。做到这一点的能力非常重要:这就是将结构化放入结构化查询语言的原因。

最后，我们需要从成员资格表中获取最新的行并在您的 SELECT 子句中使用它。这需要另一个 JOIN。

    SELECT a.mID, a.ageGroup, a.groupTheme, 
           c.firstName, c.lastName, 
           a.dayAvail, a.startTime, a.endTime,
           b.id, b.memberType, b.paymentDate
      FROM referrals AS a
 LEFT JOIN members   AS c ON a.mID = c.id
INNER JOIN (    SELECT MAX(id) AS id,
                       memberID
                  FROM membership
              GROUP BY memberId
            ) AS latestId  ON a.mID = latestId.memberID
INNER JOIN membership AS b ON latestId.id = b.id 
     WHERE a.ageGroup ='preSchool'
  ORDER BY lastName, a.groupTheme

将 GROUP BY 移动到子查询可以防止它清除结果集中实际需要的行。

对这些 SQL 东西要有耐心；它实际上确实有意义，即使它看起来不像。