【SQL语法之窗口函数：一对多关系，对应关系取值】

• 一对多关系取多条

公用表达式：为每一个对应关系的数据创建一个序号
WITH RankedChildren AS (
    SELECT
        hwr.resourceid,hwr.company,hwr.jobtitle,hwr.startdate,hwr.enddate,
        # 窗口函数
        ROW_NUMBER() OVER (
            PARTITION BY hwr.resourceid -- 关联关系id及分组id
            ORDER BY hwr.startdate DESC -- 需要排序的字段
        ) AS rn
    FROM
        HrmWorkResume hwr 
    INNER JOIN HrmResource hr ON hwr.resourceid = hr.id
)
SELECT
     rc.resourceid,rc.company,rc.jobtitle,rc.startdate,rc.enddate
FROM
    RankedChildren rc
WHERE
    rc.rn <= 3 -- 取对应关系的前三条
ORDER BY 
	rc.resourceid, rc.rn;  -- 取完之后的数据排序	

• 一对多关系取一条

SELECT 
DISTINCT  hwr.resourceid id,
FIRST_VALUE(hwr.company)OVER(PARTITION BY hwr.resourceid ORDER BY hwr.enddate DESC) company,
FIRST_VALUE(hwr.jobtitle)OVER(PARTITION BY hwr.resourceid ORDER BY hwr.enddate DESC) jobtitle,
FIRST_VALUE(hwr.startdate)OVER(PARTITION BY hwr.resourceid ORDER BY hwr.enddate DESC) startdate,
FIRST_VALUE(hwr.enddate)OVER(PARTITION BY hwr.resourceid ORDER BY hwr.enddate DESC) enddate FROM  HrmWorkResume hwr ORDER BY hwr.resourceid