获取与缺少ID排在红移

问题描述：

id | name 
---|----- 
1 | Sarah 
3 | Pat 
4 | Lea

我正在寻找丢失的行。我试过使用generate_series和左连接，但这是你在Redshift中无法做到的事情，因为generate_series不受支持。

是否有可能没有临时表呢？

编辑

最后没with a temporary table（0至1_000_000）看到答案。

您需要临时表或子查询。因为你需要一些东西来告诉你什么是全套，知道什么是缺少的。所以答案是***没有***。 –

这可能有助于http://stackoverflow.com/questions/17282276/using-sql-function-generate-series-in-redshift –

你怎么知道哪一行是“丢失”？ –

答

这可能不是最优的。但这是我的做法

-- create temporary table 
CREATE TABLE series (id INT) SORTKEY(id); 

-- insert 0 to 1_000_000 
INSERT INTO series WITH seq_0_9 AS 
(SELECT 0 AS num 
UNION ALL SELECT 1 AS num 
UNION ALL SELECT 2 AS num 
UNION ALL SELECT 3 AS num 
UNION ALL SELECT 4 AS num 
UNION ALL SELECT 5 AS num 
UNION ALL SELECT 6 AS num 
UNION ALL SELECT 7 AS num 
UNION ALL SELECT 8 AS num 
UNION ALL SELECT 9 AS num), 
        seq_0_999 AS 
(SELECT a.num + b.num * 10 + c.num * 100 AS num 
FROM seq_0_9 a, 
    seq_0_9 b, 
    seq_0_9 c) 
SELECT a.num + b.num * 1000 AS num 
FROM seq_0_999 a, 
seq_0_999 b 
ORDER BY num; 

-- Why not 
VACUUM series; 

-- LEFT OUTER JOIN with table inverted and with the interval 
SELECT * 
FROM series 
LEFT OUTER JOIN other_table ON series.id = other_table.id 
WHERE series.id BETWEEN 0 AND 4 
ORDER BY series.id;

获取与缺少ID排在红移

相关推荐