{"id":440188,"date":"2021-04-13T01:40:18","date_gmt":"2021-04-12T17:40:18","guid":{"rendered":"http:\/\/4563.org\/?p=440188"},"modified":"2021-04-13T01:40:18","modified_gmt":"2021-04-12T17:40:18","slug":"%e4%b8%80%e9%a2%98-mysql-%e6%9f%a5%e8%af%a2%e9%a2%98%e6%b1%82%e8%a7%a3","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=440188","title":{"rendered":"\u4e00\u9898 MySQL \u67e5\u8be2\u9898\u6c42\u89e3"},"content":{"rendered":"
\n
\n
\n

\u4e00\u9898 MySQL \u67e5\u8be2\u9898\u6c42\u89e3 <\/h1>\n

<\/p>\n

\n
\u8cc7\u6df1\u5927\u4f6c : WaterWestBolus <\/span> <\/i> 6<\/span> <\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n
<\/p>\n

\u9500\u552e\u8868 sales_dtl \u6709\u57ce\u5e02(city)\uff0c\u9500\u552e\u7ecf\u7406(cbm)\uff0c\u5ba2\u6237(customer)\uff0c\u9500\u552e\u91d1\u989d(sale_amt)\u4e09\u5217\u5b57\u6bb5\uff0c\u8bf7\u7528 sql \u53d6\u51fa\u6bcf\u4e2a\u57ce\u5e02\u4e2d\u9500\u552e\u91d1\u989d\u4e3a\u672c\u57ce\u5e02\u524d 10 \u540d\u7684\u9500\u552e\u7ecf\u7406\u7684\u4fe1\u606f\u3002<\/p>\n

\u662f\u4e00\u9898\u9762\u8bd5\u9898\uff0c\u8feb\u4e8e SQL \u6280\u672f\u592a\u5dee\u5199\u4e0d\u51fa\u7b54\u6848\u3002\u770b\u4e86\u7f51\u4e0a\u535a\u5ba2\u7684\u8d44\u6599\uff0c\u6ca1\u770b\u61c2\u4e5f\u6ca1\u8dd1\u8d77\u6765\u3002\u6545\u6765\u6c42\u89e3<\/p>\n<\/p><\/div>\n

\u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (20<\/span>) <\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n
    \n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : acr0ss <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u5178\u578b\u7684 top k \u9898\u578b\uff0c\u7b54\u9898\u601d\u8def\u5927\u81f4\u662f\u4e24\u79cd\u3002
    1. \u8fde\u8868\u67e5\u8be2\uff08\u4e00\u822c\u89e3\u6cd5\uff09
    2. \u7a97\u53e3\u51fd\u6570\uff08 MySQL version >= 8.0 \uff09<\/p>\n

    \u5177\u4f53\u53ef\u4ee5\u53c2\u8003\uff1a [leetcode-cn ]( https:\/\/leetcode-cn.com\/problems\/department-top-three-salaries\/comments\/852822\/)\uff0c\u6709**\u5b98\u65b9\u9898\u89e3**\u3002 <\/p><\/div>\n<\/p><\/div>\n<\/li>\n

  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : Oktfolio <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    select cbm, sum(sale_amt) as amt
    from sales_dtl
    group by cbm
    order by amt desc
    limit 10 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : Ptu2sha <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u4e0a\u679c\u7136\u4e0d\u5ba1\u9898 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : Oktfolio <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u5662\uff0c\u6bcf\u4e2a\u57ce\u5e02… <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : Ptu2sha <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    substring_index(group_concat(cbm order by sale_amt),10) from sales_dtl group by city
    \u5927\u81f4\u8fd9\u6837\u5199\u5373\u53ef <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : WaterWestBolus <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @acr0ss thanks\uff0c\u770b\u5b98\u65b9\u7684\u6709\u70b9\u7d2f\uff0c\u6211\u518d\u7814\u7a76\u4e00\u4e0b\u3002 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : WaterWestBolus <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @Ptu2sha \u8bd5\u4e86\u4e00\u4e0b\u4e0d\u592a\u884c\u3002\u3002\u6211\u518d\u770b\u770b\u3002\u8c22\u8c22 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : nuistzhou <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    window function \u5427 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : Ptu2sha <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @WaterWestBolus \u62a5\u9519\u4e86 substring_index \u5c11\u5199\u4e86 \uff0c \u3001\u4f60\u67e5\u4e0b\u6587\u6863
    \u601d\u8def\u5c31\u662f\u6309\u57ce\u5e02\u5206\u7ec4 \u91cc\u9762\u7684\u9500\u552e\u989d\u6392\u5e8f\u5f97\u5230\u9500\u552e \u7136\u540e\u53d6\u524d\u5341 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : acr0ss <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @WaterWestBolus \u5b98\u7f51\u9898\u89e3\u533a\u73b0\u6210\u7684\u8bed\u53e5\uff0c\u8fd8\u6709\u4e2d\u6587\u89e3\u91ca\u3002 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : shine20070522 <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    select t.city, substring_index(group_concat(t.cbm order by sale_amt desc),’,’,10)
    from (
    select city,cbm, sum(sale_amt) sale_amt from sales_dtl group by 1,2
    ) t
    group by 1; <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : yeqizhang <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u4e0d\u662f\u56db\u5217\u5417\u2026\u2026 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : akira <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u53ea\u4f1a\u9010\u4e2a\u57ce\u5e02\u53d6 \u7136\u540e\u4ee3\u7801\u5408\u5e76\u4e00\u8d77\u3002\u3002\u3002 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : xiaolanger <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u8fd9\u4e2a\u5f97\u7528\u4e0a\u53d8\u91cf\u4e86 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u4e3b<\/span> \u8cc7\u6df1\u5927\u4f6c : WaterWestBolus <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @shine20070522 \u975e\u5e38\u611f\u8c22\uff0c\u6210\u4e86\uff01 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : raaaaaar <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    select city, cbm, sale_amt
    from sales_dtl as t1
    where 10 > (
    select count(*)
    from sales_dtl as t2
    where t1.city = t2.city
    and t1.sale_amt < t2.sale_amt
    ); <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : raaaaaar <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u5927\u81f4\u60f3\u4e86\u4e0b\uff0c\u91cd\u70b9\u662f\u8fd9\u4e2a\uff1a
    \u5047\u8bbe\u4e00\u5171 n \u4e2a\u6570\uff0c\u5982\u679c\u662f\u4ece\u5927\u5f80\u5c0f\u6392\uff0c\u90a3\u4e48\u6392\u540d\u7b2c m\uff0c\u5c31\u6bd4\u8fd9\u4e2a\u6570\u5927\u7684\u6570\u7684\u6570\u76ee\uff0c\u5c31\u662f m-1\uff0c\u4e5f\u5c31\u662f\u8bf4\u5b83\u524d\u9762\u53c8 m -1 \u4e2a\u6570\uff0c\u90a3\u4e48\u8981\u627e\u524d k \u4e2a\u6570\uff0c\u53ea\u9700\u8981\u6bd4\u8fd9\u4e2a\u6570\u5927\u7684\u6570\u76ee\u7684\u4e2a\u6570\u5c0f\u4e8e k \u5c31\u884c\u4e86\uff0c\u6240\u4ee5\u8fd9\u91cc\u5916\u9762\u904d\u5386\u786e\u5b9a\u4e00\u4e2a\u6570\uff0c\u7136\u540e\u91cc\u9762\u627e\u5230\u6bd4\u8fd9\u4e2a\u6570\u5927\u7684\u6570\u76ee\u5c0f\u4e8e 10 \u7684\u5c31\u884c\u4e86 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : akiraX <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u7ec4\u5185\u81ea\u589e\u5e8f\u53f7\u8f85\u52a9
    select * from (
    select a.*,
    case when @pre_city=a.city then @n:[email protected] + 1 else @n:=1 end as i, # \u5224\u65ad\u57ce\u5e02\u662f\u5426\u5207\u6362\uff0c\u6ca1\u5207\u6362\u7d2f\u52a0\u5e8f\u53f7\uff0c\u5207\u6362\u4e86\u4ece 1 \u5f00\u59cb
    @pre_city := a.city # \u8bb0\u5f55\u5f53\u524d\u57ce\u5e02\u5230 pre_city\uff0c\u7528\u4e8e\u4e0b\u4e00\u884c\u5224\u65ad\uff0c\u8fd9\u4e00\u5217\u5fc5\u987b\u5728\u4e0a\u9762\u8fd9\u5217\u540e\u9762
    from (
    select city,cbm,sum(sale_amt) as total from sales_dtl group by city,cbm order by city,total desc # \u5206\u7ec4\u6c42\u548c\uff0c\u6309\u57ce\u5e02\u3001\u603b\u91d1\u989d\u5012\u5e8f\u6392\u5e8f
    ) a <\/p>\n

    ) b where b.i <= 10 # \u53d6 top n <\/p><\/div>\n<\/p><\/div>\n<\/li>\n

  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : xxlee <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    \u597d\u5947 mysql \u6ca1\u6709\u7a97\u53e3\u51fd\u6570 row_number over \u4e48 <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
    \u8cc7\u6df1\u5927\u4f6c : zc832097 <\/span> <\/div>\n
    <\/i> <\/span> <\/div>\n<\/p><\/div>\n
    @xxlee 8.0 \u624d\u652f\u6301\u7a97\u53e3\u51fd\u6570 \u73b0\u5728\u597d\u591a\u90fd\u8fd8\u5728\u7528 5.x <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n","protected":false},"excerpt":{"rendered":"

    \u4e00\u9898 MySQL \u67e5\u8be2\u9898\u6c42\u89e3 \u8cc7\u6df1…<\/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\/440188"}],"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=440188"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/440188\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=440188"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=440188"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=440188"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}