100高分求sql

Spring源码解析 2012-07-11 07:00:54
数据库 mysql

表pic(pic_is,user_id,comment_num,praise_num,browser_num,hot_status)

hot_status 为图片状态(1,2,3),其中3表示是热图。


我想查出用户id(去重)列表,并且是按这个计算权重的倒序排序,
计算公式:comment_num*2+praise_num+browser_num*0.2+热图数*3

就是说按某个用户的所有图片的评论总数*2加上他的所有图片的赞数,加上他的所有图片的浏览数*0.2,再加上他的所有热图数*3,按这个计算出来的值倒序排序……

不知道我说的明白不。

在线,等高手回答,,,,,

跪求……
...全文
117 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
cangzhu 2012-07-19
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 的回复:]

select userid,sum(comment_num)*2+sum(praise_num)+sum(browser_num)*0.2+
sum(if(hot_status=3,1,0))*3 as quanzhong
from tb
group by user_id order by 2 desc
[/Quote]

sum(if(hot_status=3,1,0))*3
,就是了~~~
wwwwb 2012-07-12
  • 打赏
  • 举报
回复
select userid,sum(comment_num)*2+sum(praise_num)+sum(browser_num)*0.2+
sum(if(hot_status=3,1,0))*3 as quanzhong
from tb
group by user_id order by 2 desc
过去的我 2012-07-12
  • 打赏
  • 举报
回复
热图数要另外统计的吧。。
Rotel-刘志东 2012-07-12
  • 打赏
  • 举报
回复
SELECT  userid,SUM(comment_num)*2+SUM(praise_num)+SUM(browser_num)*0.2+SUM(热图数)*3 as quanzhong
FROM tb
GROUP BY user_id ORDER BY 2 DESC;
wwwwb 2012-07-12
  • 打赏
  • 举报
回复
贴建表及插入记录的SQL,及要求结果出来看看


select userid,sum(comment_num)*2+sum(praise_num)+sum(browser_num)*0.2+sum(热图数)*3 as quanzhong
from tb
group by user_id order by 2 desc
syd0308 2012-07-11
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 的回复:]

SQL code
select *
from (
select userid,sum(comment_num)*2+sum(praise_num)+sum(browser_num)*0.2+sum(热图数)*3 as quanzhong
from tb
group by user_id
) D
order by quanzhong desc
limit 10;
[/Quote]
是不是
sum(热图数) = select count(*) from TB where user_id = "xxx" ?
ACMAIN_CHM 2012-07-11
  • 打赏
  • 举报
回复
select user_id, sum(comment_num*2+praise_num+browser_num*0.2+热图数*3)
form pic
group by user_id
order by 2 desc
rucypli 2012-07-11
  • 打赏
  • 举报
回复
select *
from (
select userid,sum(comment_num)*2+sum(praise_num)+sum(browser_num)*0.2+sum(热图数)*3 as quanzhong
from tb
group by user_id
) D
order by quanzhong desc
limit 10;

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧