Пропущенные числа в таблице

#sql #sql_server

Есть таблица с одним полем, заполненным числами, допустим:

1, 2, 3, 5, 7, 11, 12, 13, 22, 24, 25.

Нужно создать запрос, результатом которого будет таблица с полем, содержащим те же
числа, но что бы пропущенные числа заменялись, например нулём. То есть:

1, 2, 3, 0, 5, 0, 7, 0, 0, 0, 11, 12, 13, 0, 0, 0, 0, 0, 0, 0, 0, 22, 0, 24, 25

Я не слишком хорош в SQL, поэтому, по возможности, предложите не слишком сложное решение.
    

Ответы

Ответ 1

В "With" cоздали последовательность от 1 до 100, 
ограничили ее минимальным и максимальным значением из таблицы datatable.

Соединили последовательность с datatable левым внешним соединением.
в строках, где последовательность не нашла значение num в таблице datatable, 
datatable.num будет null, заменяем null на 0, 
результат вставляем в othertable.




create table datatable (num int);
GO






create table othertable (num int);
GO






insert into datatable (num) values (1), (2), (3), (5), (7), (11), (12), (13), (22),
(24), (25);
GO






with TblSeq as 
(
select 10*t1.n + t2.n + 1 as "num"
  from 
  (
    (select 0 n union select 1 union select 2 union select 3 union select 4 
    union select 5 union select 6 union select 7 union select 8 union select 9 ) t1
    cross join
    (select 0 n union select 1 union select 2 union select 3 union select 4 
    union select 5 union select 6 union select 7 union select 8 union select 9 ) t2
    ) 
where 
   10*t1.n + t2.n + 1 between (select min(num) from datatable) and (select max(num)
from datatable)
)
insert into othertable (num)
select 
  (case when datatable.num is null then 0 else datatable.num end) num
from TblSeq
  left outer join datatable on TblSeq.num = datatable.num

GO



25 rows affected






select * from othertable
GO



| num |
| --: |
|   1 |
|   2 |
|   3 |
|   0 |
|   5 |
|   0 |
|   7 |
|   0 |
|   0 |
|   0 |
|  11 |
|  12 |
|  13 |
|   0 |
|   0 |
|   0 |
|   0 |
|   0 |
|   0 |
|   0 |
|   0 |
|  22 |
|   0 |
|  24 |
|  25 |



db<>fiddle here


Ответ 2

WITH 
maxmin (vmin, vmax) AS
(SELECT MIN(num), MAX(num) 
 FROM datatable
),
temp (num) AS
(SELECT vmin FROM maxmin
 UNION ALL
 SELECT num+1 FROM temp WHERE num < (SELECT vmax FROM maxmin)
)
INSERT INTO othertable (num)
SELECT COALESCE(datatable.num, 0)
FROM temp
LEFT JOIN datatable ON temp.num = datatable.num


datatable - имя исходной таблицы, num - имя поля с числами в ней. 

othertable - заполняемая таблица, num - имя поля с числами в ней. 

db<>fiddle


Ответ 3

Если задача одноразовая - перенести данные из одной таблицы в другую с некоторыми
изменениями, то наиболее простое решение - использовать курсор.

DECLARE @counter INT= 1, @number INT;
DECLARE cur CURSOR LOCAL FAST_FORWARD
FOR SELECT st.Number
    FROM source_table AS st
    ORDER BY st.Number;
OPEN cur;
FETCH NEXT FROM cur INTO @number;
WHILE @@FETCH_STATUS = 0
    BEGIN
        WHILE @number > @counter
            BEGIN
                INSERT INTO target_table(Number) VALUES(0);
                SET @counter+=1;
            END;
        INSERT INTO target_table(Number) VALUES(@number);
        SET @counter+=1;
        FETCH NEXT FROM cur INTO @number;
    END;
CLOSE cur;
DEALLOCATE cur;   


Где source_table - исходная таблица, а target_table - искомая. Имя числового поля
в обеих таблицах - Number.