求一个sql语句:
数据库里有一个字段flag,还有一个datetime型字段
我要检索所有flag=1的数据放到最顶,然后还要datetime(注意:他的flag不等于1)最新的数据(要注意:就是BBS的最新帖子功能,但是置顶帖子放最上面),只要前40条 比如说flag=1的有10条,那么还有30条是FLAG=0的 但是flag=0的必须是最新的,而且时间要注意会有交叉比如:
表A
datetime flag
2006-10-1 0
2006-10-2 1
2006-10-3 0
2006-10-4 1
2006-10-5 1
...
2006-12-29 0
2006-12-30 0
要求选择出所有的flag=1的数据,如果不到40条,则用时间(datetime)最新的补充足够40条
选择出来的应该是这样(假设flag=1的不足40)
2006-10-5 1
2006-10-4 1
2006-10-2 1
...
2006-12-30 0
2006-12-29 0
数据库里有一个字段flag,还有一个datetime型字段
我要检索所有flag=1的数据放到最顶,然后还要datetime(注意:他的flag不等于1)最新的数据(要注意:就是BBS的最新帖子功能,但是置顶帖子放最上面),只要前40条 比如说flag=1的有10条,那么还有30条是FLAG=0的 但是flag=0的必须是最新的,而且时间要注意会有交叉比如:
表A
datetime flag
2006-10-1 0
2006-10-2 1
2006-10-3 0
2006-10-4 1
2006-10-5 1
...
2006-12-29 0
2006-12-30 0
要求选择出所有的flag=1的数据,如果不到40条,则用时间(datetime)最新的补充足够40条
选择出来的应该是这样(假设flag=1的不足40)
2006-10-5 1
2006-10-4 1
2006-10-2 1
...
2006-12-30 0
2006-12-29 0
insert into t select '2006-10-1',0
insert into t select '2006-10-2',1
insert into t select '2006-10-3',0
insert into t select '2006-10-4',1
insert into t select '2006-10-5',1
insert into t select '2006-12-29',0
insert into t select '2006-10-4',1
insert into t select '2006-10-5',1
insert into t select '2006-12-29',0
insert into t select '2006-10-4',1
insert into t select '2006-10-5',1
insert into t select '2006-12-29',0
insert into t select '2006-10-30',0
create table #t(id int identity(1,1),da datetime,flag int)declare @i int,@x int,@account int
declare @v varchar(100)
select @account=40 --这里是你定义行的总数,
select @i=count(*) from t where flag='1'
if @i<@account
begin
insert into #t(da,flag) select da,flag from t where flag='1' order by da desc
set @x=@account-@i
set @v='insert into #t(da,flag) select top '+cast(@x as varchar)+' da,flag from t where flag='+'''0'''+' order by da desc'
end
else
begin
set @v='insert into #t(da,flag) select top '+cast(@account as varchar)+' da,flag from t where flag='+'''1'''+' order by da desc'
end
exec(@v)select * from #t
drop table #t
drop table tid da flag
1 2006-10-05 00:00:00.000 1
2 2006-10-05 00:00:00.000 1
3 2006-10-05 00:00:00.000 1
4 2006-10-04 00:00:00.000 1
5 2006-10-04 00:00:00.000 1
6 2006-10-04 00:00:00.000 1
7 2006-10-02 00:00:00.000 1
8 2006-12-29 00:00:00.000 0我测试了一下没有什么问题
DECLARE @tb TABLE([datetime] datetime, [flag] int)
INSERT INTO @tb
SELECT '2006-10-1', 0
UNION ALL SELECT '2006-10-2', 1
UNION ALL SELECT '2006-10-3', 0
UNION ALL SELECT '2006-10-4', 1
UNION ALL SELECT '2006-10-5', 1
UNION ALL SELECT '2006-12-29', 0
UNION ALL SELECT '2006-12-30', 0SELECT TOP 40 [datetime], [flag] FROM @tb ORDER BY flag DESC, [datetime] DESC
下面的有问题吗?
DECLARE @tb TABLE([datetime] datetime, [flag] int)
INSERT INTO @tb
SELECT '2006-10-1', 0
UNION ALL SELECT '2006-10-2', 1
UNION ALL SELECT '2006-10-3', 0
UNION ALL SELECT '2006-10-4', 1
UNION ALL SELECT '2006-10-5', 1
UNION ALL SELECT '2006-12-29', 0
UNION ALL SELECT '2006-12-30', 0SELECT TOP 40 [datetime], [flag] FROM @tb ORDER BY flag DESC, [datetime] DESC楼上的很巧妙,兄弟我顶一下
(
select top 40 * from table where flag=1 order by datetime desc
union
select top 40 * from table order by datetime desc
)
(
select top 40 * from table where flag=1
union
select top 40 * from table order by datetime desc
)
order by flag desc,datetime desc
from(select * from table where flag=1 order by datetime desc --提取所有置頂貼
union
select top 40 * from table where flage=0 order by datetime desc)a --非置頂貼最多為40個,用top 40比不用top 40應該能提升效率