{"id":94367,"date":"2020-05-02T11:00:34","date_gmt":"2020-05-02T03:00:34","guid":{"rendered":"http:\/\/4563.org\/?p=94367"},"modified":"2020-05-02T11:00:34","modified_gmt":"2020-05-02T03:00:34","slug":"%e6%9c%89-v2-%e5%a4%a7%e4%bd%ac%e6%8c%87%e5%af%bc%e4%b8%80%e6%9d%a1-sql-%e8%af%ad%e5%8f%a5%e6%80%8e%e4%b9%88%e5%86%99%e5%90%97","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=94367","title":{"rendered":"\u6709 V2 \u5927\u4f6c\u6307\u5bfc\u4e00\u6761 sql \u8bed\u53e5\u600e\u4e48\u5199\u5417?"},"content":{"rendered":"
\n
\n
\n

\u6709 V2 \u5927\u4f6c\u6307\u5bfc\u4e00\u6761 sql \u8bed\u53e5\u600e\u4e48\u5199\u5417? <\/h1>\n

<\/p>\n

\n
\u8cc7\u6df1\u5927\u4f6c : cobainlu <\/span> <\/i> 15<\/span> <\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n
\u6709\u4e00\u7ec4\u6570\u636e<\/p>\n

| id | context(\u5185\u5bb9) | time(\u4fee\u6539\u6b21\u6570) | bizNo(\u6d41\u6c34\u53f7) | bizNo_Key(\u4e3b\u6d41\u6c34\u53f7) |
| —- | ————- | ————– | ————- | ——————- |
| 1 | \u7565 | 0 | 1 | 1 |
| 2 | \u7565 | 0 | 2 | 2 |
| 3 | \u7565 | 1 | 3 | 2 |
| 4 | \u7565 | 1 | 4 | 1 |
| 5 | \u7565 | 2 | 5 | 1 |<\/p>\n

\u60f3\u8981\u67e5\u8be2\u5f97\u5230\u8fd9\u6837\u7684\u4e00\u7ec4\u6570\u636e,\u5df2 bizNo_key \u5206\u7ec4,\u83b7\u53d6\u6bcf\u4e00\u7ec4\u91cc\u9762\u4fee\u6539\u6b21\u6570\u6700\u591a\u7684\u90a3\u6761\u6570\u636e,\u5e0c\u671b\u5927\u4f6c\u4eec\u7ed9\u70b9\u601d\u8def.<\/p>\n

| id | context(\u5185\u5bb9) | time(\u4fee\u6539\u6b21\u6570) | bizNo(\u6d41\u6c34\u53f7) | bizNo_Key(\u4e3b\u6d41\u6c34\u53f7) |
| —- | ————- | ————– | ————- | ——————- |
| 3 | \u7565 | 1 | 3 | 2 |
| 5 | \u7565 | 2 | 5 | 1 <\/div>\n

\u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (14<\/span>) <\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n
    \n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : Godykc <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    |\u5173\u952e\u8bcd OVER PARTITION BY\uff0c\u7136\u540e\u6839\u636e\u4f60\u7528\u7684\u6570\u636e\u5e93\u7c7b\u578b\u81ea\u5df1\u5199\u7740\u8bd5\u8bd5\uff0c\u4e0d\u96be\u7684 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : gochat <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    “`sql
    select *
    from ttt as a
    left join(
    select max(time) as m, bizNo_key
    from ttt
    group by bizNo_key
    ) as b on (a.bizNo_key = b.bizNo_key and a.time = b.m);
    “`
    \u591a\u8bd5\u8bd5 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : Yourshell <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u540c\u662f\u840c\u65b0\uff0c\u96be\u9053\u4e0d\u662f max+group by \u5417 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : cobainlu <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @Godykc
    @gochat <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : cobainlu <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @Godykc
    @gochat
    \u611f\u8c22\u5927\u4f6c,\u6211\u53bb\u7814\u7a76\u4e00\u4e0b <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : cobainlu <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @Yourshell select max \u7684\u8bdd\u4e0d\u80fd\u53d6\u5230\u6240\u6709\u6570\u636e\u5440 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : FanGanXS <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    SELECT
    id,context,MAX(time),bizNo,bizNo_Key
    FROM
    table_name
    GOURP BY
    bizNo_Key <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : vindac <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    SELECT * FROM( SELECT id, @rank := IF (@perNO = bizNo_Key, @rank + 1, 1) AS rank, @perNO = bizNo_Key FROM tbl ORDER BY bizNo_Key, time DESC) t WHERE t.rank = 1 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : vindac <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @vindac \u4e0d\u597d\u610f\u601d\uff0c\u4e0d\u5c0f\u5fc3 at \u5230\u4e86\u5404\u4f4d <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : MOONYANYI <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u8e0f\u5b9e\u7684\u5c31\u662f left join,\u7b80\u6d01\u7684\u5c31\u7528\u7a97\u53e3\u51fd\u6570 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : liubian <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    SELECT * FROM (SELECT id,context,time,bizNo,bizNo_Key FROM table_name ORDER BY time DESC) t GROUP BY bizNo_Key <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : forrestgump <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @FanGanXS \u4f60\u8fd9\u4e2a\u662f\u8ba4\u771f\u7684\u5417\uff0cgroup by \u8fd8\u80fd\u67e5\u8be2\u975e\u805a\u5408\u5b57\u6bb5\u4ee5\u5916\u7684\u975e\u6392\u5e8f\u5b57\u6bb5\uff1f <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : ackoly <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u540d\u79f0\u6570\u636e\u5e93\u7684\u8bed\u6cd5\u4e0d\u540c\uff0c\u5efa\u8bae\u63d0\u95ee\u5e26\u4e0a\u6570\u636e\u5e93\u540d\u79f0\u548c\u7248\u672c <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : FanGanXS <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @forrestgump Oracle \u8fd8\u771f\u53ef\u4ee5 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n","protected":false},"excerpt":{"rendered":"

    \u6709 V2 \u5927\u4f6c\u6307\u5bfc\u4e00\u6761 sql \u8bed…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[],"tags":[],"_links":{"self":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/94367"}],"collection":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=94367"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/94367\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=94367"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=94367"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=94367"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}