Удалить строку с нулевым значением с помощью CTE

У меня есть некоторые данные, как

RowIdentifier   ID  RowID   Position    Data    Rn
1             1      1       a1          A1      1
2             1      2       a2          A2      1
3             1      3       a3          NULL    1
4             1      4       a3          A3      2
5             1      1       b1          B1      1
6             1      2       b2          NULL    1
7             1      3       b2          B2      2
8             1      4       b3          B3      1

Желаемый результат

ID  RowID  Position  Data
1     1     a1       A1
1     1     b1       B1
1     2     a2       A2
1     2     b2       B2
1     3     a3       A3
1     3     b3       B3

Мне нужно удалить те строки, в которых позиции дублируются и чьи данные равны нулю. т. е. в примере в RowIdentifier 3 и 4 значение в столбце Position равно a3, но thired запись RowIdentifier не будет отображаться в окончательном выводе, поскольку она имеет значение null в столбце Data.

ddl как под

Declare @t table(RowIdentifier int identity,ID int,RowID int,Position varchar(10),Data varchar(10),Rn int)
    Insert into @t
    Select 1,1,'a1','A1',1 union all
    Select 1,2,'a2','A2',1 union all
    Select 1,3,'a3',null,1 union all
    Select 1,4,'a3','A3',2 union all
    Select 1,1,'b1','B1',1 union all
    Select 1,2,'b2',null,1 union all
    Select 1,3,'b2','B2',2 union all
    Select 1,4,'b3','B3',1 

    Select * from @t

Мой подход такой, как под

;with cte as(
Select ID,RowID,Position,Position as p2,Data,RowIdentifier from @t
union all
select c4.ID,c4.RowID,c4.Position,c5.Position , c4.Data,c4.RowIdentifier
from cte c5
join @t c4 on c4.Position = c5.Position
where c5.RowIdentifier < c4.RowIdentifier
 )
 ,
 cte2 as(
 select * , rn = Row_Number() over(PARTITION by position order by RowIdentifier)
 from cte where Data is not null)

select ID,RowID,Position,Data from cte2 where rn =1

Но не работает в соответствии с ожидаемым результатом. Мой вывод

ID  RowID   Position    Data
1   1   a1  A1
1   2   a2  A2
1   4   a3  A3
1   1   b1  B1
1   3   b2  B2
1   4   b3  B3

Нужна помощь

Спасибо


sql-server tsql common-table-expression sql-server-2005
person aditi    schedule 21.06.2011    source источник
comment
Вам нужно условие завершения, когда вы рекурсивно вызываете CTE. т.е. WHERE something < somethingelse   -  person Johan    schedule 21.06.2011
comment
Я обновил свой запрос, но результат не такой, как ожидалось, хотя я близок... Буду благодарен, если вы укажете на ошибку в моем запросе.   -  person aditi    schedule 21.06.2011
comment
Почему вы хотите, чтобы RowID не был связан с исходным RowID? Это должно быть другое имя столбца, а RowID не должен быть в выходных данных. это вводит в заблуждение   -  person gbn    schedule 21.06.2011

Ответы (2)


arrow_upward
0
arrow_downward

Попробуйте этот код

Select 
    ID,
    dense_rank() over(order by substring(data,2,len(data))*1) as rowid,
    position,
    data 
from 
    @t 
where 
    data is not null
group by 
    ID,RowID,position,data
person Madhivanan    schedule 21.06.2011

arrow_upward
0
arrow_downward

Это простой агрегат после фильтра

SELECT
   ID, MIN(RowID) AS RowID, POSITION, Data
FROM
   @t
WHERE
   Data IS NOT NULL
GROUP BY
   ID, RowID, POSITION, Data
ORDER BY
   POSITION, RowID

Это сохраняет RowID из исходного набора данных, что было бы более правильным

3             1      3       a3          NULL    1
4             1      4       a3          A3      2   --take this row
person gbn    schedule 21.06.2011
comment
Если я правильно понимаю вопрос, мы хотим опускать нулевые значения только в том случае, если в наборе результатов уже есть ненулевые значения с тем же значением позиции. - person Tao; 21.06.2011
comment
@Tao: для этого случая нет примеров данных. Основываясь на том, что мы знаем, можно разумно предположить, что NULL случаются только там, где у нас есть дубликаты... - person gbn; 21.06.2011