where子句用于最后一个非空值

Question

我正在为一个学校项目制作一个社区数据库，遇到了一个问题。我试图集成一个日志系统，但检索最新的非空值，从列在一个表称为日志，并在不同的页面上呈现该信息。我当前的代码(没有任何尝试按它们的排名进行过滤)如下所示:

SELECT m.MemberID, m.MemberName, o.OfficeID, o.OfficeDesignation, p.PositionAbbreviation, l.LogRank
FROM logs l
INNER JOIN (SELECT l.LogMember, MAX(l.LogDate) AS maxLogDate FROM logs l GROUP BY l.LogMember) l2
ON (l.LogMember = l2.LogMember AND l.LogDate = l2.maxLogDate)
INNER JOIN members m
ON (l.LogMember = m.MemberID)
INNER JOIN offices o
ON (l.LogOffice = o.OfficeID)
INNER JOIN positions p
ON (l.LogPosition = p.PositionID)
GROUP BY m.MemberID;

上面的查询返回日志表中每个成员的最新条目，但是我不知道如何，当l.LogRank返回NULL时，仅为该列获取最新的非空值。

在过去的一周里，我尝试了各种方法来解决这个问题，但都无济于事。如有任何帮助或指示，将不胜感激。

编辑:示例数据如下:

+-------+-----------+---------+-----------+-------------+-----------+
| LogID | LogMember | LogRank | LogOffice | LogPosition |  LogDate  |
+-------+-----------+---------+-----------+-------------+-----------+
|   1   |     1     |    1    |     7     |      5      | TIMESTAMP |
+-------+-----------+---------+-----------+-------------+-----------+
|   2   |     1     |    1    |           |      1      | TIMESTAMP |
+-------+-----------+---------+-----------+-------------+-----------+
|   3   |     1     |         |     1     |             | TIMESTAMP |
+-------+-----------+---------+-----------+-------------+-----------+

各种INT值引用其他相关表中的id。

所需输出:

+-------+-----------+---------+-------------------+-----------------+-----------+
| LogID | LogMember | LogRank | OfficeDesignation | PositionAbbrev. |  LogDate  |
+-------+-----------+---------+-------------------+-----------------+-----------+
|   1   |     1     |    1    |        C-6        |        CO       | TIMESTAMP |
+-------+-----------+---------+-------------------+-----------------+-----------+

所以基本上我想从各种表中检索MemberID、MemberName、OfficeID、OfficeDesignation、position缩写和LogRank，并为每列获取最新的非空记录。

Answer 1

INNER JOIN (
SELECT l.LogMember, l.LogRank FROM logs l
WHERE l.LogDate=(
SELECT LogDate FROM logs
WHERE LogMember=l.LogMember
AND LogRank IS NOT NULL
ORDER BY LogDate DESC
LIMIT 1
)
) l3
ON (l.LogMember = l3.LogMember)