1레벨 질문 테이블
==========================
Level_1 table
------------------------------------------------
PK_ID | ID | Content | MDate
---------------------------------------------------
1 AA 제일좋아는 과일은? 2017-05-23
2 BB 좋은방 잇나요? 2017-03-05
3 CC 게임 종류는? 2017-11-25
4 DD 과외하시는 분 모집합니다 2017-03-25
Level_2 Table
------------------------------------------------------------------
PK_ID | Level_1_PK_ID | ID ! Content | MDate
-------------------------------------------------------------------
1 1 EE 난 사과요 2017-05-25
2 1 AA 요즘 사과가 얼마하나요? 2017-05-30
3 2 PP 보증금2,000 월30 2017-04-02
4 3 KK 리니지가 재미있죠 2017-11-26
5 1 EE 2개에 천원쯤해요 2017-06-01
6 3 TT 슈팅게임도 재미잇어요 2017-11-30
위 질문 답변레벨에서 레벨1기준으로 아래와 같이 표현하고 싶습니다
마지막 질문글을 리스트에 표현하고 싶습니다
Level_1_PK_ID | ID | Content | MDate
==========================================
1 EE 2개에 천원쯤해요 2017-06-01
2 PP 보증금2,000 월30 2017-04-02
3 TT 슈팅게임도 재미잇어요 2017-11-30
4 DD 과외하시는 분 모집합니다 2017-03-25
위와같이 표현하고 싶은데 어떻게 하면 되죠?
Comment 3
-
김덜덜
2017.06.30 11:03
-
문상연
2017.06.30 12:05
너무나도 감사드립니다
주말 잘보내세요
-
知音
2017.07.03 13:16
참고하세요with level_1 as (select 1 pk_id, 'AA' id, '제일좋아는 과일은? ' contents, '2017-05-23' mdate union allselect 2 pk_id, 'BB' id, '좋은방 잇나요? ' contents, '2017-03-05' mdate union allselect 3 pk_id, 'CC' id, '게임 종류는? ' contents, '2017-11-25' mdate union allselect 4 pk_id, 'DD' id, '과외하시는 분 모집합니다' contents, '2017-03-25' mdate), level_2 as (select 1 pk_id, 1 level_1_pk_id, 'EE' id, '난 사과요 ' contents, '2017-05-25' mdate union allselect 2 pk_id, 1 level_1_pk_id, 'AA' id, '요즘 사과가 얼마하나요?' contents, '2017-05-30' mdate union allselect 3 pk_id, 2 level_1_pk_id, 'PP' id, '보증금2,000 월30 ' contents, '2017-04-02' mdate union allselect 4 pk_id, 3 level_1_pk_id, 'KK' id, '리니지가 재미있죠 ' contents, '2017-11-26' mdate union allselect 5 pk_id, 1 level_1_pk_id, 'EE' id, '2개에 천원쯤해요 ' contents, '2017-06-01' mdate union allselect 6 pk_id, 3 level_1_pk_id, 'TT' id, '슈팅게임도 재미잇어요 ' contents, '2017-11-30' mdate)select isnull(l3.l1_pk_id, l1.pk_id ) pk_id, isnull(l3.id , l1.id ) id, isnull(l3.contents, l1.contents) contents, isnull(l3.mdate , l1.mdate ) mdatefrom level_1 l1left outer join (select pk_id, level_1_pk_id l1_pk_id, id, contents, mdate, row_number()over(partition by level_1_pk_id order by mdate desc) rnfrom level_2) l3on l1.pk_id = l3.l1_pk_idand l3.rn = 1order by 1
초짜라서.. 맞는지 모르겠네요
SELECTCASE
WHEN SQ1.Level_1_PK_ID IS NOT NULL THEN SQ1.Level_1_PK_ID
ELSE TB1.PK_ID
END AS Level_1_PK_ID,
CASE
WHEN SQ1.Level_1_PK_ID IS NOT NULL THEN SQ1.ID
ELSE TB1.ID
END AS ID,
CASE
WHEN SQ1.Level_1_PK_ID IS NOT NULL THEN SQ1.Content
ELSE TB1.Content
END AS Content,
CASE
WHEN SQ1.Level_1_PK_ID IS NOT NULL THEN SQ1.MDate
ELSE TB1.MDate
END AS MDate
FROM
Lv1_Table TB1 WITH (NOLOCK)
LEFT OUTER JOIN
(
SELECT
TB2.Level_1_PK_ID,
TB2.ID,
TB2.Content,
TB2.MDate,
DENSE_RANK() OVER (PARTITION BY Level_1_PK_ID ORDER BY MDate DESC) AS Num
FROM
Lv2_Table TB2
) SQ1
ON SQ1.Level_1_PK_ID = TB1.PK_ID
WHERE
SQ1.Num = '1' OR
SQ1.Num IS NULL