hot表:gsid不重复,可以增加记录,用来设置该公司的信息被推荐
id gsid
1 123
2 234
3 345xxb表:是用户信息发布表,gsid重复出现,bz用来设置该信息被推荐
id gsid cpmc bz date
1 123 aaa 0
2 123 aaa 0
3 234 bbb 0
4 345 ccc 0
5 345 ccc 0
6 456 ddd 0
7 665 eee 1
8 666 fff 0
9 667 ggg 1
10 668 hhh 1
实现:1、取出xxb的id,gsid,cpmc,bz,date字段
2、只取5条记录,按照xxb.date排序,必须保持5条记录
3、优先取hot表中gsid的一条记录,也就是一个gsid只取一条记录
4、假如满足hot表的gsid的记录不够5条,则取xxb.bz=1的记录补足5条
id gsid
1 123
2 234
3 345xxb表:是用户信息发布表,gsid重复出现,bz用来设置该信息被推荐
id gsid cpmc bz date
1 123 aaa 0
2 123 aaa 0
3 234 bbb 0
4 345 ccc 0
5 345 ccc 0
6 456 ddd 0
7 665 eee 1
8 666 fff 0
9 667 ggg 1
10 668 hhh 1
实现:1、取出xxb的id,gsid,cpmc,bz,date字段
2、只取5条记录,按照xxb.date排序,必须保持5条记录
3、优先取hot表中gsid的一条记录,也就是一个gsid只取一条记录
4、假如满足hot表的gsid的记录不够5条,则取xxb.bz=1的记录补足5条
--建立测试环境
create table #hot (id int,gsid int)
insert #hot (id,gsid)
select '1','123' union all
select '2','234' union all
select '3','345'
go
create table #xxb(id int,gsid int,cpmc varchar(10),bz int,date datetime)
insert #xxb(id,gsid,cpmc,bz)
select '1','123','aaa','0' union all
select '2','123','aaa','0' union all
select '3','234','bbb','0' union all
select '4','345','ccc','0' union all
select '5','345','ccc','0' union all
select '6','456','ddd','0' union all
select '7','665','eee','1' union all
select '8','666','fff','0' union all
select '9','667','ggg','1' union all
select '10','668','hhh','1'
go
--执行测试语句
select top 5 x.id,x.gsid,x.cpmc,x.bz
from #xxb x
left join #hot h on h.gsid = x.gsid
where not exists(select 1 from #xxb where x.gsid = gsid and x.id > id)
and (x.bz = 1 and h.id is null or h.id is not null )
order by isnull(h.id,2147483647),x.datego
--删除测试环境
drop table #hot ,#xxb
go
/*--测试结果
id gsid cpmc bz
----------- ----------- ---------- -----------
1 123 aaa 0
3 234 bbb 0
4 345 ccc 0
10 668 hhh 1
9 667 ggg 1(5 row(s) affected)
*/
from #xxb x
left join #hot h on h.gsid = x.gsid
where not exists(select 1 from #xxb where x.gsid = gsid and x.id > id)
and (x.bz = 1 and h.id is null or h.id is not null )
order by case when h.id is null then 1 else 0 end,x.date
insert into @hot select
1,'123' union all select
2,'234' union all select
3,'345'declare @xxb table(id int,gsid int,intcpmc varchar(16),bz bit,date datetime)
insert into @xxb select
1, 123, 'aaa', 0,null union all select
2, 123, 'aaa', 0,null union all select
3, 234, 'bbb', 0,null union all select
4, 345, 'ccc', 0,null union all select
5, 345, 'ccc', 0,null union all select
6, 456, 'ddd', 0,null union all select
7, 665, 'eee', 1,null union all select
8, 666, 'fff', 0,null union all select
9, 667, 'ggg', 1,null union all select
10, 668, 'hhh', 1,null select top 5 b.* from
(select min(id) id from @xxb group by gsid) a inner join
@xxb b on a.id=b.id left join
@hot c on b.gsid=c.gsid
order by case when c.gsid is not null then 0 when b.bz=1 then 1 else 2 end,b.date
id gsid intcpmc bz date
4 345 ccc 0 NULL
3 234 bbb 0 NULL
1 123 aaa 0 NULL
7 665 eee 1 NULL
9 667 ggg 1 NULL
insert hot (id,gsid)
select '1','123' union all
select '2','234' union all
select '3','345'
go
create table xxb(id int,gsid int,cpmc varchar(10),bz int,date datetime)
insert xxb(id,gsid,cpmc,bz)
select '1','123','aaa','0' union all
select '2','123','aaa','0' union all
select '3','234','bbb','0' union all
select '4','345','ccc','0' union all
select '5','345','ccc','0' union all
select '6','456','ddd','0' union all
select '7','665','eee','1' union all
select '8','666','fff','0' union all
select '9','667','ggg','1' union all
select '10','668','hhh','1'select top 5 id,gsid,cpmc,bz,date
from(
select top 5 t1.*,tmp=1
from xxb t1
inner join
(select min(B.id) as id,B.gsid
from hot A
inner join xxb B
on A.gsid=B.gsid
group by B.gsid) t2
on t1.id=t2.idunion select top 5 *,tmp=2 from xxb
where bz=1
) t
order by tmp,date/*
id gsid cpmc bz date
------------------------------------------------------
1 123 aaa 0 NULL
3 234 bbb 0 NULL
4 345 ccc 0 NULL
7 665 eee 1 NULL
9 667 ggg 1 NULL*/