Вопросы Теги

Запрос Sql для присвоения значения столбцу с нулевым значением из другой строки на основе различных сценариев

from itertools import product

elemets = [None, 0, 1, 2]
l = product(elemets, repeat=5)
s = set(p for p in l if p.count(None) == 3)

print(s)

Выход: 

{(None, None, None, 0, 2), (None, None, 0, None, 2), (1, 2, None, None, None), (0, None, None, 1, None), (1, None, 2, None, None), (1, None, None, 2, None), (None, 2, 1, None, None), (None, None, None, 2, 1), (None, None, 2, None, 1), (None, None, None, 1, 1), (None, 0, None, 0, None), (None, 0, 0, None, None), (None, 1, None, None, 1), (2, 1, None, None, None), (2, None, None, 1, None), (None, 0, None, None, 1), (None, 0, None, 1, None), (0, None, None, 0, None), (0, None, 0, None, None), (None, None, 1, 1, None), (1, None, None, 1, None), (2, None, None, 2, None), (2, None, 2, None, None), (None, None, 2, None, 0), (None, None, None, 2, 0), (None, None, None, 1, 2), (0, 0, None, None, None), (None, 0, None, None, 0), (2, 0, None, None, None), (None, None, 0, 1, None), (None, None, 1, 2, None), (None, 0, None, 2, None), (None, 0, 2, None, None), (0, 1, None, None, None), (None, 2, None, 2, None), (None, 2, 2, None, None), (None, 1, None, 1, None), (None, None, 1, None, 0), (2, None, None, None, 2), (2, 2, None, None, None), (2, None, None, 0, None), (2, None, 0, None, None), (None, 1, None, 2, None), (None, 1, 2, None, None), (0, None, 1, None, None), (0, None, None, None, 1), (None, None, None, 0, 1), (None, None, 0, None, 1), (None, 2, None, 1, None), (None, 0, None, None, 2), (None, None, 1, None, 1), (2, None, 1, None, None), (2, None, None, None, 1), (None, None, 0, 2, None), (1, None, None, None, 0), (None, 1, None, None, 2), (None, 0, 1, None, None), (0, None, None, None, 2), (None, 2, None, None, 2), (0, None, None, None, 0), (None, None, 0, 0, None), (None, None, 2, 2, None), (None, None, None, 0, 0), (None, None, 0, None, 0), (None, 2, None, 0, None), (None, 2, 0, None, None), (1, None, 1, None, None), (None, None, 1, None, 2), (2, None, None, None, 0), (0, 2, None, None, None), (1, None, None, 0, None), (1, None, None, None, 1), (1, None, 0, None, None), (None, None, 1, 0, None), (None, 1, None, 0, None), (None, 1, 0, None, None), (None, None, 2, 1, None), (None, 2, None, None, 1), (1, 1, None, None, None), (None, None, None, 2, 2), (1, None, None, None, 2), (0, None, None, 2, None), (0, None, 2, None, None), (None, 1, 1, None, None), (None, None, None, 1, 0), (None, None, 2, None, 2), (None, 1, None, None, 0), (None, None, 2, 0, None), (1, 0, None, None, None), (None, 2, None, None, 0)}
1
задан trincot 18 January 2019 в 23:49
поделиться

2 ответа

Я думаю, что вам нужны ISNULL() и MAX() OVER(), так что ваш запрос будет иметь что-то вроде этого: 

SELECT  
    t1.PK
,   t1.Id
,   t1.Status
,   ISNULL(t1.Worker, MAX(t1.Worker) OVER(PARTITION BY Id) ) Worker
,   t1.CreatedDate
FROM #temp tl

ISNULL () проверит значение, если это значение равно null, заменит его вторичное значение. это тот же самый случай, который у вас есть в вашем запросе. 

MAX(t1.Worker) OVER(PARTITION BY Id)

Поскольку функции агрегирования исключают нули, мы используем это преимущество и используем его с предложением OVER() для разделения строк на Id и получения значения, которое нам нужно, с помощью одной из функций агрегирования.

0
ответ дан iSR5 18 January 2019 в 23:49
поделиться

Возможно, самый простой способ - это outer apply: 

select t.id, t.status, t2.worker, t.date
from t outer apply
     (select top (1) t2.*
      from t2
      where t2.worker is not null and t2.id >= t.id
      order by t2.id asc
     ) t2;

. Что вам действительно нужно, так это опция IGNORE NULLS в LEAD(). Однако SQL Server не поддерживает это.

Если вы хотите заполнить последние значения значением , предшествующим значению , следуйте той же логике с другой apply: 

select t.id, t.status,
       coalesce(tnext.worker, tprev.worker) as worker, t.date
from t outer apply
     (select top (1) t2.*
      from t2
      where t2.worker is not null and t2.id >= t.id
      order by t2.id asc
     ) tnext outer apply
     (select top (1) t2.*
      from t2
      where t2.worker is not null and t2.id <= t.id
      order by t2.id desc
     ) tprev;
0
ответ дан Gordon Linoff 18 January 2019 в 23:49
поделиться
Другие вопросы по тегам:

Похожие вопросы: