• 请不要在回答技术问题时复制粘贴 AI 生成的内容
ackfin01
V2EX  ›  程序员

SQL 在分组查询时,怎么获取最新一条记录

  •  
  •   ackfin01 · Nov 30, 2018 · 8612 views
    This topic created in 2908 days ago, the information mentioned may be changed or developed.

    表 A 和表 B,为 1 对多关系,如何查询对于某个 A,多个 B 中最近更新的那一条记录

    14 replies  •  2018-11-30 18:21:56 +08:00
    gzq527
    gzq527
    Nov 30, 2018
    1
    每组最新的那一条?
    ackfin01
    ackfin01
    OP
    Nov 30, 2018
    2
    @gzq527 嗯 有个字段纪录了更新时间
    loongwang
    loongwang
    Nov 30, 2018
    5
    SELECT B.*
    FROM B b1 JOIN
    (select b2.x,max(b.date)
    FROM B b2 JOIN A ON b2.x=A.x
    loongwang
    loongwang
    Nov 30, 2018
    6
    这样行吗,看着有点丑
    SELECT B.*
    FROM B b1 JOIN
    (
    select A.x,max(b.date)
    FROM B b2 JOIN A ON b2.x=A.x
    GROUP BY A.x
    ) C ON b1.x=C.x where b1.date=C.date
    ackfin01
    ackfin01
    OP
    Nov 30, 2018
    7
    @gzq527
    这个只 GROUP BY b.USER_ID; 还可以 SELECT ID,USER_ID,problems,last_updated_date ?
    ackfin01
    ackfin01
    OP
    Nov 30, 2018
    8
    @loongwang em...应该可以,不过感觉用 date 做比较条件,也行吧
    killaboy712
    killaboy712
    Nov 30, 2018
    9
    先把 B 表分组排序,row_number() over(partition by xx order by 时间) as 'rk',然后 select * from B 排序后的表,where rk =1,再将其与 A 表关联
    fanhaipeng0403
    fanhaipeng0403
    Nov 30, 2018
    11
    查询返回每个 id 的前 5

    SELECT yourtable.* FROM yourtable INNER JOIN ( SELECT id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year FROM yourtable GROUP BY id) group_max ON yourtable.id = group_max.id AND FIND_IN_SET(year, grouped_year) BETWEEN 1 AND 5 ORDER BY yourtable.id, yourtable.year DESC
    ackfin01
    ackfin01
    OP
    Nov 30, 2018
    12
    @watzds
    @killaboy712
    学到了~ Thx!
    lueffy
    lueffy
    Nov 30, 2018
    13
    mark 一下
    Magic347
    Magic347
    Nov 30, 2018
    14
    @killaboy712 漂亮,这里需要用到开窗函数,分组之后的 topk 问题的典型用例
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Privacy   ·   Solana   ·   2338 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 13:36 · PVG 21:36 · LAX 05:36 · JFK 08:36
    ♥ Do have faith in what you're doing.