--try
select * from tb
order by (select count(1) from tb t where 用户=tb.用户 and 发布日期>tb.发布日期)
, convert(varchar(10), 发布日期, 120) desc, 会员积分 desc
select * from tb
order by (select count(1) from tb t where 用户=tb.用户 and 发布日期>tb.发布日期)
, convert(varchar(10), 发布日期, 120) desc, 会员积分 desc
2008-4-18 08:20:00 100 A
2008-4-18 08:10:00 200 B
2008-4-18 08:19:00 100 A
2008-4-18 08:18:00 100 A 这条为什么排这里啊
select * from tb
order by (select count(1) from tb t where 用户=tb.用户 and 发布日期>=tb.发布日期)
, convert(varchar(10), 发布日期, 120) desc, 会员积分 desc
insert A select '2008-4-18 08:20:00', 100, 'A'
union all select '2008-4-18 08:19:00', 100, 'A'
union all select '2008-4-18 08:18:00', 100, 'A'
union all select '2008-4-18 08:16:00', 200, 'B'
union all select '2008-4-18 08:10:00', 200, 'B' select * from A
order by (select count(1) from A t where 用户=A.用户 and datediff(d, 发布日期, A.发布日期)=0 and 发布日期>=A.发布日期)
, convert(varchar(10), 发布日期, 120) desc, 会员积分 desc/*
发布日期 会员积分 用户
----------------------- ----------- --------------------------------
2008-04-18 08:16:00.000 200 B
2008-04-18 08:20:00.000 100 A
2008-04-18 08:10:00.000 200 B
2008-04-18 08:19:00.000 100 A
2008-04-18 08:18:00.000 100 A*/drop table A
create table A(发布日期 datetime, 会员积分 int, 用户 varchar(32))
insert A select '2008-4-18 08:20:00', 100, 'A'
union all select '2008-4-18 08:19:00', 100, 'A'
union all select '2008-4-18 08:18:00', 100, 'A'
union all select '2008-4-18 08:16:00', 200, 'B'
union all select '2008-4-18 08:10:00', 200, 'B' select * from A
order by convert(varchar(10), 发布日期, 120) desc
,(select count(1) from A t where 用户=A.用户 and datediff(d, 发布日期, A.发布日期)=0 and 发布日期>=A.发布日期)
,会员积分 desc/*
发布日期 会员积分 用户
----------------------- ----------- --------------------------------
2008-04-18 08:16:00.000 200 B
2008-04-18 08:20:00.000 100 A
2008-04-18 08:10:00.000 200 B
2008-04-18 08:19:00.000 100 A
2008-04-18 08:18:00.000 100 A*/drop table A
declare @t table(发布日期 datetime,会员积分 int,用户 varchar(10))
insert into @t select '2008-4-18 08:20:00',100,'A'
insert into @t select '2008-4-18 08:19:00',100,'A'
insert into @t select '2008-4-18 08:18:00',100,'A'
insert into @t select '2008-4-18 08:16:00',200,'B'
insert into @t select '2008-4-18 08:10:00',200,'B'select 发布日期,会员积分,用户 from
(select *,px=(select count(1) from @t where 用户=a.用户 and 发布日期>=a.发布日期)
from @t a)a
order by px,会员积分 desc
select * from A
order by (select count(1) from A t where 用户=A.用户 and 发布日期>=A.发布日期)
,会员积分 desc
2008-4-18 08:16:00 200 B
2008-4-18 08:20:00 100 A
2008-4-18 08:10:00 200 B
2008-4-18 08:19:00 100 A
2008-4-18 08:18:00 100 A
------------------------------------------讲讲排序规则?
会员表B (用户,会员积分)排序规则如下,按日期排序会员积分高的排在前面,但是为防止用户刷屏一家用户发布的信息只提取一条 信息表
发布日期 用户
2008-4-18 08:20:00 A
2008-4-18 08:19:00 A
2008-4-18 08:18:00 A
2008-4-18 08:16:00 B
2008-4-18 08:10:00 B 会员表用户 积 分
A 100
B 200
C 300排完序如下 发布日期 会员积分 用户
2008-4-18 08:16:00 200 B
2008-4-18 08:20:00 100 A
2008-4-18 08:10:00 200 B
2008-4-18 08:19:00 100 A
2008-4-18 08:18:00 100 A 主要是数据是几万条啊怎么样才能快一点啊
我是考虑在生成一张排好序的表可是不知道怎么现实啊
insert into @t select '2008-4-18 08:20:00','A'
insert into @t select '2008-4-18 08:19:00','A'
insert into @t select '2008-4-18 08:18:00','A'
insert into @t select '2008-4-18 08:16:00','B'
insert into @t select '2008-4-18 08:10:00','B'declare @a table (用户 varchar(10),积分 int)
insert into @a select 'A',100
insert into @a select 'B',200
insert into @a select 'C',300select x.发布日期,y.积分,y.用户 from
(select *,px=(select count(1) from @t where 用户=a.用户 and 发布日期>=a.发布日期)
from @t a)x
inner join @a y on x.用户 = y.用户
order by x.px,y.积分 desc
感觉有该索引不应该会慢。
select * from A,B WHERE A.用户 = B.用户
order by (select count(1) from A t where 用户=A.用户 and 发布日期>=A.发布日期)
,会员积分 desc
/*
create table fbb(rq datetime,yh varchar(10))
create table hyb(yh varchar(10),jf int)
create index index_fbb_yh on fbb(yh)
create index index_hyb_yh on hyb(yh)
declare @i int
set @i = 1
while @i<=30000
begin
insert hyb select 'a'+rtrim(@i),@i/100
set @i = @i+1
end
insert fbb select top 20000 getdate(),yh from hyb order by newid()
insert fbb select top 20000 getdate(),yh from hyb order by newid()
insert fbb select top 20000 getdate(),yh from hyb order by newid()
insert fbb select top 20000 getdate(),yh from hyb order by newid()
*/
declare @t1 datetime,@t2 datetime
set @t1 = getdate()
select * from fbb a,hyb b WHERE a.yh = b.yh order by (select count(1) from fbb t where yh=A.yh and rq>=a.rq),jf desc
set @t2 = getdate()
select datediff(ms,@t2,@t1)
--耗时 13720 毫秒 信息表 8万记录,会员表 3万记录