34,575
社区成员
发帖
与我相关
我的任务
分享
select b.*
from (select *
from vip
where datediff(d,getdate(),etime)>=0) a
left join
(select top 2 *
from infor
order by istuijian desc,sysdate desc) b
on a.id = b.id
select t.* from (select a.id,a.title,a.sysdate from infor a , VIP b where a.id = b.id and a.istuijian = 1 and b.etime < getdate()) t
where sysdate in
(
select top 2 sysdate from (select a.id,a.title,a.sysdate from infor a , VIP b where a.id = b.id and a.istuijian = 1 and b.etime < getdate()) m where id=t.id order by sysdate desc
)
union all
select t.* from (select a.id,a.title,a.sysdate from infor a , VIP b where a.id = b.id and a.istuijian = 0 and b.etime < getdate()) t
where sysdate in
(
select top 2 sysdate from (select a.id,a.title,a.sysdate from infor a , VIP b where a.id = b.id and a.istuijian = 1 and b.etime < getdate()) m where id=t.id order by sysdate desc
)
select
b.*
from
[VIP] a
join
[infor] b on a.username=b.username
where
datediff(d,getdate(),a.etime)>=0
ID in(
select
top 2 ID
from [infor]
where username=b.username and istuijian=0
order by sysdate desc )
union all
select
b.*
from
[VIP] a
join
[infor] b on a.username=b.username and a.istuijian>0
where
datediff(d,getdate(),a.etime)>=0
select t.* from (select a.id,a.title,a.sysdate from infor a , VIP b where a.id = b.id and b.etime < getdate()) t
where sysdate in
(
select top 2 sysdate from (select a.id,a.title,a.sysdate from infor a , VIP b where a.id = b.id and b.etime < getdate()) m where id=t.id order by sysdate desc
)
-- 所有vip表中未到期的会员发布信息的id,title,sysdate
select id, title, sysdate into #t1 from info where 1=2
insert into #t1
select top 2 id, title, sysdate
from infor
where exists ( select 1 from vip
where etime>getdate() -- vip表中未到期的会员
and vip.username = infor.username )
and istuijian=1
order by sysdate
-- 有推荐(1条或2条都行)则显示推荐的信息
if @@rowcount=0 begin
-- 如果没有推荐信息则显示最新发布的2条信息。
insert into #t1
select top 2 id, title, sysdate
from infor
where exists ( select 1 from vip
where etime>getdate() -- vip表中未到期的会员
and vip.username = infor.username )
and istuijian=0
order by sysdate desc
end
select * from #t1
drop table #t1