官方解答｜標籤長度序

官方解答｜標籤長度序

解題目標

輸出所有原始欄位。

查詢步驟

建立完整 ORDER BY，先決定每個鍵的 NULL 排位，再比較值與後續平手鍵。去重若有要求則先完成，LIMIT／OFFSET 最後套用。 依序輸出欄位 id, zone, reading, weight, flag, day, label。最終排序：label 字元數 升序，NULL 最後；再按 label 升序，NULL 最後；再按 id 升序。

正確性

各階段分別實現指定篩列、數值或分組規則。每個保留項目只貢獻題意要求的次數；未配對、缺值與平手依明文規則處理。最後固定別名及排序，因而得到要求的列數、欄位順序與內容。

NULL 與邊界

NULL 表示缺值，不等於 0 或空字串。WHERE／HAVING 只保留真值，假或未知皆排除；一般等號不匹配 NULL。算術操作數缺值時結果為 NULL，除非題目指定備援規則。COUNT(欄位)、SUM、AVG、MIN、MAX 忽略該欄 NULL；COUNT(*) 計算列數。未要求分組或去重時保留所有重複紀錄。空結果仍須具有指定欄名。 輸出所有原始欄位。

公開範例

fixture 為 {"tables":[{"name":"readings","columns":[{"name":"id","type":"INTEGER","nullable":false},{"name":"zone","type":"TEXT","nullable":true},{"name":"reading","type":"INTEGER","nullable":true},{"name":"weight","type":"INTEGER","nullable":true},{"name":"flag","type":"INTEGER","nullable":true},{"name":"day","type":"INTEGER","nullable":false},{"name":"label","type":"TEXT","nullable":true}],"rows":[[21,"north",7,null,null,2,"a"],[41,"south",-2,0,1,4,null],[11,"west",null,4,0,2,"beta"],[31,"north",3,2,1,1,"alpha"]]}]}。按題意處理並完成排序後得到 {"columns":["id","zone","reading","weight","flag","day","label"],"rows":[[21,"north",7,null,null,2,"a"],[11,"west",null,4,0,2,"beta"],[31,"north",3,2,1,1,"alpha"],[41,"south",-2,0,1,4,null]]}。即使沒有結果列，也必須保留指定欄名。

複雜度

設輸入列數為 n、結果列數為 k。逐列計算或統計為 O(n)，比較排序通常需要 O(n log(n+1))；分組／去重／視窗也可能需要排序與 O(n) 暫存資料。輸出儲存為 O(k)。實際成本依資料庫執行計畫而定，不能只從 SQL 的行數推斷。

三種方言

下方分別提供完整 SQLite、PostgreSQL、MySQL 查詢。文字排序明寫二進位／字元碼排序；本題 ASCII 域中 SQLite LENGTH 對應其他引擎 CHAR_LENGTH。SQL NULL 輸出 JSON null，不輸出文字 "NULL"。

SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY CASE WHEN CHAR_LENGTH(label) IS NULL THEN 1 ELSE 0 END ASC, CHAR_LENGTH(label) ASC, CASE WHEN label IS NULL THEN 1 ELSE 0 END ASC, label COLLATE utf8mb4_bin ASC, id ASC;

SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY CASE WHEN LENGTH(label) IS NULL THEN 1 ELSE 0 END ASC, LENGTH(label) ASC, CASE WHEN label IS NULL THEN 1 ELSE 0 END ASC, label COLLATE BINARY ASC, id ASC;

SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM (
SELECT id AS id, zone AS zone, reading AS reading, weight AS weight, flag AS flag, day AS day, label AS label
FROM readings
) query_result
ORDER BY CASE WHEN CHAR_LENGTH(label) IS NULL THEN 1 ELSE 0 END ASC, CHAR_LENGTH(label) ASC, CASE WHEN label IS NULL THEN 1 ELSE 0 END ASC, label COLLATE "C" ASC, id ASC;