查询客户的亲属关系和余额

根据上面2个表，查询客户的亲属关系和余额前3名，得到如下结果

SQL代码如下

WITH T1 AS(
SELECT A.ID
,RELA_ID
,RELA
,C.BAL BAL1
FROM DBO.FAMILY_BAL A
LEFT JOIN DBO.FAMILY B
ON A.ID=B.ID
LEFT JOIN DBO.FAMILY_BAL C
ON C.ID=B.RELA_ID
WHERE RELA_ID IS NOT NULL
UNION ALL
SELECT ID,ID ,'',BAL
FROM DBO.FAMILY_BAL A
),
T2 AS(
SELECT T1.* 
,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY BAL1 DESC) RN
FROM T1
)
SELECT ID
,MAX(CASE WHEN RELA='父母' THEN RELA_ID END) 父母
,MAX(CASE WHEN RELA='配偶' THEN RELA_ID END) 配偶
,MAX(CASE WHEN RELA='子女' THEN RELA_ID END) 子女
,MAX(CASE WHEN RN=1 THEN BAL1 END) BAL1
,MAX(CASE WHEN RN=2 THEN BAL1 END) BAL2
,MAX(CASE WHEN RN=3 THEN BAL1 END) BAL3
FROM T2
GROUP BY ID