• 售前

  • 售后

热门帖子
入门百科

天下杯料想活动的各类榜单的SQL语句小结

[复制链接]
123457886 显示全部楼层 发表于 2021-10-26 13:08:32 |阅读模式 打印 上一主题 下一主题
/*增幅降幅排名*/
复制代码 代码如下:
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

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作