表A与表B是一结多的关系,取表B中最近的一条记录
分组统计,取排名为1的数据。根据StuId分组 StuId为重复的数据,按照SubmitTime 降序排列
Select * from (
SELECT A.Name,
B.*,
row = ROW_NUMBER() OVER(PARTITION BY A.StuId ORDER BY B.SubmitTime DESC)
FROM A
JOIN B
ON A.StuId = B.StuId ) AS t WHERE t.row = 1
如果多条(取要取得条数)
Select * from (
SELECT A.Name,
B.*,
row = ROW_NUMBER() OVER(PARTITION BY A.StuId ORDER BY B.SubmitTime DESC)
FROM A
JOIN B
ON A.StuId = B.StuId ) AS t WHERE t.row <11
分组取最新的记录(可用于SQL server sqlite)
select * from 表 where 日期 >='2020-07-24 05:45:14' AND 日期 <='2020-07-24 20:45:14' group by 唯一字段(比如id) HAVING ID=max(ID)--(取最大id)