1
--以下是错误的sql，唯一难住我的是就是关联查询最新记录了，之前想当然地用group by来分组获取最新记录，但是会报错，group by的字段必须与查询的字段相对应，除非是聚合函数包裹着
2
select a.WEBCHECKID,a.WEBSITE,b.RECORDID,b.access_state,b.access_date from TFSWXTS_WEBCHECK a left join TFSWXTS_WEBRECORD b on a.WEBCHECKID=b.WEBCHECKID
3
group by b.WEBCHECKID,b.access_date having b.access_state=max(b.access_state)
4
--报错不是group by 的表达式

1
row_number() over(partition by b.webcheckid order by b.access_date desc)
2

3
--partition by 按字段分组
4
--order by 分组，按组内排序，desc即最新在第一条
5
--row_number() 按分组内的行个数，生成行号；1,2,3，……

1
--行号作为新字段,并用一个新select包裹，获取第一行数据，即为新记录
2
select * from (
3
         select a.WEBCHECKID,a.WEBSITE,b.RECORDID,b.access_state,b.access_date,
4

5
         row_number() over(partition by b.webcheckid order by b.access_date desc) as ran
6
          from TFSWXTS_WEBCHECK a left join TFSWXTS_WEBRECORD b on a.WEBCHECKID=b.WEBCHECKID
7
        ) where recordid is  null or ran=1