男孩车祸心脏破裂,暴雨天碰到这个要赶紧逃命!,珀尚
/*增幅降幅排名*/
select top 50 username,sum(receiveprice) - sum(guessprice) as receiveprice,
cast(sum(case when receiveprice>0 then 1.0 else 0 end) / count(receiveprice) * 100 as numeric(4,1)) as rate
from [game_fantasylog]
where isjudge=1
group by userid,username
order by sum(receiveprice) - sum(guessprice) asc
/*正确率错误率排名*/
select top 50 username,sum(receiveprice) - sum(guessprice) as receiveprice,
cast(sum(case when receiveprice>0 then 1.0 else 0 end) / count(receiveprice) * 100 as numeric(4,1)) as rate
from [game_fantasylog]
where isjudge=1
group by userid,username having count(userid) >= 5
order by cast(sum(case when receiveprice>0 then 1.0 else 0 end) / count(receiveprice) * 100 as numeric(4,1)) asc
/*大手笔排名*/
select top 50 l.username,sum(l.guessprice),sum(l.receiveprice),f.title
from [game_fantasylog] l left join [game_fantasy] f on l.topicid = f.id
group by l.topicid,l.username,f.title
order by sum(l.guessprice) desc
/*冷门场次排名*/
select top 50 f.id,f.title,f.guessprice,(select sum(receiveprice) from [game_fantasylog] l where l.topicid = f.id),
cast((select sum(case when receiveprice>0 then 1.0 else 0 end) / f.guesstimes from [game_fantasylog] l2 where l2.topicid = f.id) as numeric(4,2))
from [game_fantasy] f where f.guessprice > 1000
order by (select sum(receiveprice) from [game_fantasylog] l where l.topicid = f.id) asc
/*冷门场次的命中者*/
select top 50 username,sum(receiveprice) as receiveprice
from [game_fantasylog] where topicid=29
group by topicid,username
order by sum(receiveprice) desc
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!!
点击进行留言回复
相关文章:
-
-
sql某个日期是当年的第几周
/* *周一作为一周的开始 *当年的1月1号所在的周算作第一周 */ CREATE function GetWeekIndexFirstDate ( @...
[阅读全文]
-
-
数据库SQL---范式
1、数据冗余导致的问题:冗余存储、更新异常、插入异常、删除异常。 2、函数依赖:一种完整性约束。 在关系模式r(R)中,α属于R,β属于R。 1)α函数...
[阅读全文]
-
-
数据库SQL---查询
1、查询所有列 select *from emp;--*表示所有的,from emp表示从emp表中查询。 2、查询指定列 select empno,e...
[阅读全文]
-
-
-
-
-
网友评论