要求是在前台一页面上循环显示随机的5张图片(字段类型为uniqueidentifier)---而且是定时循环--我在 global中加入了一个定时器----间隔时间后会重新读取实现循环---改的是服务器端---
,----这个已经实现了,(也可以分组显示,这时是可以控制显示位置的)
---------但是后台有个需求:新加入一张图片时,要求在前台第一次发生循环时必须显示,既然是用:
select top 5 * from etw_info where order by newid() 这种方式了,那又如何保证必须显示呢,
------急求:大家在碰到这种需求时是如何解决的呢(个人认为目前这样的需求是矛盾的)---
------请简要列举数据库表的字段设计,及前台如何和后台进行比较好的一个中和效果!!!!
,----这个已经实现了,(也可以分组显示,这时是可以控制显示位置的)
---------但是后台有个需求:新加入一张图片时,要求在前台第一次发生循环时必须显示,既然是用:
select top 5 * from etw_info where order by newid() 这种方式了,那又如何保证必须显示呢,
------急求:大家在碰到这种需求时是如何解决的呢(个人认为目前这样的需求是矛盾的)---
------请简要列举数据库表的字段设计,及前台如何和后台进行比较好的一个中和效果!!!!
解决方案 »
- asp.net 下载后缀为.exe文件出错
- 新手求助 关于用户登录的问题
- |M| 第四贴:400分请教:我现在要给一个表做销售报表曲线图, 补分贴 400分已补完
- 请问怎样可以在关闭IE时加个确认啊?
- C#怎么读取文件?
- 免费的数据库应用工具
- 请问:aspx 接收到来自 asp from提交的string 字符串,在aspx里怎么把string 转成 UTF8变码?
- 子符格式转换问题!马上结贴!!!!
- My97DatePicker js时间控件 怎么设置初始时间 时分秒都为零
- gridview中按钮,无法激发rowcommand事件?
- 老问题:studio 2005工具箱找不到ADO.NET类对象:SqlDataAdapter ,Sqlconnection ,Sqlcommand
- Asp.Net母版页web.config配置不能用
那top 5怎么能解决啊
再有 是否可以保证每次仅添加一张?
可能要加个字段表示图片是否已经被显示了至少一次
--------------------
谢谢,我试试--
--------------------
谢谢,我试试--
写的时候要注意,取top 4时要加上不能与最新一张重复的条件
insert into etw_info select 1
insert into etw_info select 2
insert into etw_info select 3
insert into etw_info select 4
insert into etw_info select 5
insert into etw_info select 6
insert into etw_info select 7
insert into etw_info select 8
insert into etw_info select 9select * from (
select top 4 * from etw_info where id not in(select top 1 id from etw_info order by id desc) order by newid()
union all
select top 1 * from etw_info order by id desc ) tb类似这个,9总会显示,变换其他非9的4条记录,例子用的id,楼主可以用你的id或时间字段取top1
declare @time int
select @time=count(*) from recommend where datediff(s,inserttime,getdate())<4500
--print @time
if @time=1
--只有1条新添加记录时,随机取4条记录 + 1条新添加记录
begin
select top 4 * from recommend where orderlevel not in
(
select top 1 orderlevel
from recommend
order by datediff(s,inserttime,getdate())
)
order by newid()
select top 1 *
from recommend
order by datediff(s,inserttime,getdate())
end
--有2条新添加记录时,随机取3条记录 + 2条新添加记录
else if @time=2
begin
select top 3 * from recommend where orderlevel not in
(
select top 2 orderlevel from recommend
order by datediff(s,inserttime,getdate())
)
order by newid()
select top 2 *
from recommend order by datediff(s,inserttime,getdate())
end
--有3条新添加记录时,随机取2条记录 + 3条新添加记录
else if @time=3
begin
select top 2 * from recommend where orderlevel not in
(
select top 3 orderlevel from recommend
order by datediff(s,inserttime,getdate())
)
order by newid()
select top 3 *
from recommend order by datediff(s,inserttime,getdate())
end
--有4条新添加记录时,随机取1条记录 + 4条新添加记录
else if @time=4
begin
select top 1 * from recommend where orderlevel not in
(
select top 4 orderlevel from recommend
order by datediff(s,inserttime,getdate())
)
order by newid()
select top 4 *
from recommend order by datediff(s,inserttime,getdate())
end
--有5条或者大于5条新添加记录时,直接取5条新添加记录
else if @time>4
begin
select top 5 * from recommend
order by datediff(s,inserttime,getdate())
end
------------------------------------谢谢-----------
----十分感激楼上各位;以下是部分调用代码
if (ds.Tables.Count > 0)
{
for (int i = 1; i < ds.Tables.Count; i++)
{
ds.Tables[0].Merge(ds.Tables[i]);
}
Referpages.DataSource = ds.Tables[0];
Referpages.DataBind();
}
select @time=count(*) from recommend where datediff(s,inserttime,getdate()) <4500
--print @time
select * from (select top (5-@time) * from recommend where orderlevel not in
(
select top (@time) orderlevel from recommend order by datediff(s,inserttime,getdate())
) order by newid()
union all
select top (@time) * from recommend order by datediff(s,inserttime,getdate())
)tp
是sql2005吗?
select * from (
select top 5 * from recommend where orderlevel not in
(
select top 1 orderlevel from recommend order by datediff(s,inserttime,getdate())
) order by newid()
union all
select top 1 * from recommend order by datediff(s,inserttime,getdate())
)tp
------------------------
服务器: 消息 104,级别 15,状态 1,行 1
如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。
union all
select b.* from (select top 1 * from [表] order by [字段] desc) as b
其用途是看是否已经被调用过
或者可以理解为是否为新加入的
等到新加入的被调用了之后
把新加入的这个字段改成false 或者其他 证明它已经被调用过~
行不行?
declare @sql nvarchar(600)
declare @time int
declare @count int
select @time=count(*) from recommend where datediff(s,inserttime,getdate()) <10377print @time
if @time>5
begin
select top 5 * from recommend order by datediff(s,inserttime,getdate())
end
else
begin
set @count=5-@time
set @sql=('select top '+ Convert(varchar(50) ,@count) +' * from recommend
where orderlevel not in
(select top '+ Convert(varchar(50) ,@time)+' orderlevel
from recommend order by datediff
(s,inserttime,getdate())) order by newid()')exec('select top '+@time+'
* from recommend order by datediff(s,inserttime,getdate())')
exec sp_executesql @sql
end
-----------------高手帮忙优化下---现在测试可以用的---
如果语句中包含 UNION 运算符,那么 ORDER BY 子句中的项就必须出现在选择列表中。解决union后orderby问题的方法create table etw_info(id int,ty int)
insert into etw_info select 1,1
insert into etw_info select 2,1
insert into etw_info select 3,1
insert into etw_info select 4,1
insert into etw_info select 5,1
insert into etw_info select 6,1
insert into etw_info select 7,1
insert into etw_info select 8,2
insert into etw_info select 9,2declare @time int
select @time=count(*) from etw_info where ty=2select * from (select * from (select top (5-@time) * from etw_info where id not in(select top (@time) id from etw_info where ty=2) order by newid())taunion allselect * from (select top (@time) * from etw_info where ty=2 order by id)tb)tp
外面的select * from ()tp只是清楚点
-------最后sql
create proc ShowImageAgain
as
declare @sql nvarchar(600)
declare @time int
declare @count int
select @time=count(*) from recommend where datediff(s,inserttime,getdate()) <10377 --定时器间隔时间
set @count=5-@time
set @sql=('select * from (
select * from (select top '+ Convert(varchar(50) ,@count) +' * from recommend
where orderlevel not in
(select top '+ Convert(varchar(50) ,@time)+' orderlevel
from recommend order by datediff
(s,inserttime,getdate())) order by newid()) ta
union all
select * from (select top '+Convert(varchar(50),@time)+'
* from recommend order by datediff(s,inserttime,getdate()))tb)tp ')
exec sp_executesql @sql