假設我有以下 SQLite v3 表，其中包含每個國家/地區的一些名稱：
Germany Peter,Jan,David,Florian
USA James,Joe,Bob,David,Alan,George
UK George,Jack,Peter
Israel David,Moshe,Chaim
每個國家/地區的名稱使用逗號分隔。
我想計算總共有多少個唯一名稱，在這種情況下將是 12 個（因為例如 David 在德國、美國和以色列）。
有沒有直接的方法可以通過 SQL 查詢來做到這一點？
uj5u.com熱心網友回復：
有沒有直接的方法可以通過 SQL 查詢來做到這一點？
我相信以下將直接產生唯一名稱的數量：-
WITH
splt(value,rest) AS
(
SELECT
substr(names,1,instr(names,',')-1),
substr(names,instr(names,',') 1)||','
FROM thetable
UNION ALL SELECT
substr(rest,1,instr(rest,',')-1),
substr(rest,instr(rest,',') 1)
FROM splt
WHERE length(rest) > 0
LIMIT 20 /* just in case limit to 20 iterations increase if more iterations exected */
),
intermediate AS
(
SELECT count(*),
group_concat(value)
FROM splt
WHERE length(value) > 0
GROUP BY value
)
SELECT count(*) AS unique_names FROM intermediate;
解釋
這假定該國是在一列，這些名稱在另一個列和列名是名在一個表名為thetable
查詢由 2 個 CTE（公用表運算式，基本上是臨時表）組成。
第一個名為splt 的CTE是遞回的，它將串列中的每個名稱提取為一行。
請注意，遞回 CTE 必須具有某種方法來確定何時停止迭代 WHERE 子句或 LIMIT。在兩者都使用的情況下，當提取值的長度大于 0 時，WHERE 子句是停止迭代的正確檢查（基于每個源行）。這LIMIT 20是一種預防措施，當然它可能會增加。
名為中間的第二個 CTE然后通過使用 splt CTE 的結果根據值進行分組來洗掉 0 長度的名稱和重復項。
最后計算剩余的行數。
示范
使用以下內容來演示：-
DROP TABLE IF EXISTS thetable;
CREATE TABLE IF NOT EXISTS thetable (country TEXT, names TEXT);
INSERT INTO thetable VALUES
('Germany','Peter,Jan,David,Florian'),
('USA','James,Joe,Bob,David,Alan,George'),
('UK','George,Jack,Peter'),
('Isreal','David,Moshe,Chaim'),
/*<<<<< ADDED to test resillience*/
('Spain',''),
('France',null),
('Italy',zeroblob(100))
;
WITH
splt(value,rest) AS
(
SELECT
substr(names,1,instr(names,',')-1),
substr(names,instr(names,',') 1)||','
FROM thetable
UNION ALL SELECT
substr(rest,1,instr(rest,',')-1),
substr(rest,instr(rest,',') 1)
FROM splt
WHERE length(rest) > 0
LIMIT 20 /* just in case limit to 20 iterations increase if more iterations exected */
),
intermediate AS
(
SELECT count(*),
group_concat(value)
FROM splt
WHERE length(value) > 0
GROUP BY value
)
SELECT count(*) AS unique_names FROM intermediate;
DROP TABLE IF EXISTS thetable;
結果是：-
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/382485.html