\u5047\u8bbe\u624b\u91cc\u6709\u4e09\u4efd\u5b66\u751f\u540d\u5355，\u8981\u653e\u8fdb\u4e00\u5f20\u62a5\u8868\u91cc：\u4e9a\u6d32\u4e00\u5217，\u6b27\u6d32\u4e00\u5217，\u7f8e\u6d32\u4e00\u5217\u3002\u6bcf\u5217\u6309\u59d3\u540d\u6392\u5e8f，\u54ea\u4efd\u540d\u5355\u5148\u6392\u5b8c，\u540e\u9762\u7684\u683c\u5b50\u5c31\u7559\u7a7a\u3002<\/p>\n
\u8fd9\u542c\u8d77\u6765\u4e0d\u590d\u6742，\u590d\u5236\u5230\u8868\u683c\u91cc\u5c31\u80fd\u6446\u51fa\u6765\u3002\u4f46\u5728\u6570\u636e\u5e93\u91cc，\u5b66\u751f\u539f\u672c\u662f\u4e00\u884c\u4e00\u884c\u5b58\u7684\u3002\u6211\u4eec\u5148\u4e0d\u8981\u627e\u201c\u884c\u8f6c\u5217\u6a21\u677f\u201d，\u770b\u6e05\u695a\u8981\u600e\u6837\u6446\u3002<\/p>\n
\u4e0b\u9762\u8fd9\u9053\u7ec3\u4e60\u6765\u81ea\u65e7SQL\u7b14\u8bb0\u4e2d\u201c\u6309\u5730\u533a\u6574\u7406\u5b66\u751f\u62a5\u8868\u201d\u7684\u9009\u9898\u3002\u7b14\u8bb0\u63d0\u5230\u8fc7LeetCode 618，\u4e0d\u8fc7\u5b98\u65b9\u9898\u9875\u5f53\u524d\u9700\u8981\u8ba2\u9605\u624d\u80fd\u8bfb\u5b8c\u6574\u9898\u9762，\u6240\u4ee5\u8fd9\u91cc\u81ea\u884c\u7ea6\u5b9a\u8868\u7ed3\u6784\u548c\u89c4\u5219，\u4e0d\u628a\u5b83\u5f53\u6210\u5b98\u65b9\u539f\u9898\u590d\u8ff0\u3002<\/p>\n
\u6211\u4eec\u4f7f\u7528\u8fd96\u6761\u8bb0\u5f55\u3002student_id\u552f\u4e00；\u59d3\u540d\u548c\u5730\u533a\u90fd\u4e0d\u4e3a\u7a7a，\u59d3\u540d\u53ea\u7528\u5927\u5199\u82f1\u6587\u5b57\u6bcd\u3002\u5730\u533a\u9650\u5b9a\u4e3aAsia\u3001Europe\u3001America\u3002\u540c\u540d\u7b97\u4e0d\u540c\u5b66\u751f，\u540c\u540d\u65f6\u6309\u7f16\u53f7\u4ece\u5c0f\u5230\u5927\u6392\u3002<\/p>\n
\u5728\u7a7a\u7684MySQL 8\u5b66\u4e60\u5e93\u4e2d\u6267\u884c；\u6709\u540c\u540d\u8868\u5c31\u6362\u5e93，\u4e0d\u8981\u8986\u76d6\u4e1a\u52a1\u8868\u3002<\/p>\n
CREATE TABLE Student (
\n student_id INT PRIMARY KEY,
\n name VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
\n continent ENUM('Asia','Europe','America') NOT NULL
\n);
\nINSERT INTO Student VALUES
\n (30, 'BO', 'Asia'),
\n (11, 'ADA', 'Europe'),
\n (9, 'AMY', 'Asia'),
\n (24, 'CARL', 'America'),
\n (3, 'AMY', 'Asia'),
\n (8, 'BEN', 'Europe');<\/p>\n
\u4e3a\u4ec0\u4e48\u7279\u610f\u5199\u4e24\u4e2aAMY？\u56e0\u4e3a\u6211\u4eec\u6392\u7684\u662f\u5b66\u751f，\u4e0d\u662f\u4e0d\u540c\u59d3\u540d\u7684\u96c6\u5408\u3002\u4e24\u540d\u540c\u540d\u5b66\u751f\u90fd\u5f97\u5360\u4e00\u4e2a\u4f4d\u7f6e\u3002<\/p>\n
\u5148\u628a\u5730\u533a\u62c6\u5f00，\u5404\u81ea\u6392\u5e8f：<\/p>\n
Asia Europe America
\nAMY(id=3) ADA(id=11) CARL(id=24)
\nAMY(id=9) BEN(id=8)
\nBO(id=30)<\/p>\n
\u63a5\u4e0b\u6765，\u62ff\u4e09\u4efd\u540d\u5355\u91cc\u7684\u7b2c\u4e00\u4eba\u653e\u4e00\u884c，\u7b2c\u4e8c\u4eba\u653e\u4e0b\u4e00\u884c\u3002\u6700\u540e\u5e94\u8be5\u662f：<\/p>\n
\u4f4d\u7f6e Asia Europe America
\n 1 AMY ADA CARL
\n 2 AMY BEN NULL
\n 3 BO NULL NULL<\/p>\n
\u8fd9\u91cc\u7528SQL\u7684NULL\u8868\u793a\u6ca1\u6709\u5b66\u751f，\u5e76\u4e0d\u662f\u59d3\u540d\u53eb\u201cNULL\u201d，\u4e5f\u4e0d\u662f\u628a\u7a7a\u683c\u5f53\u6210\u59d3\u540d\u5b58\u8fdb\u53bb\u3002\u6700\u7ec8\u67e5\u8be2\u53ea\u8fd4\u56de\u4e09\u4e2a\u5730\u533a\u5217，\u201c\u4f4d\u7f6e\u201d\u6682\u65f6\u7528\u6765\u5e2e\u6211\u4eec\u770b\u5bf9\u9f50\u8fc7\u7a0b\u3002<\/p>\n
\u6ce8\u610f，\u540c\u4e00\u884c\u7684\u4e09\u4e2a\u4eba\u6ca1\u6709\u4e1a\u52a1\u4e0a\u7684\u8054\u7cfb\u3002\u7b2c\u4e00\u884c\u5e76\u4e0d\u662f\u540c\u73ed\u3001\u540c\u4e00\u5929\u5165\u5b66，\u6216\u8005\u7f16\u53f7\u76f8\u540c，\u53ea\u662f\u5404\u81ea\u5728\u672c\u5730\u533a\u6392\u5230\u7b2c\u4e00\u4f4d\u3002<\/p>\n
\u5982\u679c\u5148\u67e5\u4e9a\u6d32\u540d\u5355，\u518d\u548c\u6b27\u6d32\u540d\u5355\u3001\u7f8e\u6d32\u540d\u5355\u62fc\u5728\u4e00\u8d77，\u4f1a\u4e0d\u4f1a\u5c31\u5f97\u5230\u8fd9\u5f20\u8868？<\/p>\n
\u60f3\u4e00\u4e0b\u7b2c\u4e00\u4f4d\u4e9a\u6d32\u5b66\u751fAMY(id=3)\u3002\u6b27\u6d32\u6709ADA\u3001BEN\u4e24\u4f4d，\u6ca1\u6709\u989d\u5916\u7684\u5339\u914d\u6761\u4ef6\u65f6，\u8fd9\u4e2aAMY\u4f1a\u548c\u4e24\u4eba\u5206\u522b\u62fc\u4e00\u6b21\u3002<\/p>\n
AMY(id=3) — ADA
\n — BEN<\/p>\n
\u53e6\u5916\u4e24\u4e2a\u4e9a\u6d32\u5b66\u751f\u4e5f\u5404\u62fc\u4e24\u6b21\u3002\u4e09\u5217\u8fd9\u6837\u505a\u7b1b\u5361\u5c14\u79ef，\u4e00\u5171\u662f3\u00d72\u00d71=6\u884c，\u800c\u6211\u4eec\u8981\u7684\u662f3\u884c\u3002\u4e00\u65e6\u67d0\u4e2a\u5730\u533a\u65e0\u4eba，\u4ea4\u53c9\u8fde\u63a5\u751a\u81f3\u53ef\u80fd\u628a\u6574\u5f20\u62a5\u8868\u53d8\u6210\u7a7a\u7ed3\u679c\u3002<\/p>\n
\u73b0\u5728\u7f3a\u7684\u4e0d\u662f\u4e00\u4e2a\u66f4\u5de7\u7684\u8fde\u63a5\u6761\u4ef6，\u800c\u662f：\u8c01\u5e94\u8be5\u548c\u8c01\u653e\u5728\u540c\u4e00\u884c？<\/p>\n
\u624b\u5de5\u6392\u8868\u65f6，\u6211\u4eec\u5176\u5b9e\u5df2\u7ecf\u6709\u4e86\u7b54\u6848：\u5404\u5730\u533a\u7684\u7b2c\u4e00\u4eba\u653e\u4e00\u8d77，\u7b2c\u4e8c\u4eba\u653e\u4e00\u8d77\u3002\u90a3\u5c31\u5728\u67e5\u8be2\u91cc\u628a\u8fd9\u4e2a\u201c\u7b2c\u51e0\u4eba\u201d\u7b97\u51fa\u6765\u3002<\/p>\n
ROW_NUMBER()\u80fd\u4f9d\u6b21\u7ed9\u8bb0\u5f55\u7f16\u53f7\u3002\u8fd9\u91cc\u4e0d\u80fd\u7ed9\u5168\u8868\u4ece1\u6392\u52306，\u800c\u8981\u5728\u6bcf\u4e2a\u5730\u533a\u91cc\u5206\u522b\u4ece1\u5f00\u59cb\u3002<\/p>\n
SELECT student_id, name, continent,
\n ROW_NUMBER() OVER (
\n PARTITION BY continent
\n ORDER BY name, student_id
\n ) AS pos
\nFROM Student
\nORDER BY continent, pos;<\/p>\n
PARTITION BY continent\u628a\u7f16\u53f7\u8303\u56f4\u5206\u5f00，ORDER BY name, student_id\u51b3\u5b9a\u6bcf\u4e2a\u8303\u56f4\u91cc\u8c01\u5728\u524d\u9762\u3002\u8868\u4e2d\u63d2\u5165\u7684\u5148\u540e\u987a\u5e8f\u4e0d\u5f71\u54cd\u8fd9\u4e2a\u7ea6\u5b9a\u3002<\/p>\n
\u8fd0\u884c\u540e，\u6309\u6211\u4eec\u5efa\u8868\u65f6ENUM\u7684\u5730\u533a\u987a\u5e8f\u663e\u793a：<\/p>\n
id name continent pos
\n 3 AMY Asia 1
\n 9 AMY Asia 2
\n30 BO Asia 3
\n11 ADA Europe 1
\n 8 BEN Europe 2
\n24 CARL America 1<\/p>\n
\u4e24\u4e2aAMY\u7684\u4f4d\u7f6e\u4e0d\u540c\u3002\u8fd9\u6b63\u662f\u6211\u4eec\u9700\u8981\u7684\u201c\u7b2c\u4e00\u540d\u5b66\u751f\u3001\u7b2c\u4e8c\u540d\u5b66\u751f\u201d，\u4e0d\u662f\u8003\u8bd5\u540d\u6b21\u3002<\/p>\n
\u5982\u679c\u6539\u6210RANK() OVER (PARTITION BY continent ORDER BY name)，\u4e24\u4e2aAMY\u90fd\u4f1a\u6392\u7b2c1，BO\u6392\u7b2c3\u3002\u540e\u9762\u518d\u6309\u4f4d\u7f6e\u5206\u7ec4，\u4e24\u4e2aAMY\u5c31\u6324\u5230\u4e86\u540c\u4e00\u4e2a\u683c\u5b50\u91cc\u3002DENSE_RANK\u867d\u7136\u4e0d\u4f1a\u8df3\u53f7，\u4e5f\u8fd8\u662f\u8ba9\u540c\u540d\u5b66\u751f\u5171\u7528\u4f4d\u7f6e，\u95ee\u9898\u5e76\u6ca1\u6709\u89e3\u51b3\u3002<\/p>\n
\u5982\u679c\u7ed9RANK\u7684\u6392\u5e8f\u4e5f\u52a0\u4e0a\u552f\u4e00\u7f16\u53f7，\u540c\u540d\u5b66\u751f\u4e0d\u518d\u5e76\u5217，\u672c\u4f8b\u53ef\u4ee5\u6392\u5bf9\u3002\u4f46\u8fd9\u91cc\u65e2\u7136\u8981\u7684\u662f\u4e00\u4eba\u4e00\u4e2a\u4f4d\u7f6e，\u76f4\u63a5\u4f7f\u7528ROW_NUMBER\u66f4\u8d34\u8fd1\u9700\u6c42\u3002<\/p>\n
\u628a\u521a\u624d\u7684\u67e5\u8be2\u547d\u540d\u4e3aranked\u3002\u73b0\u5728\u53ea\u770bpos=1\u7684\u4e09\u6761\u8bb0\u5f55，\u5148\u4e0d\u6025\u7740\u5408\u5e76：<\/p>\n
\u539f\u6765：
\nAMY Asia 1
\nADA Europe 1
\nCARL America 1<\/p>\n
\u53ea\u586b\u81ea\u5df1\u7684\u5730\u533a\u5217：
\nAsia Europe America
\nAMY NULL NULL
\nNULL ADA NULL
\nNULL NULL CARL<\/p>\n
\u201c\u53ea\u586b\u81ea\u5df1\u7684\u5217\u201d\u53ef\u4ee5\u7528CASE\u5199：\u5f53\u524d\u8bb0\u5f55\u662fAsia，\u5c31\u628a\u59d3\u540d\u653e\u8fdbAsia\u5217，\u5426\u5219\u7ed9NULL\u3002\u53e6\u4e24\u5217\u540c\u7406\u3002<\/p>\n
\u8fd9\u65f6\u8fd8\u53ea\u662f\u4e09\u884c\u7a00\u758f\u7684\u683c\u5b50\u3002\u8981\u5408\u6210\u4e00\u884c，\u53ef\u4ee5\u6309\u4f4d\u7f6e\u5206\u7ec4，\u7136\u540e\u6bcf\u5217\u53d6\u51fa\u90a3\u4e2a\u975e\u7a7a\u59d3\u540d\u3002<\/p>\n
WITH ranked AS (
\n SELECT student_id, name, continent,
\n ROW_NUMBER() OVER (
\n PARTITION BY continent ORDER BY name, student_id
\n ) AS pos
\n FROM Student
\n)
\nSELECT pos,
\n CASE WHEN continent = 'Asia' THEN name END AS Asia,
\n CASE WHEN continent = 'Europe' THEN name END AS Europe,
\n CASE WHEN continent = 'America' THEN name END AS America
\nFROM ranked
\nORDER BY pos, continent;<\/p>\n
CASE\u6ca1\u6709\u5199ELSE，\u4e0d\u5339\u914d\u65f6\u8fd4\u56deNULL\u3002\u540c\u4e00\u4e2a\u4f4d\u7f6e\u91cc，\u6bcf\u4e2a\u5730\u533a\u6700\u591a\u4e00\u4f4d\u5b66\u751f，\u6240\u4ee5\u6bcf\u5217\u6700\u591a\u53ea\u6709\u4e00\u4e2a\u975e\u7a7a\u503c\u3002<\/p>\n
MAX\u4f1a\u5ffd\u7565NULL：AMY\u3001NULL\u3001NULL\u53d6\u51faAMY；\u5168\u90e8\u662fNULL，\u7ed3\u679c\u8fd8\u662fNULL\u3002\u8fd9\u91cc\u7528MAX\u5e76\u4e0d\u662f\u60f3\u627e\u59d3\u540d\u6700\u5927\u7684\u5b66\u751f，\u800c\u662f\u628a\u521a\u624d\u6563\u5f00\u7684\u4e09\u4e2a\u683c\u5b50\u5408\u5e76\u8d77\u6765\u3002<\/p>\n
\u8fd9\u4e5f\u8bf4\u660e\u4e3a\u4ec0\u4e48\u524d\u9762\u7684\u7f16\u53f7\u4e0d\u80fd\u9519：\u5982\u679c\u540c\u4e00\u5730\u533a\u6709\u4e24\u4eba\u5171\u7528\u4e00\u4e2a\u4f4d\u7f6e，MAX\u53ea\u4f1a\u53d6\u5176\u4e2d\u4e00\u4e2a\u503c，\u4e0d\u4f1a\u66ff\u6211\u4eec\u53d1\u73b0\u4eba\u6570\u4e22\u4e86\u3002\u540c\u540d\u65f6，\u62a5\u8868\u751a\u81f3\u770b\u4e0d\u51fa\u5c11\u4e86\u4e00\u4eba\u3002<\/p>\n
WITH ranked AS (
\n SELECT name, continent,
\n ROW_NUMBER() OVER (
\n PARTITION BY continent ORDER BY name, student_id
\n ) AS pos
\n FROM Student
\n)
\nSELECT MAX(CASE WHEN continent = 'Asia' THEN name END) AS Asia,
\n MAX(CASE WHEN continent = 'Europe' THEN name END) AS Europe,
\n MAX(CASE WHEN continent = 'America' THEN name END) AS America
\nFROM ranked
\nGROUP BY pos
\nORDER BY pos;<\/p>\n
\u7ed3\u679c\u5c31\u662f\u524d\u9762\u624b\u5de5\u6392\u7684\u4e09\u884c\u3002\u7b2c\u4e8c\u884c\u7684America\u3001\u7b2c\u4e09\u884c\u7684Europe\u548cAmerica\u90fd\u8865NULL\u3002<\/p>\n
\u8fd9\u6b21GROUP BY\u786e\u5b9e\u628a\u591a\u6761\u5b66\u751f\u8bb0\u5f55\u5408\u5e76\u4e86，\u4f46\u5408\u5e76\u7684\u662f\u201c\u6392\u5728\u540c\u4e00\u4e2a\u4f4d\u7f6e\u201d\u7684\u8bb0\u5f55，\u4e0d\u662f\u628a\u4e00\u4e2a\u5730\u533a\u6240\u6709\u5b66\u751f\u538b\u6210\u4e00\u884c\u3002\u5206\u7ec4\u7684\u5bf9\u8c61\u4e00\u53d8，\u62a5\u8868\u542b\u4e49\u5c31\u4e0d\u540c\u3002<\/p>\n
\u8fd8\u6709\u4e24\u4e2a\u5bb9\u6613\u5ffd\u7565\u7684\u60c5\u51b5：<\/p>\n
\u8f93\u51fa\u6709\u591a\u5c11\u884c？\u662f\u4e09\u4e2a\u5730\u533a\u4eba\u6570\u7684\u6700\u5927\u503c\u3002\u56e0\u4e3a\u6700\u957f\u7684\u540d\u5355\u6392\u5230\u54ea\u91cc，\u90a3\u91cc\u81f3\u5c11\u8fd8\u6709\u4e00\u4e2a\u683c\u5b50\u975e\u7a7a\u3002\u8fd9\u6761\u89c4\u5f8b\u4e5f\u53ef\u4ee5\u62ff\u6765\u68c0\u67e5\u6709\u6ca1\u6709\u6f0f\u884c，\u4f46\u4ec5\u68c0\u67e5\u884c\u6570\u8fd8\u4e0d\u591f，\u59d3\u540d\u548c\u987a\u5e8f\u4ecd\u53ef\u80fd\u4e0d\u5bf9\u3002<\/p>\n
\u8fd9\u91cc\u7684\u4e09\u4e2a\u5730\u533a\u662f\u56fa\u5b9a\u5217\u3002\u589e\u52a0Africa\u5e76\u4e0d\u53ea\u662f\u5f80\u8868\u91cc\u52a0\u6570\u636e，\u8fd8\u9700\u8981\u7ed9\u67e5\u8be2\u589e\u52a0\u5bf9\u5e94\u7684CASE\u548c\u8f93\u51fa\u5217\u3002\u5982\u679c\u5217\u7684\u79cd\u7c7b\u7531\u7528\u6237\u4e34\u65f6\u51b3\u5b9a，\u5c31\u5df2\u7ecf\u662f\u52a8\u6001\u62a5\u8868\u7684\u53e6\u4e00\u4e2a\u95ee\u9898\u4e86\u3002\u4e2d\u6587\u59d3\u540d\u3001\u5927\u5c0f\u5199\u6216\u91cd\u97f3\u7684\u6392\u5217\u89c4\u5219，\u4e5f\u8981\u91cd\u65b0\u7ea6\u5b9a\u6392\u5e8f\u89c4\u5219，\u4e0d\u80fd\u62ff\u672c\u4f8b\u7684ASCII\u987a\u5e8f\u4ee3\u66ff\u3002<\/p>\n
\u53c2\u8003\u7a0b\u5e8f\u4e0d\u7528\u7a97\u53e3\u51fd\u6570，\u4e5f\u4e0d\u518d\u5199\u4e00\u6b21\u6761\u4ef6\u805a\u5408\u3002\u5b83\u5148\u5728Python\u91cc\u51c6\u5907\u4e09\u4e2a\u5217\u8868，\u628a\u5b66\u751f\u6309\u5730\u533a\u653e\u8fdb\u53bb，\u5404\u81ea\u6309\u59d3\u540d\u548c\u7f16\u53f7\u6392\u5e8f，\u7136\u540e\u50cf\u6587\u7ae0\u5f00\u5934\u90a3\u6837\u9010\u4e2a\u4f4d\u7f6e\u53d6\u4eba，\u77ed\u540d\u5355\u53d6\u4e0d\u5230\u5c31\u8865NULL\u3002<\/p>\n
\u518d\u628a\u76f8\u540c\u7684\u6570\u636e\u4ea4\u7ed9\u72ec\u7acbMySQL\u5b9e\u4f8b，\u6267\u884c\u4ece\u672c\u6587\u63d0\u53d6\u7684\u67e5\u8be2，\u9010\u884c\u6bd4\u8f83\u4e09\u5217\u59d3\u540d\u53ca\u987a\u5e8f\u3002\u8fd9\u6837\u6bd4\u8f83\u7684\u662f\u540c\u4e00\u4efd\u8f93\u5165\u7ecf\u8fc7\u4e24\u79cd\u4e0d\u540c\u7ec4\u7ec7\u65b9\u5f0f\u540e，\u80fd\u4e0d\u80fd\u5f97\u5230\u540c\u4e00\u5f20\u62a5\u8868\u3002<\/p>\n
\u4e13\u95e8\u52a0\u5165\u4e86\u540c\u540d\u5b66\u751f\u3001\u53ea\u6709\u4e00\u4e2a\u5730\u533a\u3001\u7f3a\u6574\u4e2a\u5730\u533a\u3001\u7a7a\u8868\u4ee5\u53ca\u6253\u4e71\u63d2\u5165\u987a\u5e8f\u7684\u6570\u636e\u3002\u8fd8\u6267\u884c\u4e86\u51e0\u79cd\u80fd\u8fd0\u884c\u4f46\u903b\u8f91\u9519\u8bef\u7684SQL：\u6309\u59d3\u540d\u5e76\u5217\u6392\u540d\u3001\u5fd8\u8bb0\u5206\u5730\u533a\u7f16\u53f7\u3001\u6309\u5730\u533a\u800c\u4e0d\u662f\u6309\u4f4d\u7f6e\u5206\u7ec4\u3001\u628aNULL\u6539\u6210\u7a7a\u5b57\u7b26\u4e32\u3002\u6bcf\u79cd\u90fd\u8981\u627e\u5230\u5177\u4f53\u53cd\u4f8b，\u4e0d\u80fd\u53ea\u770b\u201c\u7a0b\u5e8f\u8dd1\u5b8c\u4e86\u201d\u3002<\/p>\n
\u9644\u5f55\u7684\u9a8c\u8bc1\u7a0b\u5e8f\u4f7f\u7528\u9694\u79bb\u7684MySQL 8\u6570\u636e\u76ee\u5f55，\u4ec5\u901a\u8fc7\u672c\u673aUnix socket\u8fde\u63a5，\u7ed3\u675f\u540e\u5173\u95ed\u81ea\u5df1\u542f\u52a8\u7684\u8fdb\u7a0b；\u4e0d\u8fde\u63a5\u5df2\u6709\u6570\u636e\u5e93\u3002\u9700\u8981Python 3\u548cMySQL 8\u7684\u670d\u52a1\u7aef\u3001\u5ba2\u6237\u7aef\u53ca\u8fd0\u884c\u5e93\u3002\u9ed8\u8ba4\u8def\u5f84\u662f\u672c\u6587\u7684WSL\u5b9e\u9a8c\u73af\u5883，\u53ef\u901a\u8fc7BLOG_MYSQL_ROOT\u6307\u5b9a\u89e3\u5305\u76ee\u5f55；\u4e0d\u540c\u5b89\u88c5\u5e03\u5c40\u8fd8\u9700\u8c03\u6574\u8fd0\u884c\u5e93\u8def\u5f84\u3002<\/p>\n
\u4e0b\u9762JSON\u4fdd\u5b58\u4e3aarticle-steps.json，Python\u4fdd\u5b58\u4e3a\u540c\u76ee\u5f55\u7684verify_roster.py，\u8fd0\u884cpython3 verify_roster.py\u3002JSON\u4fdd\u7559\u672c\u6587\u5404\u9636\u6bb5SQL\u53ca\u793a\u4f8b\u671f\u671b\u503c，Python\u5148\u6838\u5bf9\u793a\u4f8b，\u518d\u6279\u91cf\u5bf9\u7167\u3002<\/p>\n
[
\n {
\n "id": "setup",
\n "sql": "CREATE TABLE Student (\\\\n student_id INT PRIMARY KEY,\\\\n name VARCHAR(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,\\\\n continent ENUM('Asia','Europe','America') NOT NULL\\\\n);\\\\nINSERT INTO Student VALUES\\\\n (30, 'BO', 'Asia'),\\\\n (11, 'ADA', 'Europe'),\\\\n (9, 'AMY', 'Asia'),\\\\n (24, 'CARL', 'America'),\\\\n (3, 'AMY', 'Asia'),\\\\n (8, 'BEN', 'Europe');",
\n "expected": []
\n },
\n {
\n "id": "ranked",
\n "sql": "SELECT student_id, name, continent,\\\\n ROW_NUMBER() OVER (\\\\n PARTITION BY continent\\\\n ORDER BY name, student_id\\\\n ) AS pos\\\\nFROM Student\\\\nORDER BY continent, pos;",
\n "expected": [
\n "3\\\\tAMY\\\\tAsia\\\\t1",
\n "9\\\\tAMY\\\\tAsia\\\\t2",
\n "30\\\\tBO\\\\tAsia\\\\t3",
\n "11\\\\tADA\\\\tEurope\\\\t1",
\n "8\\\\tBEN\\\\tEurope\\\\t2",
\n "24\\\\tCARL\\\\tAmerica\\\\t1"
\n ]
\n },
\n {
\n "id": "sparse",
\n "sql": "WITH ranked AS (\\\\n SELECT student_id, name, continent,\\\\n ROW_NUMBER() OVER (\\\\n PARTITION BY continent ORDER BY name, student_id\\\\n ) AS pos\\\\n FROM Student\\\\n)\\\\nSELECT pos,\\\\n CASE WHEN continent = 'Asia' THEN name END AS Asia,\\\\n CASE WHEN continent = 'Europe' THEN name END AS Europe,\\\\n CASE WHEN continent = 'America' THEN name END AS America\\\\nFROM ranked\\\\nORDER BY pos, continent;",
\n "expected": [
\n "1\\\\tAMY\\\\tNULL\\\\tNULL",
\n "1\\\\tNULL\\\\tADA\\\\tNULL",
\n "1\\\\tNULL\\\\tNULL\\\\tCARL",
\n "2\\\\tAMY\\\\tNULL\\\\tNULL",
\n "2\\\\tNULL\\\\tBEN\\\\tNULL",
\n "3\\\\tBO\\\\tNULL\\\\tNULL"
\n ]
\n },
\n {
\n "id": "final",
\n "sql": "WITH ranked AS (\\\\n SELECT name, continent,\\\\n ROW_NUMBER() OVER (\\\\n PARTITION BY continent ORDER BY name, student_id\\\\n ) AS pos\\\\n FROM Student\\\\n)\\\\nSELECT MAX(CASE WHEN continent = 'Asia' THEN name END) AS Asia,\\\\n MAX(CASE WHEN continent = 'Europe' THEN name END) AS Europe,\\\\n MAX(CASE WHEN continent = 'America' THEN name END) AS America\\\\nFROM ranked\\\\nGROUP BY pos\\\\nORDER BY pos;",
\n "expected": [
\n "AMY\\\\tADA\\\\tCARL",
\n "AMY\\\\tBEN\\\\tNULL",
\n "BO\\\\tNULL\\\\tNULL"
\n ]
\n }
\n]<\/p>\n
import getpass
\nimport itertools
\nimport json
\nimport os
\nimport random
\nimport subprocess
\nimport tempfile
\nimport time
\nfrom datetime import date, timedelta
\nfrom pathlib import Path<\/p>\n
base = Path(__file__).resolve().parent
\nsteps = json.loads((base \/ 'article-steps.json').read_text())
\npackage = Path(os.environ.get('BLOG_MYSQL_ROOT',
\n '\/mnt\/d\/blog-ops\/experiments\/2026-10-03-candidates\/mysql-packages\/root'))
\nenv = dict(os.environ, LD_LIBRARY_PATH=str(package \/ 'usr\/lib\/x86_64-linux-gnu'))
\nrun = Path(tempfile.mkdtemp(prefix='roster-', dir=base))
\n(run \/ 'data').mkdir()
\nsock = Path('\/dev\/shm') \/ (run.name + '.sock')
\nargs = [str(package \/ 'usr\/sbin\/mysqld'), '–no-defaults',
\n '–basedir=' + str(package \/ 'usr'), '–datadir=' + str(run \/ 'data'),
\n '–lc-messages-dir=' + str(package \/ 'usr\/share\/mysql'),
\n '–user=' + getpass.getuser(), '–secure-file-priv=NULL',
\n '–innodb-buffer-pool-size=67108864', '–innodb-redo-log-capacity=16777216',
\n '–log-error=' + str(run \/ 'server.log')]
\ninit = subprocess.run(args + ['–initialize-insecure'], env=env,
\n capture_output=True, text=True, timeout=90)
\nif init.returncode:
\n raise RuntimeError(init.stderr)
\nclient = [str(package \/ 'usr\/bin\/mysql'), '–no-defaults', '–protocol=SOCKET',
\n '–socket=' + str(sock), '-u', 'root', '–batch', '–skip-column-names',
\n '–connect-timeout=2']
\nserver = subprocess.Popen(args + ['–skip-networking', '–mysqlx=0',
\n '–socket=' + str(sock), '–pid-file=' + str(run \/ 'mysql.pid'),
\n '–tmpdir=' + str(run)], env=env, stdout=subprocess.DEVNULL,
\n stderr=subprocess.DEVNULL)
\nrecord = {'passed': False, 'run': str(run)}<\/p>\n
def sql(text):
\n result = subprocess.run(client, input=text, env=env, capture_output=True,
\n text=True, timeout=120)
\n if result.returncode or result.stderr.strip():
\n raise RuntimeError(result.stdout + result.stderr)
\n return result.stdout.splitlines()<\/p>\n
def oracle(rows):
\n lists = [sorted((n, i) for i, n, c in rows if c == region)
\n for region in ['Asia', 'Europe', 'America']]
\n return ['\\\\t'.join(g[p][0] if p < len(g) else 'NULL' for g in lists)
\n for p in range(max(map(len, lists), default=0))]<\/p>\n
def insert(rows):
\n if not rows:
\n return ''
\n return 'INSERT INTO Student VALUES ' + ','.join(
\n f"({i},'{n}','{c}')" for i, n, c in rows) + ';'<\/p>\n
def chunks(lines, prefix):
\n groups = []
\n for line in lines:
\n if line.startswith(prefix):
\n groups.append([])
\n else:
\n assert groups, line
\n groups[-1].append(line)
\n return groups<\/p>\n
try:
\n for _ in range(200):
\n if server.poll() is not None:
\n raise RuntimeError((run \/ 'server.log').read_text())
\n if sock.exists():
\n try:
\n sql('SELECT 1;')
\n break
\n except RuntimeError:
\n pass
\n time.sleep(0.1)
\n else:
\n raise TimeoutError('MySQL startup timed out')
\n record['version'] = sql('SELECT VERSION();')[0]
\n commands = ['CREATE DATABASE blog_roster; USE blog_roster;']
\n for i, step in enumerate(steps):
\n commands.extend([f"SELECT '__step_{i}__';", step['sql']])
\n outputs = chunks(sql('\\\\n'.join(commands)), '__step_')
\n assert outputs == [s['expected'] for s in steps], outputs
\n record['articleSteps'] = [dict(s, actual=a) for s, a in zip(steps, outputs)]
\n final = next(s['sql'] for s in steps if s['id'] == 'final')
\n main = [(30, 'BO', 'Asia'), (11, 'ADA', 'Europe'), (9, 'AMY', 'Asia'),
\n (24, 'CARL', 'America'), (3, 'AMY', 'Asia'), (8, 'BEN', 'Europe')]
\n cases = [main, [], [(1, 'Z', 'Europe')]]
\n for states in itertools.product(range(4), repeat=6):
\n cases.append([(i+1, ['A','A','B','C','A','B'][i], ['Asia','Europe','America'][v-1])
\n for i, v in enumerate(states) if v])
\n rng = random.Random(618)
\n for _ in range(100):
\n rows = [(i, rng.choice(['A','B','C','D']), rng.choice(['Asia','Europe','America']))
\n for i in range(1, rng.randint(1, 35))]
\n rng.shuffle(rows)
\n cases.append(rows)
\n commands = ['USE blog_roster;']
\n for i, rows in enumerate(cases):
\n commands.extend(['DELETE FROM Student;', insert(rows), f"SELECT '__case_{i}__';", final])
\n outputs = chunks(sql('\\\\n'.join(commands)), '__case_')
\n assert len(outputs) == len(cases)
\n for i, (got, rows) in enumerate(zip(outputs, cases)):
\n assert got == oracle(rows), {'case': i, 'input': rows, 'actual': got, 'expected': oracle(rows)}
\n record['cases'] = len(cases)
\n record['exhaustiveCases'] = 4**6
\n record['randomSeed'] = 618
\n variants = {
\n 'name-rank': final.replace('ROW_NUMBER()', 'RANK()').replace('ORDER BY name, student_id', 'ORDER BY name'),
\n 'dense-name-rank': final.replace('ROW_NUMBER()', 'DENSE_RANK()').replace('ORDER BY name, student_id', 'ORDER BY name'),
\n 'global-number': final.replace('PARTITION BY continent ', ''),
\n 'wrong-group': final.replace('GROUP BY pos\\\\nORDER BY pos', 'GROUP BY continent\\\\nORDER BY continent'),
\n 'empty-string': final.replace('THEN name END', "THEN name ELSE '' END"),
\n 'reverse-output': final.replace('ORDER BY pos;', 'ORDER BY pos DESC;')
\n }
\n record['mutants'] = []
\n for name, query in variants.items():
\n assert query != final
\n got = sql('USE blog_roster; DELETE FROM Student;' + insert(main) + query)
\n assert got != oracle(main), name
\n record['mutants'].append({'name': name, 'input': main, 'actual': got,
\n 'expected': oracle(main), 'rejected': True})
\n record['passed'] = True
\nfinally:
\n server.terminate()
\n try:
\n server.wait(timeout=30)
\n except subprocess.TimeoutExpired:
\n server.kill()
\n server.wait(timeout=5)
\n record['serverStopped'] = server.poll() is not None
\n record['socketRemoved'] = not sock.exists()
\n (run \/ 'results.json').write_text(json.dumps(record, indent=2))
\n print('RESULTS=' + str(run \/ 'results.json'))
\nassert record['passed'] and record['serverStopped'] and record['socketRemoved']
\nprint('ROSTER_CHECKS_PASSED')<\/p>\n
\u53c2\u8003：MySQL 8\u7a97\u53e3\u51fd\u6570\u8bf4\u660e\u3002\u672c\u6587\u53ea\u9a8c\u8bc1\u8fd9\u91cc\u7ea6\u5b9a\u7684\u7ec3\u4e60，\u6ca1\u6709\u5728LeetCode\u63d0\u4ea4，\u4e5f\u6ca1\u6709\u6d4b\u8bd5\u5927\u89c4\u6a21\u540d\u5355\u7684\u6027\u80fd\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u4e09\u4efd\u540d\u5355\u957f\u77ed\u4e0d\u540c，SQL\u600e\u4e48\u6a2a\u7740\u6392？\u5148\u7ed9\u6bcf\u7ec4\u5b66\u751f\u627e\u4e2a\u4f4d\u7f6e
\n\u5047\u8bbe\u624b\u91cc\u6709\u4e09\u4efd\u5b66\u751f\u540d\u5355，\u8981\u653e\u8fdb\u4e00\u5f20\u62a5\u8868\u91cc：\u4e9a\u6d32\u4e00\u5217，\u6b27\u6d32\u4e00\u5217，\u7f8e\u6d32\u4e00\u5217\u3002\u6bcf\u5217\u6309\u59d3\u540d\u6392\u5e8f，\u54ea\u4efd\u540d\u5355\u5148\u6392\u5b8c，\u540e\u9762\u7684\u683c\u5b50\u5c31\u7559\u7a7a\u3002
\n\u8fd9\u542c\u8d77\u6765\u4e0d\u590d\u6742，\u590d\u5236\u5230\u8868\u683c\u91cc\u5c31\u80fd\u6446\u51fa\u6765\u3002\u4f46\u5728\u6570\u636e\u5e93\u91cc，\u5b66\u751f\u539f\u672c\u662f\u4e00\u884c\u4e00\u884c\u5b58\u7684\u3002\u6211\u4eec\u5148\u4e0d\u8981\u627e\u201c\u884c\u8f6c\u5217\u6a21\u677f\u201d，\u770b\u6e05\u695a\u8981\u600e\u6837\u6446\u3002
\n\u4e0b\u9762\u8fd9\u9053\u7ec3\u4e60\u6765<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[114,768,100],"topic":[],"class_list":["post-121615","post","type-post","status-publish","format-standard","hentry","category-server","tag-mysql","tag-sql","tag-100"],"yoast_head":"\n