sql - 选择不存在的行

Question

假设我有一张 table :

ColumnA       ColumnB
---------------------------------
1             10.75
4             1234.30
6             2000.99

如何编写一个 SELECT 查询，结果如下:

ColumnA    ColumnB
---------------------------------
1             10.75
2             0.00
3             0.00
4             1234.30
5             0.00
6             2000.99

Answer 1

您可以使用 CTE 创建一个从 1 到表中最大值的数字列表:

; with  numbers as
        (
        select  max(ColumnA) as nr
        from    YourTable
        union all
        select  nr - 1
        from    numbers
        where   nr > 1
        )
select  nr.nr as ColumnA
,       yt.ColumnB
from    numbers nr
left join
        YourTable yt
on      nr.nr = yt.ColumnA
order by
        nr.nr
option  (maxrecursion 0)

See it working at SQL Fiddle.