Level1 Table
=========================================================
Pk_iD | name | Content | RegDate
----------------------------------------------------------
1 AAA 사과는 얼마에여? 2018-01-23 21:20
2 BBB 서울역이 어디에요? 2018-01-24 14:41
3 CCC 방배동은거리가가깝나요? 2018-01-23 19:02
Level_2 Table
============================================================
Pk_iD | Levle1_PK_ID | name | Content | RegDate
---------------------------------------------------------------------------------------------
1 1 A222 2000원입니다 2018-01-23 23:40
2 1 AAA 할인은 안되나요? 2018-01-23 23:55
3 1 A222 물론 됩니다 2018-01-23 24:01
4 3 C222 택시로10분걸립나다 2018-01-23 19:52
5 3 CCC 택시는 어디에서잡죠? 2018-01-23 20:04
6 3 C222 저기가택시정류장입니다 2018-01-23 20:47
결과
======================================================================
Levle1_PK_ID | name | Content | RegDate
-----------------------------------------------------------------------------------------------------
1 A222 물론 됩니다 2018-01-23 24:01
2 BBB 서울역이 어디에요? 2018-01-24 14:41
3 C222 저기가택시정류장입니다 2018-01-23 20:47
결과에서처럼 레벨1테이블에 해당하는 레벨2테이블의 쪽지내용중에 가장 최근걸 표시하고 레벨2에 내용이 없으면 레벨1의쪽지내용을 표시해주고 싶습니다
도저히못하겟습니다
도와주세요
Comment 2
-
이리
2018.08.09 11:11
;WITH Level1_Table(Pk_iD, name, Content, RegDate)AS(SELECT 1, 'AAA', '사과는 얼마에여?', '2018-01-23 21:20' UNION ALLSELECT 2, 'BBB', '서울역이 어디에요?', '2018-01-24 14:41' UNION ALLSELECT 3, 'CCC', '방배동은거리가가깝나요?', '2018-01-23 19:02'),Level2_Table(Pk_iD, Levle1_PK_ID, name, Content, RegDate)AS(SELECT 1, 1, 'A222', '2000원입니다', '2018-01-23 23:40' UNION ALLSELECT 2, 1, 'AAA', '할인은 안되나요?', '2018-01-23 23:55' UNION ALLSELECT 3, 1, 'A222', '물론 됩니다', '2018-01-23 24:01' UNION ALLSELECT 4, 3, 'C222', '택시로10분걸립나다', '2018-01-23 19:52' UNION ALLSELECT 5, 3, 'CCC', '택시는 어디에서잡죠?', '2018-01-23 20:04' UNION ALLSELECT 6, 3, 'C222', '저기가택시정류장입니다', '2018-01-23 20:47')SELECT ISNULL(T2.Levle1_PK_ID, T1.Pk_iD) AS Levle1_PK_ID, ISNULL(T2.name, T1.name) AS name, ISNULL(T2.Content, T1.Content) AS Content, ISNULL(T2.RegDate, T1.RegDate) AS RegDateFROM Level1_Table AS T1OUTER APPLY(SELECT TOP 1 *FROM Level2_Table AS A1WHERE A1.Levle1_PK_ID = T1.Pk_iDORDER BYA1.RegDate DESC) AS T2 -
문상연
2018.08.10 14:43
오 정말 감사합니다잘됩니다
