Hero Image
[SQL] Ranking

先建立一個練習表格： CREATE TABLE Department ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL ); CREATE TABLE Employee ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, salary INT, dep_id INT, ); INSERT INTO Employee(name, salary, dep_id) VALUES ('Mandy', 45000, 2); INSERT INTO Employee(name, salary, dep_id) VALUES ('Emily', 43000, 1); INSERT INTO Employee(name, salary, dep_id) VALUES ('Sylvia', 26000, 2); INSERT INTO Employee(name, salary, dep_id) VALUES ('Eva', 48000, 3); INSERT INTO Employee(name, salary, dep_id) VALUES ('Sandra', 33000, 3); INSERT INTO Employee(name, salary, dep_id) VALUES ('Lily', 28000, 2); INSERT INTO Employee(name, salary, dep_id) VALUES ('April', 50000, 1); INSERT INTO Employee(name, salary, dep_id) VALUES ('Cindy', 43000, 1); INSERT INTO Employee(name, salary, dep_id) VALUES ('Kay', 40000, 3); INSERT INTO Department(name) VALUES ('IT'); INSERT INTO Department(name) VALUES ('RD'); INSERT INTO Department(name) VALUES ('QA'); 資料排序 直接用 ORDER BY 無法得到秩(排名)

June 17, 2022 Read
Hero Image
[SQL] MERGE

一段神奇的語法，可以指定對舊資料、新的資料分別進行不同的動作(通常是更新、新增)， 不指定的話預設是 BY TARGET， 動作目標都是 TARGET。 在更新清單的時候特別好用。 MERGE INTO TABLE1 DEST -- 目標表格 USING( SELECT :K1 K1, :K2 K2, :K3 K3, :K4 K4, FROM DUAL) SRC ON( DEST.K1 = SRC.K1 AND DEST.K2 = SRC.K2 AND DEST.K4 = SRC.K4 ) -- TARGET 有，SOURCE 沒有，更新 TARGET 裡面的紀錄 WHEN MATCHED THEN UPDATE SET F1 = SRC.F1, F2 = SRC.F2, -- TARGET 沒有，SOURCE 有，SOURCE 新增到 TARGET WHEN NOT MATCHED THEN INSERT ( K1, K2, K4, K3, F1, F2 ) VALUES ( :K1, :K2, :K4, :K3, :F1, :F2) Reference [StackOverflow] Performing MERGE with Dapper.

June 14, 2022 Read
Hero Image
[SQL] 用 CTE (Common Table Expression) 達成遞迴查詢，建立 MenuTree

建立一個暫存表 testCTE，並暫存查詢語句(所有資料)的結果 MSSQL CTE 名稱前不需加上 RECURSEIVE 關鍵字，必須使用 UNION ALL PostgreSQL：CTE 名稱前需加上 RECURSIVE 關鍵字，可用 UNION 或 UNION ALL WITH CTE (id, name, parentId, lvl) AS ( -- 取得第一層的資料(Anchor member)(假設沒有 parentId 的是第一層) SELECT id, name parentId, 0 AS lvl FROM menus WHERE parentId IS NULL UNION ALL -- 遞迴取得 Recursive member ( SELECT A.id, A.name, A.parentId, B.lvl + 1 AS lvl FROM menus A INNER JOIN CTE B on A.parentIdi = B.d ) ) SELECT * FROM CTE WHERE id=1 ORDER BY LEVEL Reference [StackOverflow] How to create a query from a menu tree using RECURSIVE CTE?

April 25, 2022 Read