{"id":341,"date":"2021-08-12T02:22:48","date_gmt":"2021-08-11T18:22:48","guid":{"rendered":"https:\/\/blog.cauchyschwarz.com\/?p=341"},"modified":"2021-12-04T22:20:29","modified_gmt":"2021-12-04T14:20:29","slug":"hackerrank%e5%88%b7%e9%a2%98%e7%ac%94%e8%ae%b0%e4%b9%8bsql","status":"publish","type":"post","link":"https:\/\/blog.cauchyschwarz.com\/?p=341","title":{"rendered":"Hackerrank\u5237\u9898\u7b14\u8bb0\u4e4bSQL"},"content":{"rendered":"\n
  1. \u67e5\u8be2\u4ee5aeiou\u5b57\u6bcd\u5f00\u5934\u7684\u57ce\u5e02<\/a><\/li><\/ol>\n\n\n\n
       SELECT DISTINCT(CITY) FROM STATION\n   WHERE CITY REGEXP '^[aeiou].*'<\/code><\/pre>\n\n\n\n
    \u4e3b\u8981\u662f\u8bb0\u5f97\u4f7f\u7528REGEXP<\/strong>\u51fd\u6570\uff0c\u548c\u6b63\u5219\u8868\u8fbe\u5f0f\u8981\u7528^<\/strong>\u8868\u793a\u5f00\u5934\u3002
    \u6216\u8005\uff1a<\/p>\n\n\n\n
       select distinct city from station \n   where left(city,1) in ('a','e','i','o','u')<\/code><\/pre>\n\n\n\n
    \u7528left<\/strong>\u51fd\u6570\u63d0\u53d6\u5b50\u5b57\u7b26\u4e32\u3002<\/p>\n\n\n\n
    1. \u57fa\u672c\u7684join\u64cd\u4f5c<\/a>
      \u4f7f\u7528join\u5199\u4e24\u4e2a\u8bed\u53e5\uff0c\u5206\u522b\u628a\u957f\u5ea6\u6700\u957f\u548c\u6700\u77ed\u7684\u7ed9\u627e\u51fa\u6765\u3002\u6ce8\u610f\u7684\u662fmysql\u4e0d\u652f\u6301\u5728JOIN ON<\/code>\u8bed\u6cd5\u4e2d\u4f7f\u7528\u524d\u9762\u7528AS<\/code>\u8bbe\u7f6e\u7684\u522b\u540d(\u4f46\u662fclickhouse\u5c31\u53ef\u4ee5)\u3002\u6392\u5e8f\u540e\u4f7f\u7528TOP<\/code>\u6216\u8005LIMIT<\/code>\u8bed\u6cd5\u90fd\u53ef\u4ee5\u3002
      \u987a\u4fbf\u60f3\u8d77\u4e00\u4e2a\u95ee\u9898\uff0c\u90a3\u5c31\u662fTOP<\/code>\u548cLIMIT<\/code>\u6709\u6ca1\u6709\u533a\u522b\u5462\uff0c\u7f51\u4e0a\u641c\u4e86\u4e00\u4e0b\uff0c\u53d1\u73b0\u7684\u552f\u4e00\u533a\u522b\u662fLIMIT<\/code>\u53ef\u4ee5\u518d\u6307\u5b9a\u4e00\u4e2aoffset\uff0c\u8fd9\u6837\u5c31\u53ef\u4ee5\u53d6\u67e5\u8be2\u7ed3\u679c\u4e2d\u95f4\u7684\u4e00\u6bb5\u503c\u4e86\u3002<\/li>
    2. int\u4e0estring\u7684\u8f6c\u6362<\/a>
      \u4f7f\u7528cast<\/strong>\u5728\u6570\u5b57\u548c\u5b57\u7b26\u4e32\u4e4b\u95f4\u6765\u56de\u8f6c\u6362\uff0c\u9700\u8981\u6ce8\u610f\u7684\u662fcast<\/strong>\u8f6c\u6362\u7684\u76ee\u6807\u7c7b\u578b\u662f\u51e0\u4e2a\u56fa\u5b9a\u6b7b\u4e86\u7684\u7c7b\u578b\uff0c\u6240\u8c13\u7684int<\/strong>\u548cvarchar<\/strong>\u4e0d\u5728\u8f6c\u6362\u76ee\u6807\u7c7b\u578b\u4e4b\u5217\u3002
      \u53e6\u5916\u5c31\u662f\u4f7f\u7528REPLACE<\/strong>\u51fd\u6570\u628a0\u7ed9\u66ff\u6362\u6210\u7a7a\u5b57\u7b26\u4e32\u3002<\/li>
    3. \u6253\u5370\u4e09\u89d2\u5f62<\/a>
      \u5173\u952e\u5728\u4e8e\u4f7f\u7528SET<\/strong>\u8bbe\u7f6e\u53d8\u91cf\u548c\u4f7f\u7528REPEAT<\/strong>\u51fd\u6570\u3002
      \u4ee5\u53ca\u4e00\u4e2a\u5145\u5f53\u5360\u4f4d\u7528\u7684information_schema.tables<\/strong>\u3002<\/li>
    4. The PADS<\/a>
      \u5c31\u662f\u7ec3\u4e60\u4e00\u4e0bconcat<\/strong>\u4e0elower<\/strong>\u51fd\u6570\u3002<\/li>
    5. Occupations<\/a>
      \u8fd9\u9053\u9898\u6d89\u53ca\u4e00\u4e2apivot\u7684\u6982\u5ff5(\u5176\u5b9e\u4e0d\u61c2\u8fd9\u4e2a\u6982\u5ff5\u4e5f\u6ca1\u5565\u5173\u7cfb)\uff0c\u5c31\u662f\u6211\u4eec\u4e00\u822c\u7684\u67e5\u8be2\u662f\u7528\u8fd9\u4e2a\u8868\u5efa\u8868\u65f6\u4f7f\u7528\u7684\u5b57\u6bb5\u4f5c\u4e3a\u5217\uff0c\u800cpivot\u5462\u662f\u8bf4\u6211\u4eec\u7528\u5176\u4e2d\u67d0\u4e2a\u5b57\u6bb5\u7684\u679a\u4e3e\u503c\u6765\u4f5c\u4e3a\u5217\u3002
      \u5728mssql\u4e2d\u662f\u6709pivot\u51fd\u6570\u7684\uff0c\u800c\u5728mysql\u4e2d\u5219\u662f\u6ca1\u6709pivot\u51fd\u6570\u7684\uff0c\u9700\u8981\u81ea\u5df1\u5b9e\u73b0\u4e00\u4e2a\u5f31\u667a\u7248\u672c\u7684pivot(\u56e0\u4e3a\u9700\u8981\u63d0\u524d\u77e5\u9053\u5b57\u6bb5\u7684\u6240\u6709\u503c\u6765\u5199case\u8bed\u53e5\u679a\u4e3e)\u3002
      \u6765\u81ea\u4e8e\u8ba8\u8bba\u533a\u7684mysql\u7b54\u6848\uff1a<\/li><\/ol>\n\n\n\n
         SELECT \n   MIN(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor,\n   MIN(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor,\n   MIN(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer,\n   MIN(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor\n   FROM (\n     SELECT a.Occupation,\n            a.Name,\n            (SELECT COUNT(*) \n               FROM Occupations AS b\n               WHERE a.Occupation = b.Occupation AND a.Name > b.Name) AS myrank\n     FROM Occupations AS a\n   ) AS c\n   GROUP BY c.myrank\n   ORDER BY c.myrank<\/code><\/pre>\n\n\n\n
      \u8fd9\u91cc\u9700\u8981\u6ce8\u610f\u7528\u5b50\u67e5\u8be2\u4f5c\u4e3a\u5217\u7684\u8fd9\u79cd\u5199\u6cd5\uff0c\u4e0d\u662f\u770b\u5230\u4eba\u5bb6\u8fd9\u4e48\u5199\u6211\u90fd\u4e0d\u77e5\u9053\u5b50\u67e5\u8be2\u8fd8\u53ef\u4ee5\u4f5c\u4e3aselect\u7684\u4e00\u5217\u3002
      \u9664\u4e86\u7528\u5b50\u67e5\u8be2\uff0c\u4e5f\u53ef\u4ee5\u7528rank over\u51fd\u6570\u6765\u5b9e\u73b0\u8ba1\u7b97\u4e0d\u540c\u804c\u4e1a\u7684\u4eba\u7684\u59d3\u540d\u6392\u540d\uff1a<\/p>\n\n\n\n
         SELECT\n   MIN(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor,\n   MIN(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor,\n   MIN(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer,\n   MIN(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor\n   FROM\n   (\n       SELECT Occupation, Name, rank() over( partition by Occupation order by Name) as myrank\n       FROM Occupations\n   )t\n   group by myrank\n   order by myrank<\/code><\/pre>\n\n\n\n
      \u7528rank over\u51fd\u6570\u9700\u8981\u6ce8\u610f\u7684\u70b9\u662fmysql\u8981\u4ece8.0\u5f00\u59cb\u624d\u6709\u8fd9\u79cdwindow\u51fd\u6570\u3002\u5982\u679c\u662f8.0\u4e4b\u524d\u7684\u7248\u672c\u4f60\u8981\u83b7\u5f97\u8fd9\u4e2a\u6392\u540d\u9700\u8981join\u8868\u6765\u8ba1\u7b97(\u6216\u8005\u7528\u4e4b\u524d\u7684\u5b50\u67e5\u8be2\u65b9\u5f0f)\uff0c\u7b80\u76f4\u662f\u9ebb\u70e6\u6b7b\u4e86\u3002
      rank over\u51fd\u6570\u7b80\u4ecb<\/a>\u3002
      \u987a\u4fbf\u8bf4\u4e00\u4e0bmysql\u6ca1\u6709full join\uff0c\u5751\u3002<\/p>\n\n\n\n
      1. Manhattan Distance<\/a>
        \u5c31\u662f\u7ec3\u4e60\u4e00\u4e0bround\u51fd\u6570\u548cabs\u51fd\u6570\u3002
        \u53e6\u5916\u4e5f\u6709\u9898\u76ee\u8054\u7cfbsqrt\u548cpower\u51fd\u6570\u7684\uff0c\u5c31\u61d2\u5f97\u5217\u4e86\u3002<\/li>
      2. Median Query<\/a>
        \u5728\u53ef\u4ee5\u4f7f\u7528rank over\u548csubquery\u7684\u60c5\u51b5\u4e0b\u975e\u5e38\u597d\u89e3\u51b3\uff1a<\/li><\/ol>\n\n\n\n
           with t as\n   (\n   select LAT_N, rank() over (order by LAT_N ) as place\n   from station\n   )\n   select round(AVG(LAT_N), 4)\n   from t\n   where abs(place - ((select count(*) from t) + 1)\/2.0) <= 0.6 <\/code><\/pre>\n\n\n\n
        \u5982\u679c\u6ca1\u6709rank over\uff0c\u90a3\u4e48\u5c31\u53ea\u80fd\u73a9join\u7684\u82e6\u903c\u5957\u8def\u4e86\uff1a\u7528join\u7684\u65b9\u5f0f\u6765\u7b97rank\uff0c\u7136\u540e\u518d\u627e\u51fa\u4e2d\u4f4d\u6570\u5bf9\u5e94\u7684\u90a3\u4e00\u884c\u6216\u8005\u4e24\u884c\u53bb\u7b97\u5e73\u5747\u503c\u3002
        \u8fd9\u91cc\u7b97\u4fbf\u8bb0\u5f55\u4e00\u4e0b\uff0cmysql\u7684subquery(\u6216\u8005\u53ebinner query, inner select)\u53ef\u4ee5\u653e\u5728WHERE\u3001FROM\u548cSELECT\u5b50\u53e5\u91cc\u3002
        \u4ee5\u53carank over\u51fd\u6570\u662f\u4e0d\u80fd\u7528\u5728where\u5b50\u53e5\u91cc\u7684\u3002<\/p>\n\n\n\n
        1. Prime Number<\/a>
          \u6ce8\u610f\u6bcf\u4e00\u4e2a\u751f\u6210\u7684\u4e2d\u95f4\u8868\u90fd\u9700\u8981\u53d6\u522b\u540d\uff1aEvery derived table must have its own alias<\/em>\u3002
          \u5728mysql\u8bed\u6cd5\u4e2d\uff0cwhere\u5b50\u53e5\u548cgroup by\u5b50\u53e5\u4e0d\u80fd\u7528\u5217\u7684\u522b\u540d\uff0c\u800corder by\u5b50\u53e5\u53ef\u4ee5\u7528\u5217\u7684\u522b\u540d\u3002
          \u53ef\u4ee5\u6982\u62ec\u4e3a\u7ec3\u4e60\u4e86\u4e00\u4e0bgroup_concat<\/em>\u548cmod<\/em>\u4e24\u4e2a\u51fd\u6570\u7684\u4f7f\u7528\u3002\u540c\u65f6\u52a0\u6df1\u4e86\u5bf9\u751f\u6210\u8fd9\u4e2a1\u52301000\u8303\u56f4\u7684\u6570\u5217\u7684sql\u7684\u7406\u89e3(\u90a3\u5c31\u662f\u5bf9\u8868\u4e2d\u7684\u6bcf\u4e00\u884c\u6267\u884ctmp\u52a01\u7684\u64cd\u4f5c\u800c\u4e0d\u662f\u6211\u539f\u5148\u7406\u89e3\u7684\u8fd9\u4e2ainformation_schmea.tables\u662f\u6ca1\u7528\u7684\uff0c\u662f\u62ff\u6765\u5145\u6570\u7684)\u3002<\/li><\/ol>\n\n\n\n
             set @tmp:=0;\n   with t1 as (\n       select @tmp:=@tmp+1 as n\n       from information_schema.tables, information_schema.tables c\n       limit 1000\n   ) \n   select group_concat(t3.n SEPARATOR '&')\n   from(\n       select n, (select count(1) from t1 as t2 where t2.n <= t1.n and mod(t1.n, t2.n) = 0) as divisor\n       from t1 \n       where (select count(1) from t1 as t2 where t2.n <= t1.n and mod(t1.n, t2.n) = 0) = 2\n       order by t1.n\n   )t3<\/code><\/pre>\n\n\n\n
          10. Multiple Join<\/a>
          \u5e2e\u52a9\u4f60\u7ec3\u4e60\u4e0d\u505c\u7684join\u3002 <\/p>\n\n\n\n
          SELECT Company.company_code, Company.founder, COUNT(DISTINCT Lead_manager.lead_manager_code)\n\t, COUNT(DISTINCT Senior_manager.senior_manager_code), COUNT(DISTINCT Manager.manager_code)\n\t, COUNT(DISTINCT Employee.employee_code)\nFROM Company\n\tLEFT JOIN Lead_manager ON Company.company_code = Lead_manager.company_code\n\tLEFT JOIN Senior_manager ON Lead_manager.lead_manager_code = Senior_manager.lead_manager_code\n\tLEFT JOIN Manager ON Senior_manager.senior_manager_code = Manager.senior_manager_code\n\tLEFT JOIN Employee ON Manager.manager_code = Employee.manager_code\nGROUP BY Company.company_code, Company.founder<\/code><\/pre>\n\n\n\n
          11. Project Planning<\/a>
          \u95ee\u9898\u5927\u610f\u5c31\u662f\u5408\u5e76\u957f\u5ea6\u76f8\u540c\u7684\u8fde\u7eed\u533a\u95f4\u3002
          \u89e3\u6cd5\u4e00\uff1a\u4f7f\u7528\u53d8\u91cf\u6765\u6807\u8bb0\u5c5e\u4e8e\u540c\u4e00\u4e2a\u4efb\u52a1\u7684\u5b50\u4efb\u52a1\uff0c\u540c\u65f6\u8fd8\u7528\u5230\u4e86LAG\u51fd\u6570\u3002 <\/p>\n\n\n\n
          SET @tmp = 0;\n\nSELECT MIN(Start_Date) AS st, MAX(End_Date) AS et\nFROM (\n\tSELECT Start_Date, End_Date\n\t\t, if(Start_Date <> Previous_End_Date, @tmp := @tmp + 1, @tmp) AS groupid\n\tFROM (\n\t\tSELECT Start_Date, End_Date, LAG(End_Date) OVER (ORDER BY Start_Date) AS Previous_End_Date\n\t\tFROM Projects\n\t\tORDER BY Start_Date\n\t) t1\n) t2\nGROUP BY groupid\nORDER BY MAX(End_Date) - MIN(Start_Date), st<\/code><\/pre>\n\n\n\n
          \u89e3\u6cd5\u4e8c\uff1a\u4f7f\u7528subquery\uff0c\u5177\u4f53\u539f\u7406\u67e5\u770b\u8be5\u95ee\u9898\u8ba8\u8bba\u533a\u3002 <\/p>\n\n\n\n
          SELECT Start_Date, MIN(End_Date)\nFROM (\n\tSELECT Start_Date\n\tFROM Projects\n\tWHERE Start_Date NOT IN (\n\t\tSELECT End_Date\n\t\tFROM Projects\n\t)\n) a, (\n\t\tSELECT end_date\n\t\tFROM PROJECTS\n\t\tWHERE end_date NOT IN (\n\t\t\tSELECT start_date\n\t\t\tFROM PROJECTS\n\t\t)\n\t) b\nWHERE start_date < end_date\nGROUP BY start_date\nORDER BY datediff(start_date, MIN(end_date)) DESC, start_date<\/code><\/pre>\n\n\n\n
          \u89e3\u6cd5\u4e09\uff1a\u7531\u4e8e\u5b50\u533a\u95f4\u7684\u957f\u5ea6\u76f8\u540c\uff0c\u5176\u5b9e\u95ee\u9898\u7b49\u4ef7\u4e8e\u5728\u4e00\u4e2a\u6392\u597d\u5e8f\u7684\u5e8f\u5217\u4e2d\uff0c\u627e\u51fa\u6784\u6210\u8fd9\u4e2a\u5e8f\u5217\u7684\u8fde\u7eed\u5b50\u5e8f\u5217\u3002\u53ea\u8981\u7528row_number\u548c\u6bcf\u4e2a\u5143\u7d20\u505a\u5dee\uff0c\u7136\u540e\u5bf9\u8fd9\u4e2a\u5deegroup by\u5c31\u53ef\u4ee5\u533a\u5206\u5c5e\u4e8e\u4e0d\u540c\u8fde\u7eed\u5b50\u5e8f\u5217\u7684\u5143\u7d20\u4e86\u3002 <\/p>\n\n\n\n
          ;WITH CTE_Projects AS ( SELECT Start_Date, End_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) AS RowNumber FROM Projects WITH (NOLOCK) ) SELECT MIN(Start_Date), MAX(End_Date) FROM CTE_Projects WITH (NOLOCK) GROUP BY DATEDIFF(DAY, RowNumber, Start_Date) ORDER BY DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)), MIN(Start_Date) <\/code><\/pre>\n\n\n\n
          \u8fd9\u4e2a\u95ee\u9898\u66f4\u96be\u7684\u4e00\u4e2a\u7248\u672c\uff0c\u7ed9\u51fa\u82e5\u5e72\u4e2a\u533a\u95f4\uff0c\u6c42\u8fd9\u4e9b\u533a\u95f4\u5408\u5e76\u540e\u7684\u7ed3\u679c\u3002\u5728stackoverflow\u4e0a\u641c\u5230\u4e00\u4e2aclickhouse\u7248\u672c\u7684\u7b54\u6848<\/a>\uff0c\u81ea\u5df1\u4e5f\u60f3\u8fc7\uff0c\u6ca1\u60f3\u51fa\u6765\u3002<\/p>\n\n\n\n
          12. Having<\/a>
          \u7ec3\u4e60\u4e00\u4e0bhaving\uff0cwhere\u662f\u5728\u805a\u5408\u524d\u8fdb\u884c\u7b5b\u9009\uff0c\u800chaving\u662f\u5bf9\u805a\u5408\u540e\u7684\u7ed3\u679c\u8fdb\u884c\u7b5b\u9009\u3002 <\/p>\n\n\n\n
          SELECT hackers.hacker_id, hackers.name\nFROM hackers\n\tJOIN submissions ON hackers.hacker_id = submissions.hacker_id\n\tJOIN challenges ON submissions.challenge_id = challenges.challenge_id\n\tJOIN difficulty ON challenges.difficulty_level = difficulty.difficulty_level\nWHERE submissions.score = difficulty.score\nGROUP BY hackers.hacker_id, hackers.name\nHAVING COUNT(1) > 1\nORDER BY COUNT(1) DESC, hackers.hacker_id<\/code><\/pre>\n\n\n\n
          13. Challenges<\/a>
          \u5927\u610f\u662f\u8981\u540c\u65f6\u8ba1\u7b97\u51fa\u67d0\u4e00\u5217\u7684\u6700\u5927\u503c\uff0c\u548c\u6bcf\u4e00\u884c\u7684\u8fd9\u4e00\u5217\u7684\u8fd9\u4e2a\u503c\u7684\u6392\u540d\u3002hackerrank\u8fd9\u9053\u9898\u7ed9\u7684mysql\u7248\u672c\u662f\u4e00\u4e2a\u5f88\u4f4e\u7248\u672c\u7684\uff0c          \u6ca1\u6709with\u8bed\u53e5<\/a>\uff0c\u8fd8\u8001\u662f\u62a5\u5404\u79cd\u8bed\u6cd5\u9519\uff0c\u88ab\u8fd9\u4e2a\u4f4e\u7248\u672cmysql\u8bed\u6cd5\u7ed9\u641e\u6b7b\u4e86\u3002\u6700\u540e\u53cd\u6b63\u662f\u770b\u8bc4\u8bba\u533a\uff0c\u7528\u5b50\u67e5\u8be2\u5199\u7684(\u800c\u4e14\u8fd8\u4e0d\u80fd\u4f7f\u7528\u8868\u522b\u540d\u90a3\u79cd\u5b50\u67e5\u8be2\uff0c\u9700\u8981\u81ea\u5df1\u91cd\u65b0\u628a\u76f8\u5e94\u7684\u8868\u751f\u6210\u4e00\u904d\uff0c\u65e0\u8bed)\uff1a <\/p>\n\n\n\n
          SELECT hacker_id, name, challenge_count\nFROM (\n\tSELECT hackers.hacker_id AS hacker_id, name, COUNT(1) AS challenge_count\n\tFROM hackers\n\t\tJOIN challenges ON hackers.hacker_id = challenges.hacker_id\n\tGROUP BY hacker_id, name\n\tORDER BY challenge_count DESC, hacker_id\n) t\nWHERE (\n\t\tSELECT MAX(cnt)\n\t\tFROM (\n\t\t\tSELECT COUNT(1) AS cnt\n\t\t\tFROM challenges\n\t\t\tGROUP BY hacker_id\n\t\t) tmp\n\t) = challenge_count\n\tOR (\n\t\tSELECT COUNT(1)\n\t\tFROM (\n\t\t\tSELECT COUNT(1) AS cnt\n\t\t\tFROM challenges\n\t\t\tGROUP BY hacker_id\n\t\t) tmp\n\t\tWHERE cnt = challenge_count\n\t) = 1<\/code><\/pre>\n\n\n\n
          14. Wands<\/a>
          \u4e11\u964b\u7684subquery\uff1a <\/p>\n\n\n\n
          SELECT id, age, coins, wpower\nFROM (\n\tSELECT wands.id AS id, wands_property.age AS age, wands.coins_needed AS coins, wands.power AS wpower\n\tFROM wands\n\t\tJOIN wands_property ON wands.code = wands_property.code\n\tWHERE wands_property.is_evil = 0\n) t\nWHERE (\n\tSELECT COUNT(1)\n\tFROM (\n\t\tSELECT wands.id AS id, wands_property.age AS age, wands.coins_needed AS coins, wands.power AS wpower\n\t\tFROM wands\n\t\t\tJOIN wands_property ON wands.code = wands_property.code\n\t\tWHERE wands_property.is_evil = 0\n\t) t1\n\tWHERE t1.age = t.age\n\t\tAND t1.wpower = t.wpower\n\t\tAND t1.coins < t.coins\n) = 0\nORDER BY wpower DESC, age DESC<\/code><\/pre>\n\n\n\n
          \u7528row_number\u5bf9\u591a\u4e2a\u5217\u6392\u5e8f\u65f6\u5c45\u7136\u4f1a\u51fa\u9519\uff0c\u8c8c\u4f3c\u662f\u56e0\u4e3a\u8bc4\u5224\u7cfb\u7edf\u7684mysql\u7248\u672c\u592a\u4f4e\u4e86\u5bfc\u81f4\u7684\u3002<\/p>\n\n\n\n
          15. Interviews<\/a>
          \u9898\u76ee\u6280\u672f\u4e0a\u5176\u5b9e\u6bd4\u8f83\u7b80\u5355\uff0c\u5c31\u662f\u9898\u76ee\u7406\u89e3\u5bb9\u6613 \u51fa\u9519\uff1a<\/p>\n\n\n\n
          View_Stats\u548cSubmission_Stats\u8fd9\u4e24\u4e2a\u8868\u91cc\u9762\u7684total_views\u3001total_unique_views\u3001total_submissions\u548ctotal_accepted_submissions\u90fd\u9700\u8981\u518d\u6c42\u4e00\u6b21\u548c\uff1b<\/p>\n\n\n\n
          \u9700\u8981left join\uff0c\u6709\u4e9b\u9898\u76ee\u53ef\u80fd\u5728View_Stats\u548cSubmission_Stats\u4e2d\u6ca1\u6709\u51fa\u73b0\uff1b<\/p>\n\n\n\n
          16. Days of Learning SQL<\/a>
          \u867d\u7136\u4e0d\u505c\u5730\u5199\u5b50\u8868\u5957\u5b50\u8868\u53ef\u4ee5\u5957\u51fa\u6765\uff0c\u53ef\u662f\u8fd8\u662f\u53d7\u4e0d\u4e86\u8fd9\u79cd\u6298\u78e8\u770b\u4e86\u8ba8\u8bba\u533a\u7684\u7b54\u6848\uff0c\u7136\u540e\u53d1\u73b0\u81ea\u5df1\u8fd8\u662f\u6ca1\u6709\u771f\u6b63\u7406\u89e3mysql\u7684\u8bed\u6cd5\u3002\u3002\u3002<\/p>\n\n\n\n
          SELECT submission_date\n\t, (\n\t\tSELECT COUNT(DISTINCT hacker_id)\n\t\tFROM submissions s2\n\t\tWHERE (\n\t\t\tSELECT COUNT(DISTINCT s3.submission_date)\n\t\t\tFROM submissions s3\n\t\t\tWHERE s3.submission_date <= s1.submission_date\n\t\t\t\tAND s3.hacker_id = s2.hacker_id\n\t\t) = datediff(s1.submission_date, '2016-03-01') + 1\n\t)\n\t, (\n\t\tSELECT hacker_id\n\t\tFROM submissions s2\n\t\tWHERE s2.submission_date = s1.submission_date\n\t\tGROUP BY hacker_id\n\t\tORDER BY COUNT(1) DESC, hacker_id\n\t\tLIMIT 1\n\t) AS mvp\n\t, (\n\t\tSELECT name\n\t\tFROM hackers\n\t\tWHERE hacker_id = mvp\n\t)\nFROM (\n\tSELECT submission_date\n\tFROM submissions\n\tGROUP BY submission_date\n\tORDER BY submission_date\n) s1<\/code><\/pre>\n\n\n\n

          \u4e3b\u8981\u5b66\u5230\u7684\u5c31\u662f\u51e0\u70b9\u5427\uff1a<\/p>\n\n\n\n
          \u7528\u5b50\u67e5\u8be2\u66ff\u4ee3join(\u4e0d\u505c\u7684join\u771f\u7684\u5f88\u75db\u82e6)\uff1b<\/p>\n\n\n\n
          \u524d\u9762\u4e00\u4e2a\u7b97\u51fa\u6765\u7684\u5217\u7684\u503c\u53ef\u4ee5\u7528\u6765\u8ba1\u7b97\u540e\u9762\u7684\u5217\uff1b<\/p>\n\n\n\n
          from\u7684\u5b50\u67e5\u8be2\u67e5\u51fa\u7684\u8868\u53ef\u4ee5\u7528\u5230select\u8bed\u53e5\u91cc\uff1b<\/p>\n\n\n\n
          \u4e0d\u540c\u7684\u5b50\u67e5\u8be2\u91cc\u53ef\u4ee5\u521b\u5efa\u65b0\u7684\u76f8\u540c\u7684\u8868\uff1b<\/p>\n\n\n\n
          \u90fd\u505a\u5230\u6700\u540e\u4e00\u9898\u4e86\uff0c\u53d1\u73b0\u81ea\u5df1\u8fd8\u662fsql\u5165\u95e8\u6c34\u5e73\u3002\u3002\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
          \u67e5\u8be2\u4ee5aeiou\u5b57\u6bcd\u5f00\u5934\u7684\u57ce\u5e02 \u4e3b\u8981\u662f\u8bb0\u5f97\u4f7f\u7528REGEXP\u51fd\u6570\uff0c\u548c\u6b63\u5219\u8868\u8fbe\u5f0f\u8981\u7528^\u8868\u793a\u5f00\u5934\u3002\u6216\u8005\uff1a \u7528left\u51fd\u6570\u63d0\u53d6\u5b50\u5b57\u7b26\u4e32\u3002 \u57fa\u672c\u7684join\u64cd\u4f5c\u4f7f\u7528join\u5199\u4e24\u4e2a\u8bed\u53e5\uff0c\u5206\u522b\u628a\u957f\u5ea6\u6700\u957f\u548c\u6700\u77ed\u7684\u7ed9\u627e\u51fa...<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[32,31],"class_list":["post-341","post","type-post","status-publish","format-standard","hentry","category-10","tag-hackerrank","tag-sql"],"_links":{"self":[{"href":"https:\/\/blog.cauchyschwarz.com\/index.php?rest_route=\/wp\/v2\/posts\/341","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.cauchyschwarz.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.cauchyschwarz.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.cauchyschwarz.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.cauchyschwarz.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=341"}],"version-history":[{"count":3,"href":"https:\/\/blog.cauchyschwarz.com\/index.php?rest_route=\/wp\/v2\/posts\/341\/revisions"}],"predecessor-version":[{"id":678,"href":"https:\/\/blog.cauchyschwarz.com\/index.php?rest_route=\/wp\/v2\/posts\/341\/revisions\/678"}],"wp:attachment":[{"href":"https:\/\/blog.cauchyschwarz.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.cauchyschwarz.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.cauchyschwarz.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}