with cte as ( select *,rid = row_number() over (partition by [type] order by (case when url is null then 1 else 0 end),time) from tb )select * from cte where rid <= 3
select type ,title, url, time from (select row_number() over(partition by type order by title desc,time desc) no,* from tb) a where no<4
if object_id('[TB]') is not null drop table [TB] go create table [TB] (type int,title nvarchar(4),url nvarchar(6),time datetime) insert into [TB] select 1,'a','xxx','11:00' union all select 1,'b',null,'12:00' union all select 1,'c',null,'5:00' union all select 1,'d',null,'4:00' union all select 2,'a2',null,'6:00' union all select 2,'a3','yyy','6:45' union all select 2,'a4',null,'7:00' union all select 2,'a5',null,'8:00'select * from [TB]WITH TT AS( SELECT *,ROW_NUMBER() OVER(PARTITION BY type ORDER BY url desc,TIME asc) AS Number FROM dbo.TB) SELECT * FROM TT WHERE number <4 /* type title url time Number 1 a xxx 1900-01-01 11:00:00.000 1 1 d NULL 1900-01-01 04:00:00.000 2 1 c NULL 1900-01-01 05:00:00.000 3 2 a3 yyy 1900-01-01 06:45:00.000 1 2 a2 NULL 1900-01-01 06:00:00.000 2 2 a4 NULL 1900-01-01 07:00:00.000 3*/ ???
declare @table table (type int,title varchar(2),url varchar(3),time datetime) insert into @table select 1,'a','xxx','11:00' union all select 1,'b',null,'12:00' union all select 1,'c',null,'5:00' union all select 1,'d',null,'4:00' union all select 2,'a2',null,'6:00' union all select 2,'a3','yyy','6:45' union all select 2,'a4',null,'7:00' union all select 2,'a5',null,'8:00';with maco as( select row_number() over (partition by type order by time desc) as id, type,title,url,convert(varchar(5),time,108) as time from @table ) select type,title,url,time from maco where id<=3 order by type,url desc,time desc/* type title url time ----------- ----- ---- ----- 1 a xxx 11:00 1 b NULL 12:00 1 c NULL 05:00 2 a3 yyy 06:45 2 a5 NULL 08:00 2 a4 NULL 07:00 */
select distinct b.* from tb a cross apply (select top 3 * from tb where type=t.type order by case when url is null then 1 else 0 end)b
select * from ( select *,row_number() over( partition by type order by url desc,time desc) as rn from tb ) as t where rn <=3
WITH TT AS( SELECT *,ROW_NUMBER() OVER(PARTITION BY type ORDER BY case when url is not null then left(url,1) else 'z' end,TIME asc) AS Number FROM dbo.TB)--假设url为空的值为'z' SELECT * FROM TT WHERE number < 4try
感谢各位大侠,您给出的over (partition by type order by url desc, time desc) 里面再加个条件就可以了
with cte as
(
select *,rid = row_number() over (partition by [type] order by (case when url is null then 1 else 0 end),time)
from tb
)select *
from cte
where rid <= 3
(select row_number() over(partition by type order by title desc,time desc) no,* from tb) a
where no<4
go
create table [TB] (type int,title nvarchar(4),url nvarchar(6),time datetime)
insert into [TB]
select 1,'a','xxx','11:00' union all
select 1,'b',null,'12:00' union all
select 1,'c',null,'5:00' union all
select 1,'d',null,'4:00' union all
select 2,'a2',null,'6:00' union all
select 2,'a3','yyy','6:45' union all
select 2,'a4',null,'7:00' union all
select 2,'a5',null,'8:00'select * from [TB]WITH TT
AS(
SELECT *,ROW_NUMBER() OVER(PARTITION BY type ORDER BY url desc,TIME asc) AS Number
FROM dbo.TB)
SELECT * FROM TT WHERE number <4
/*
type title url time Number
1 a xxx 1900-01-01 11:00:00.000 1
1 d NULL 1900-01-01 04:00:00.000 2
1 c NULL 1900-01-01 05:00:00.000 3
2 a3 yyy 1900-01-01 06:45:00.000 1
2 a2 NULL 1900-01-01 06:00:00.000 2
2 a4 NULL 1900-01-01 07:00:00.000 3*/
???
declare @table table (type int,title varchar(2),url varchar(3),time datetime)
insert into @table
select 1,'a','xxx','11:00' union all
select 1,'b',null,'12:00' union all
select 1,'c',null,'5:00' union all
select 1,'d',null,'4:00' union all
select 2,'a2',null,'6:00' union all
select 2,'a3','yyy','6:45' union all
select 2,'a4',null,'7:00' union all
select 2,'a5',null,'8:00';with maco as(
select row_number() over (partition by type order by time desc) as id,
type,title,url,convert(varchar(5),time,108) as time from @table
)
select type,title,url,time from maco
where id<=3 order by type,url desc,time desc/*
type title url time
----------- ----- ---- -----
1 a xxx 11:00
1 b NULL 12:00
1 c NULL 05:00
2 a3 yyy 06:45
2 a5 NULL 08:00
2 a4 NULL 07:00
*/
distinct b.*
from
tb a
cross apply
(select top 3 * from tb where type=t.type order by case when url is null then 1 else 0 end)b
(
select *,row_number() over( partition by type order by url desc,time desc) as rn from tb
) as t where rn <=3
WITH TT
AS(
SELECT *,ROW_NUMBER() OVER(PARTITION BY type ORDER BY case when url is not null then left(url,1) else 'z' end,TIME asc) AS Number
FROM dbo.TB)--假设url为空的值为'z' SELECT * FROM TT WHERE number < 4try