删除除使用 EXCEPT SQLite 命令的行之外的所有行

Question

从具有名称列的数据集字符中，我想使用最短和最长的名称以及它们各自的长度查询两个名称，当有多个最小或最大的名称时，我选择按字母顺序排序时排在第一位的名称。

通过该查询，我得到所有最短和最长的名称 (A(

SELECT 
name, LENGTH(name) AS LEN 
FROM 
character 
WHERE 
length(name) = (SELECT MAX(LENGTH(name)) FROM character) 
OR length(name) = (SELECT MIN(LENGTH(name)) FROM character) 

有了这个，我得到了所有最短的名字，除了第一个按字母顺序排列的名字(B(

SELECT 
name, LENGTH(name) AS LEN 
FROM 
character 
WHERE 
length(name) = (SELECT MIN(LENGTH(name)) FROM character) 
ORDER BY 
name DESC
LIMIT 10 OFFSET 2;

当我尝试从A中删除B时

A EXCEPT B

我希望保留第一个最短的名称，但它没有出现。

Answer 1

我会在这里使用ROW_NUMBER：

WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY LENGTH(name), name) rn_min,
ROW_NUMBER() OVER (ORDER BY LENGTH(name) DESC, name) rn_max
FROM character
)
SELECT name, LENGTH(name) AS LEN
FROM cte
WHERE 1 IN (rn_min, rn_max)
ORDER BY LENGTH(name);

Answer 2

当您在B查询中设置OFFSET 2时，您不会得到：除前 1个按字母顺序排列
之外的所有最短名称 相反，你会得到：

除前 2个名称外的所有最短名称都按字母顺序排列，
因为这就是OFFSET 2所做的：它跳过前 2 行。

代码的另一个问题是B查询中的ORDER BY子句。
如果你有这个：

SELECT name,LENGTH(name) AS LEN FROM character 
WHERE length(name) = (select max( LENGTH(name)) from character ) 
or length(name) = (select min( LENGTH(name)) from character) 
EXCEPT
SELECT name,LENGTH(name) AS LEN FROM character 
WHERE length(name) = (select min( LENGTH(name)) from character) 
ORDER BY name desc LIMIT 10 OFFSET 2;

您可能认为ORDER BY子句(以及LIMIT和OFFSET(仅适用于您的B查询，但这不是它的解释方式。
实际上，ORDER BY(以及LIMIT和OFFSET(在返回行后应用于整个查询。

若要使用与类似代码获取所需的结果，必须使用子查询来包装B查询，如下所示：

SELECT name,LENGTH(name) AS LEN FROM character 
WHERE length(name) = (select max( LENGTH(name)) from character ) 
or length(name) = (select min( LENGTH(name)) from character) 
EXCEPT
SELECT * FROM (
SELECT name,LENGTH(name) AS LEN FROM character 
WHERE length(name) = (select min( LENGTH(name)) from character) 
ORDER BY name desc LIMIT 10 OFFSET 1
)