仅选择具有 MAX 日期或 NULL 的行 [英] SELECT only rows with either the MAX date or NULL

问题描述

我需要一个查询来生成我们所有成员及其相应状态的非重复列表国家(以及从其他表连接的其他一些数据).每个成员可能有 0 到多个 MemberAddress 记录.如果成员有 MemberAddress 记录，我只想加入最近修改过的记录.如果该成员没有任何关联的 MemberAddress 记录，我仍然希望该成员显示在列表中，但州和国家/地区将为 NULL 值.

I need a query that will produce a non duplicate list of all of our members and their corresponding states & countries (along with some other data that is joined from other tables). Each member may have 0-many MemberAddress records. If a member has MemberAddress records, I would like to join only to the record that has been modified most recently. If the member does not have any associated MemberAddress records, I still want the member to show in the list, but the state and country would then be NULL values.

SELECT m.member, ma.state, ma.country FROM Member m
 LEFT OUTER JOIN MemberAddress ma ON m.member = ma.member           
 INNER JOIN (SELECT Member, MAX(Modified) AS MaxDate
        FROM MemberAddress
        GROUP BY Member) AS m2
    ON (ma.Member = m2.Member AND ma.Modified = m2.MaxDate)

此查询删除了由于成员具有多个 MemberAddress 记录而导致的重复项，但它不允许没有任何 MemberAddress 记录的成员.

This query removes the duplicates caused when a member has multiple MemberAddress records, however it does not allow for members that do not have any MemberAddress records.

如何更改此查询以同时显示没有任何 MemberAddress 记录的成员?

How can I alter this query to also show members that do not have any MemberAddress records?

谢谢！！

编辑添加:我使用的是 SQL 2005

Edited to add: I'm using SQL 2005

推荐答案

您走在正确的轨道上，但是 ma 和 m2 之间的连接本身必须是一个完整的子查询.问题是您的 INNER JOIN 适用于整个查询，而不仅仅是适用于 ma 和 m2 之间的关系:

You were on the right track, but the join between ma and m2 has to, itself, be an entire subquery. The problem is that your INNER JOIN applies to the whole query, not just to the relationship between ma and m2:

SELECT m.member, mx.state, mx.country
FROM Member m
 LEFT OUTER JOIN (
SELECT ma.state, ma.country, ma.member from MemberAddress ma            
 INNER JOIN (SELECT Member, MAX(Modified) AS MaxDate
    FROM MemberAddress
    GROUP BY Member) AS m2
ON (ma.Member = m2.Member AND ma.Modified = m2.MaxDate)
) mx ON m.member = mx.member

假设我没有打错任何东西(除了括号，我修正了).

Assuming I didn't typo anything (except that parentheses, which I fixed).

这篇关于仅选择具有 MAX 日期或 NULL 的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！