mysql - SQL:查询多个表，只有一个表的最新结果，以及另一个表的所有结果

Question

我有很多表，我试图在一个查询中从其中的许多表中提取数据。我已经弄清楚如何成功地使用 LEFT JOIN 从其他表中提取相关数据，并使用一个公共(public) ID。

通过此查询，我试图为我们的 ASSOCIATES_BACKGROUND_DATA 表中的每个联系人获取数据。

首先，我试图在 ASSOCIATES_1_TO_1S.DATE_OF_MEETING 中找到他们最近的 session (这被存储为时间戳，所以只寻找最大的时间戳)- 我没有得到正确的结果，我是否正确使用了 MAX 函数？

其次，我想获取 ASSOCIATES_ACTION_STEPS 中该 ASSOCIATE_ID 的所有记录。正确的连接似乎也没有将其拉入？

有人可以帮忙吗？感觉我离得到结果很近了，但这真的让我很烦恼!

SELECT *, 
        ASSOCIATES_BACKGROUND_DATA.ASSOCIATE_ID as ABG_ASSOCIATE_ID,
            ASSOCIATES_BACKGROUND_DATA.NAME_KNOWN_AS as ABG_NAME_KNOWN_AS, 
        ASSOCIATES_BACKGROUND_DATA.LAST_NAME as ABG_LAST_NAME, 
        USERS.NAME_KNOWN_AS as USERS_NAME_KNOWN_AS, 
        USERS.LAST_NAME as USERS_LAST_NAME, 
        MAX(ASSOCIATES_1_TO_1S.DATE_OF_MEETING)
    FROM ASSOCIATES_BACKGROUND_DATA
 LEFT JOIN ASSOCIATES_1_TO_1S
        ON ASSOCIATES_BACKGROUND_DATA.ASSOCIATE_ID = ASSOCIATES_1_TO_1S.ASSOCIATE_ID
 LEFT JOIN LIST_OF_UNIVERSITIES
        ON ASSOCIATES_BACKGROUND_DATA.UNIVERSITY = LIST_OF_UNIVERSITIES.ID
 LEFT JOIN USERS
        ON ASSOCIATES_BACKGROUND_DATA.PROGRAMME_COORDINATOR = USERS.ID
 RIGHT JOIN ASSOCIATES_ACTION_STEPS
        ON ASSOCIATES_BACKGROUND_DATA.ASSOCIATE_ID = ASSOCIATES_ACTION_STEPS.ASSOCIATE_ID
  GROUP BY ASSOCIATES_1_TO_1S.ASSOCIATE_ID
  ORDER BY `ABG_NAME_KNOWN_AS` ASC

非常感谢您提供的任何帮助或指点...

Answer 1

我检查了你的查询。对我来说，第一步是重新格式化它以使其更具可读性(如果您花时间查询非常重要，您不需要任何开销)。

之后，我回顾了逻辑并得出了 2 个结论:

• 你不需要正确的加入
• 您的分组算法不太正确。

请查看此查询并告诉我是否缺少某些内容:

SELECT 
    abg.ASSOCIATE_ID as ABG_ASSOCIATE_ID,
    abg.NAME_KNOWN_AS as ABG_NAME_KNOWN_AS, 
    abg.LAST_NAME as ABG_LAST_NAME, 
    u.NAME_KNOWN_AS as USERS_NAME_KNOWN_AS, 
    u.LAST_NAME as USERS_LAST_NAME, 
    t.lastmeeting
FROM ASSOCIATES_BACKGROUND_DATA abg 
        LEFT JOIN (
            SELECT 
                abg.ASSOCIATE_ID as id,
                MAX(a1s.DATE_OF_MEETING) AS lastmeeting
            FROM ASSOCIATES_BACKGROUND_DATA abg
                LEFT JOIN ASSOCIATES_1_TO_1S a1s ON abg.ASSOCIATE_ID = a1s.ASSOCIATE_ID
            GROUP BY abg.ASSOCIATE_ID
            ) t ON t.id = abg.ASSOCIATE_ID
        LEFT JOIN ASSOCIATES_ACTION_STEPS aas ON abg.ASSOCIATE_ID = aas.ASSOCIATE_ID
        LEFT JOIN USERS u ON abg.PROGRAMME_COORDINATOR = u.ID
ORDER BY abg.NAME_KNOWN_AS

我尽量不使用 mysql 在其 group by 函数方面的宽松性(mysql 允许您通过不包括所有选定字段的指令进行分组)，并坚持标准.但是随后有必要进行子查询(参见 t 别名)为每个关联预先计算 MAX(DATE)，稍后将其与主记录集连接回来。

干杯。

PS:你可以再次添加与大学的连接，我跳过它只是为了隔离问题。

PPS:为了反射(reflect)您的评论，我构建了这个查询:

SELECT 
    abg.ASSOCIATE_ID as ABG_ASSOCIATE_ID,
    abg.NAME_KNOWN_AS as ABG_NAME_KNOWN_AS, 
    abg.LAST_NAME as ABG_LAST_NAME, 
    u.NAME_KNOWN_AS as USERS_NAME_KNOWN_AS, 
    u.LAST_NAME as USERS_LAST_NAME, 
    t.lastmeeting,
    t2.av AS averagetimedue
FROM ASSOCIATES_BACKGROUND_DATA abg 
        LEFT JOIN (
            SELECT 
                abg.ASSOCIATE_ID as id,
                MAX(a1s.DATE_OF_MEETING) AS lastmeeting
            FROM ASSOCIATES_BACKGROUND_DATA abg
                LEFT JOIN ASSOCIATES_1_TO_1S a1s ON abg.ASSOCIATE_ID = a1s.ASSOCIATE_ID
            GROUP BY abg.ASSOCIATE_ID
            ) t ON t.id = abg.ASSOCIATE_ID
        LEFT JOIN (
            SELECT
                (AVG(DATE_DUE)+AVG(DATE_COMPLETED))/2 AS av,
                ASSOCIATE_ID
            FROM ASSOCIATES_ACTION_STEPS
            GROUP BY ASSOCIATE_ID
            ) t2 ON abg.ASSOCIATE_ID = t2.ASSOCIATE_ID
        LEFT JOIN ASSOCIATES_ACTION_STEPS aas ON abg.ASSOCIATE_ID = aas.ASSOCIATE_ID
        LEFT JOIN USERS u ON abg.PROGRAMME_COORDINATOR = u.ID
ORDER BY abg.NAME_KNOWN_AS

不过，我不确定您对 DATE_DUE 和 DATE_COMPLETED 之间的平均时间 的定义。