帮忙写句SQL语句

发布网友 发布时间：2022-04-11 17:57

我来回答

共5个回答

懂视网 时间：2022-04-11 22:18

/*增幅降幅排名*/
代码如下:
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

热心网友 时间：2022-04-11 19:26

呵呵，不要想的好像很复杂，其实很简单的，
select * from testtable2 order by click desc,time desc

排列顺序是先按点击率排，然后按照时间排
你要取前10是这个规律，就是取全部也是这个规律。

你可以创建虚拟数据测试下，绝对没问题。

热心网友 时间：2022-04-11 20:44

例如有张表BBS
id title time click
1 北京奥运会 2008-08-08 00:00:00 99
2 世界杯 2010-05-20 00:00:00 888
3 联合会杯 2009-06-21 00:00:00 999
4 欧冠决赛 2009-05-28 00:00:00 777
5 海天旅游 2009-05-27 00:00:00 100
6 上海世博会 2010-05-01 00:00:00 52
7 伦敦奥运会 2012-08-08 00:00:00 12
参考SQL
select top 3 *
from bbs
order by click desc
union all
select *
from bbs as a
where a.id not in
(
select top 3 id
from bbs
order by click desc
)
order by a.time

热心网友 时间：2022-04-11 22:19

select * from DATALIST where ID=(select top 10 ID from DATALIST order by chick desc) order by time desc,chick desc,chick

热心网友 时间：2022-04-12 00:10

select top 10 ,time,chick,title from 表名 group by chick desc,time desc

--不要知道对不对,你去试下.!