在这种情况下，如何按顺序和按组排序？
我需要选择前3体育基于TotalUsers的DESC，并按个人体育分组。
到现在为止我所做的是
SELECT *
FROM (
SELECT R.Sports, R.RoomID ,R.Name,
COUNT(C.ChatUserLogId) AS TotalUsers,
ROW_NUMBER()
OVER (PARTITION BY R.SPORTS ORDER BY R.SPORTS DESC ) AS Rank
FROM Room R JOIN ChatUserLog C
ON R.RoomID = C.RoomId
GROUP BY
R.RoomID,
R.Name,
R.Sports
) rs WHERE Rank IN (1, 2, 3)
ORDER BY Sports, TotalUsers DESC下面是SQL的输出
Sports RoomID Name TotalUsers Rank
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aerobics 6670 Aerobic vs. Anaerobic Exercise: Which is Best to Burn more Fat? 17 1
Aerobics 7922 Is it okay to be fat if you’re fit? 13 2
Aerobics 6669 What is the best time of the day to do an aerobic work out? 7 3
Archery 7924 Who were the best archers in history? 8 1
Archery 7925 Should I get into shooting or archery? 7 2
Archery 7926 What advantages, if any, do arrows have over bullets? 6 3
Badminton 6678 Which is more challenging, physically and mentally: badminton or tennis? 9 1
Badminton 6677 Who is your favorite - Lee chong wei or Lin dan? 8 2
Badminton 6794 Which single athlete most changed the sport? 7 3
Billiards 6691 How to get great at billiards? 34 1
Billiards 6692 Why is Efren Reyes the greatest billiards and pool player of all time? 31 2
Boxing 6697 Mike Tyson: The greatest heavyweight of all time? 13 1
Boxing 6700 Who is considered the greatest boxer of all time? Why? 13 2
Boxing 6699 What is the greatest, most exciting boxing fight of all-time? 12 3但是我的查询不能解决我的需求。我需要输出如下所示。下面的输出选择TotalUsers并根据体育对它们进行分组。
Sports TotalUsers
-----------------------
Billiards 34
Billiards 31
Aerobics 17
Aerobics 13
Aerobics 7
Boxing 13
Boxing 13
Boxing 12任何帮助都是非常感谢的。
回答 3
Stack Overflow用户
发布于 2017-01-15 16:41:18
您的代码看起来非常接近，但似乎有三个问题。
Over条款
您的OVER子句中似乎有一个错误：
ROW_NUMBER() OVER(PARTITION BY R.SPORTS ORDER BY R.SPORTS DESC)PARTITION BY语句在重新启动每个分区的排序时是正确的。但是，在每个分区中，您是按照分区条件排序的，这是不确定的(对于分区中的每个值，R.SPORTS必然是相等的，因此ORDER不会产生任何影响)。相反，您想要的是由总用户订购。声明的内容如下：
ROW_NUMBER() OVER(PARTITION BY R.SPORTS ORDER BY COUNT(C.CHATUSERLOGID) DESC)(您还可以使用RANK()代替ROW_NUMBER，如果您想要拥有相同数量的用户以获得相同的排名。)
最终查询排序
该问题表明您正在按以下顺序排列结果集：
- 第一，根据体育；运动应该按该类别中最大的房间来订购。
- 第二，每项运动的前3间房间按大小顺序排列。
第一个标准要求在您的内部选择语句中添加一个新列:对于每个房间，该运动的任何房间的用户人数最多的是什么？这可以写成：
MAX(COUNT(C.CHATUSERLOGID)) OVER (PARTITION BY R.SPORTS) MaxSportsUsers有了该列，您可以按MaxSportsUsers降序，然后按等级升序进行排序。
仅限于前三名:出现了一个问题
问题解决方案表明，您只想要前三项运动，按其顶部房间的用户数进行排名。因此，您需要对表单进行排序：
RANK() OVER (PARTITION BY CATEGORY ORDER BY MAX(COUNT(USERID)) OVER (PARTITION BY CATEGORY)) CategoryTop但是 Server不支持这个，并且尝试它会引发错误“在另一个窗口函数或聚合的上下文中不能使用加窗口的函数”。
有几种选择。请注意，如果我们针对内部查询(rs)运行SELECT TOP 3 SPORT, MAX(TotalUsers) MaxUsers FROM RS ORDER BY 2 DESC，则查询将产生前三项运动和最高的用户计数。加入这些记录的RS体育将限制最后输出的前三名。
此方法要求从内部联接引用RS。为此，必须将嵌套查询(SELECT * FROM (SELECT...) rs)转换为公共表表达式表单(WITH RS AS (SELECT...) SELECT * FROM RS)。这允许对表单WITH RS AS (SELECT...) SELECT * FROM RS JOIN (SELECT... FROM RS) R2...进行查询。
一旦查询采用CTE格式，我们就可以加入CTE查询，即INNER JOIN (SELECT TOP 3 SPORT, MAX(TOTALUSERS) MaxSportsUsers FROM RS GROUP BY SPORT ORDER BY 2 DESC) RS2 ON RS2.SPORT = RS.SPORT)，保持ORDER BY子句不变。内部连接将将最终数据集限制在前3项运动中。
将MaxSportsUsers列移动到内部联接后，可以从RS (以前的内部查询)中删除它。
最终查询
结合上面的内容，我们得到了最后的查询：
WITH RS AS
(
SELECT R.Sports, R.RoomID ,R.Name,
COUNT(C.ChatUserLogId) AS TotalUsers,
ROW_NUMBER() OVER (PARTITION BY R.SPORTS ORDER BY COUNT(C.ChatUserLogId) DESC ) AS Rank
FROM Room R
JOIN ChatUserLog C ON R.RoomID = C.RoomId
GROUP BY R.RoomID, R.Name, R.Sports
)
SELECT rs.Sports, rs.TotalUsers
FROM rs
INNER JOIN (
SELECT TOP 3 SPORTS, MAX(TOTALUSERS) MaxSportsUsers FROM RS GROUP BY SPORTS ORDER BY 2 DESC
) RS2 ON RS2.SPORTS = RS.SPORTS
WHERE Rank IN (1, 2, 3)
ORDER BY MaxSportsUsers DESC, RANK;Stack Overflow用户
发布于 2017-01-15 13:38:29
从所需数据的描述中，您似乎只想从子查询中选择两列：
SELECT rs.Sports, rs.TotalUsers
FROM (SELECT R.Sports, R.RoomID ,R.Name,
COUNT(C.ChatUserLogId) AS TotalUsers,
ROW_NUMBER() OVER (PARTITION BY R.SPORTS ORDER BY R.SPORTS DESC ) AS Rank
FROM Room R JOIN
ChatUserLog C
ON R.RoomID = C.RoomId
GROUP BY R.RoomID, R.Name, R.Sports
) rs
WHERE Rank IN (1, 2, 3)
ORDER BY Sports, TotalUsers DESC;唯一的更改是外部查询选择所需的两列。
Stack Overflow用户
发布于 2017-01-15 14:16:37
如果你想要前3名，先从前3开始。如下所示：
with top3Sports as (
select top 3 sports, count(chatUserLogId) users
from room r join chatUserLog c on r.roomId = c.roomId
group by sports
order by count(chatUserLogId) desc
)
select the fields you need
from top3Sports join other tables etc这比你尝试过的方法简单得多。然而，请记住，无论你采取什么方法，领带都会把你搞砸。
https://stackoverflow.com/questions/41661666
复制相似问题
腾讯云开发者
