求SQL语句

[lovebeyondforever]

要求

表diary
1.选出top 20 人
2.根据最近30天发布日记的数量排行 datediff(d,diary_timed,getdate())<30
3.要求显示每个人的日记数量
4.显示姓名 userid
5.显示标题 title

现在等 ..马上揭帖!

select xx from diary where del=0 and datediff(d,updatetime,getdate())<20 and hf_id=0 其他不会写了...
谢谢

[itblog]

select top 20 userid,title,count(title) as 日记数量
from diary
where del=0 and datediff(d,updatetime,getdate())<20 and hf_id=0
group by userid,title

[lovebeyondforever]

速度果然快 谢谢

但是有重复的人...

20人里面不能有重复的 谢谢

[itblog]

select distinct top 20 userid,title,count(title) as 日记数量
from diary
where del=0 and datediff(d,updatetime,getdate())<20 and hf_id=0
group by userid,title



or
select top 20 * from
(
select distinct userid,title,count(title) as 日记数量
from diary
where del=0 and datediff(d,updatetime,getdate())<20 and hf_id=0
group by userid,title
) b

[LouisXIV]

--try

select distinct userid,title,数量
from diary a
inner join
(
select top 20
userid,
(select count(userid) from diary where datediff(d,diary_timed,getdate())<30) group by userid) as 数量
from diary
order by 数量
desc
)b
on a.userid=b.userid

[LouisXIV]

--改一下，没有数据测试就是不爽

select distinct userid,title,数量
from diary a
inner join
(
select top 20
userid,
(select count(title) from diary where datediff(d,diary_timed,getdate())<30) group by userid) as 数量
from diary
order by 数量
desc
)b
on a.userid=b.userid

[LouisXIV]

-_-括号贴错

select distinct userid,title,数量
from diary a
inner join
(
select top 20
userid,
(select count(title) from diary where datediff(d,diary_timed,getdate())<30 group by userid) as 数量
from diary c
order by 数量 desc
)b
on a.userid=b.userid

[lovebeyondforever]

谢谢
to itblog(BegCSharp) ( ) 信誉：100 你的还是有重复的姓名
to LouisXIV(夜游神 也有错误我正在改

如果调试好
开100分 感谢2位

[lovebeyondforever]

to LouisXIV(夜游神) ( ) 信誉：100

执行效率太低 执行时间太久了. 不过还是要感谢你

[itblog]

select top 20 userid,title,count(title) as 日记数量
from
(
select * from diary a where not exists(select 1 from diary where userid=a.userid and title=a.title and updatetime>a.updatetime)
)
b
where del=0 and datediff(d,updatetime,getdate())<20 and hf_id=0
group by userid,title

[itblog]

不知道你说的重复是怎么个重复~

[lovebeyondforever]

还是有重复.

闹心啊..

主要是没有测试环境 谢谢大家

[itblog]

这样呢？

select top 20 userid,title,count(distinct title) as 日记数量
from diary
where del=0 and datediff(d,updatetime,getdate())<20 and hf_id=0
group by userid,title

[lovebeyondforever]

是姓名重复
aini美丽
我不信
钢琴家
helenawhy
rrrnnn
网上情缘
gdgzapplelwp
gdgzapplelwp
gdgzapplelwp
xshm
amy788
怀旧
怀旧
华哥
雪落
嫣一
aini美丽
无烟
怀旧
爱我

[itblog]

不对啊，你这里也没有涉及到姓名字段的，你能贴出几条完整的记录吗？

[lovebeyondforever]

userid 就是姓名
忘了加一个diary_id 就是日记id

userid title 数量
我不信 一二三四五 1

[笨笨兔兔兔兔兔]

一句sql不难,但是没有效率,这个应该是一天或几次的吧?

可以参考我写的blog

http://blog.csdn.net/liuxiaoyi666/archive/2006/05/10/722837.aspx

[itblog]

select a.userid,a.title,b.日记数量
from diary a inner
(
select top 20 userid,日记数量=count(title) from diary where del=0 and datediff(d,updatetime,getdate())<20 and hf_id=0 group by userid
)
b
on a.userid=b.userid

[k_arches]

要求

表diary
1.选出top 20 人
2.根据最近30天发布日记的数量排行 datediff(d,diary_timed,getdate())<30
3.要求显示每个人的日记数量
4.显示姓名 userid
5.显示标题 title

重复记录的关键是 要显示:title