MySQL - 始终返回恰好 n 条记录

Question

我有一个表，其中包含任何给定外键 ID 的许多(总是至少 1 个，有时超过 6 个)值。下面是一个查询，该查询按降序获取值并将输出限制为最多 6 个值:

SELECT Diameter
FROM  `TreeDiameters` 
WHERE TreeID = ?
ORDER BY Diameter DESC 
LIMIT 0 , 6;

但是我怎样才能总是准确地返回 6 个值(多余的值是 NULL 或空白或其他值)？

Answer 1

您可以使用union all和limit来做到这一点:

(SELECT Diameter
 FROM  `TreeDiameters` 
 WHERE TreeID = ?
) union all
(select NULL as Diameter
 from (select 1 as n union all select 2 union all select 3 union all select 4 union all
       select 5 union all select 6
      ) n 
)
ORDER BY Diameter DESC
LIMIT 0, 6;

MySQL 将 NULL 值按降序排列放在最后。但您也可以具体说明:

ORDER BY (Diameter is not null) DESC, Diameter DESC