티스토리 뷰
공통 테이블 표현식을 사용하여 코드를 유지하는 데 도움이 될 수있는 문제를 분석 할 수 있습니다.
나는 lag / lead를 사용하지 않았는데, 쌍에 2 개의 행만 있기 때문에 행에 번호를 매기고 테이블을 결합하는 것이 더 빠르고 쉽게 따라갈 수있었습니다.
다음은 질문에 답하고 테스트하는 데 사용한 코드입니다.
create table #source
(
[NAME] varchar(200),
[BRAND] varchar(200),
[REFERENCE] varchar(200)
);
insert into #source values
('Gu','Skirt','101128'),
('Cci','Pants','101127'),
('Cha','Skirt','paired'),
('Gu','Pants','101128'),
('Nel','Skirt','nonpaired'),
('Gir','Pants','101188'),
('Baud','Skirt','dropped'),
('Le','Pants','paired'),
('Gir','Skirt','101188'),
('Vis','Socks',''),
('Cci','Skirts','101127'),
('Le','Socks','101188'),
('Uno','Socks','101101');
select * from #source;
with cteNumericRef as
(
select [NAME],[BRAND],[REFERENCE]
from #source
where ISNUMERIC([REFERENCE]) = 1
)
, cteCheckRow as
(
select [REFERENCE],
'CHECK' as [COMMENT]
from cteNumericRef
group by [REFERENCE]
having count(*) <> 2
)
, ctePairedRow as
(
select
num_ref.[NAME]
, num_ref.[BRAND]
, num_ref.[REFERENCE]
, row_number() over (partition by num_ref.[REFERENCE] order by num_ref.[NAME]) as [Pair_Num]
from cteNumericRef num_ref
left join cteCheckRow check_row
on check_row.[REFERENCE] = num_ref.[REFERENCE]
where check_row.[REFERENCE] is null
)
, cteTextRow as
(
select [NAME],[BRAND],[REFERENCE],
case [REFERENCE]
when 'paired' then 'PAIRED'
when 'nonpaired' then 'UNIQUE'
when 'dropped' then 'DROPPED'
when '' then ''
else 'CHECK' end as [COMMENT]
from #source
where ISNUMERIC([REFERENCE]) <> 1
)
select
left_row.[NAME]
, left_row.[BRAND]
, left_row.[REFERENCE]
, right_row.[BRAND] as [COMMENTS]
from ctePairedRow left_row
inner join ctePairedRow right_row
on left_row.[REFERENCE] = right_row.[REFERENCE]
and left_row.[Pair_Num] <> right_row.[Pair_Num]
union all
select
num_ref.[NAME]
, num_ref.[BRAND]
, num_ref.[REFERENCE]
, check_row.[COMMENT]
from cteNumericRef num_ref
inner join cteCheckRow check_row
on check_row.[REFERENCE] = num_ref.[REFERENCE]
union all
select
[NAME]
, [BRAND]
, [REFERENCE]
, [COMMENT]
from cteTextRow;
drop table #source
SELECT Name,
Brand,
Reference,
CASE WHEN Reference = 'Paired' THEN 'Paired'
WHEN Reference = 'nonpaired' THEN 'Unique'
WHEN Reference = 'dropped' THEN 'DROPPED'
WHEN Reference = ' ' THEN 'blanks'
WHEN Reference = Next_Ref AND rownum = 1 THEN next_brand
WHEN Reference = Prev_Ref AND rownum = 2 THEN prev_brand
END AS Comments
FROM
(
SELECT Name,
Brand,
Reference,
LAG( Reference, 1 )OVER PARTITION BY ( Reference ORDER BY Brand ) AS Prev_Ref,
LEAD( Reference, 1 )OVER PARTITION BY ( Reference ORDER BY Brand ) AS Next_Ref,
LAG( Brand, 1 ) OVER PARTITION BY ( Reference ORDER BY Brand ) AS Prev_Brand,
LEAD( Brand, 1 ) OVER PARTITION BY ( Reference ORDER BY Brand ) AS Next_Brand,
ROW_NUMBER( ) OVER PARTITION BY ( Reference ORDER BY Brand ) AS rownum
FROM Data
);
출처
https://stackoverflow.com/questions/39970010