复杂查询A表一对多B表，查询a表对应的b表日期最大值

最新推荐文章于 2024-04-25 18:38:03 发布

原创最新推荐文章于 2024-04-25 18:38:03 发布 · 1.7k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#select

本文介绍如何在一对多的关系中，通过SQL查询从A表关联B表，找出A表每个记录对应的B表最大日期。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
    *
FROM
    (
        SELECT
            l.Address,
            u.id,
            l.Lat,
            l.Log,
            u.Account,
            ui.Name,
            u.State,
            row_number () OVER (ORDER BY [Time] DESC) AS row_number
        FROM
            Users u,
            UserInfo ui,
            Location l
        WHERE
            u.id = l.Userid
        AND u.id = ui.UsersId
        AND u.State IN (0, 1)
        AND l.[time] = (
            SELECT
                MAX ([Time])
            FROM
                Location
            WHERE
                UserId = l.Userid
        )
        AND l.Comid = 24
    ) t
WHERE
    t.row_number > 0
AND t.row_number <= 12
这样查出来的数据，当a表的用户没有time字段，那么数据就不显示，如果我们需要所有数据，代码如下：
SELECT
    *
FROM
    (
        SELECT
            l.Address,
            u.id,
            l.Lat,
            l.Log,
            u.Account,
            ui.Name,
            u.State,
            row_number () OVER (ORDER BY [Time] DESC) AS row_number
        FROM
            Users u,
            UserInfo ui,
            Location l
        WHERE
            u.id = l.Userid
        AND u.id = ui.UsersId
        AND u.State IN (0, 1)
        AND l.[time] = (
            SELECT
                MAX ([Time])
            FROM
                Location
            WHERE
                UserId = l.Userid
        )
        AND l.Comid = 24
    ) t
WHERE
    t.row_number > 0
AND t.row_number <= 12