34,577
社区成员
发帖
与我相关
我的任务
分享
;WITH CTE(id,内容,录入日期)
AS
(
select 1,N'a','2011.1.1' union all
select 2,N'b','2011.1.2' union all
select 3,N'a','2011.1.3' union all
select 4,N'c','2011.1.4'
)
SELECT 内容 FROM CTE GROUP BY 内容 ORDER BY MAX(录入日期) DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
;WITH CTE(id,内容,录入日期)
AS
(
select 1,N'a','2011.1.1' union all
select 2,N'b','2011.1.2' union all
select 3,N'a','2011.1.3' union all
select 4,N'c','2011.1.4'
)
SELECT 内容 FROM CTE ORDER BY CTE.录入日期 DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
;WITH CTE AS
(
SELECT 1 AS id ,'a' AS 内容,'2011-01-01' AS 录入日期 UNION ALL
SELECT 2 AS id ,'b' AS 内容,'2011-01-02' AS 录入日期 UNION ALL
SELECT 3 AS id ,'a' AS 内容,'2011-01-03' AS 录入日期 UNION ALL
SELECT 4 AS id ,'c' AS 内容,'2011-01-04' AS 录入日期
)
SELECT TOP 5 内容
FROM CTE
GROUP BY CTE.内容
ORDER BY MAX(录入日期) DESC
select top 5 t.内容
from (select 内容,
录入日期=max(录入日期)
from 表a
group by 内容) t
order by t.录入日期 desc
create table test(id int, c varchar(10), rq varchar(10))
insert into test values
(1, 'a', '2011.1.1'),
(2, 'b', '2011.1.2'),
(3, 'a', '2011.1.3'),
(4, 'c', '2011.1.4')
go
with m as (
select row_number() over(partition by c order by rq desc) rn , *
from test
)
select top 5 id, c, rq from m where rn = 1 order by rq desc
go
drop table test
go
(4 行受影响)
id c rq
----------- ---------- ----------
4 c 2011.1.4
3 a 2011.1.3
2 b 2011.1.2
(3 行受影响)