hot表：gsid不重复,可以增加记录，用来设置该公司的信息被推荐
id gsid
1 123
2 234
3 345
xxb表：是用户信息发布表，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条
------解决方案--------------------
declare @hot table(id int,gsid int)
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