主页 / dba / 问题

问题[rank](dba)

Martin Hope
PrJ
Asked: 2019-04-11 02:13:05 +0800 CST
  • 0

我有一张桌子作为 table1

 id| hits | time
-------+--------+------
 1 | 9    |    40.89
 2 | 5    |    33.21
 3 | 5    |    78.31
 4 | 1    |    71.93
 5 | 4    |    23.10
 6 | 4    |    99.99
 7 | 8    |    12.32
 8 | 7    |    32.64

我想使用命中对值进行排名，如果 2 行具有相同数量的命中，我想使用时间对它们进行排名 那是时间最短的具有更高的排名 这是预期的结果


  id| hits | time     | rank 
-------+--------+--------------
 1 | 9    |    40.89  |  1
 2 | 5    |    33.21  |  4
 3 | 5    |    78.31  |  5
 4 | 1    |    71.93  |  8
 5 | 4    |    23.10  |  6
 6 | 4    |    99.99  |  7
 7 | 8    |    12.32  |  2
 8 | 7    |    32.64  |  3

任何帮助表示赞赏

mysql rank
Martin Hope
Billy Watsy
Asked: 2019-03-02 02:42:52 +0800 CST
  • 1

我有一个样本数据

// 


   USE [master]
GO
/****** Object:  Database [dbTest]    Script Date: 2019/03/01 12:20:40 ******/
CREATE DATABASE [dbTest]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'dbTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\dbTest.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'dbTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\dbTest_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [dbTest] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [dbTest].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [dbTest] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [dbTest] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [dbTest] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [dbTest] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [dbTest] SET ARITHABORT OFF 
GO
ALTER DATABASE [dbTest] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [dbTest] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [dbTest] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [dbTest] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [dbTest] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [dbTest] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [dbTest] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [dbTest] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [dbTest] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [dbTest] SET  DISABLE_BROKER 
GO
ALTER DATABASE [dbTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [dbTest] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [dbTest] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [dbTest] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [dbTest] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [dbTest] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [dbTest] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [dbTest] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [dbTest] SET  MULTI_USER 
GO
ALTER DATABASE [dbTest] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [dbTest] SET DB_CHAINING OFF 
GO
ALTER DATABASE [dbTest] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [dbTest] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [dbTest] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [dbTest] SET QUERY_STORE = OFF
GO
USE [dbTest]
GO
/****** Object:  Table [dbo].[tbl_result]    Script Date: 2019/03/01 12:20:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_result](
    [id] [int] NOT NULL,
    [student_id] [varchar](10) NOT NULL,
    [subject_id] [int] NOT NULL,
    [score] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (3, N'R135722F', 7112, 43)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (4, N'R135722F', 6118, 55)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (5, N'R135722F', 2076, 45)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (6, N'R135722F', 4402, 76)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (7, N'R135722F', 2234, 34)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (8, N'R134567Y', 6118, 65)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (9, N'R134567Y', 2076, 87)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (10, N'R134567Y', 4402, 43)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (11, N'R134567Y', 2234, 65)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (12, N'R134567Y', 1111, 34)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (13, N'R134567Y', 3454, 65)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (14, N'R134567Y', 3456, 87)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (15, N'R137634H', 7112, 98)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (16, N'R137634H', 6118, 54)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (17, N'R137634H', 2076, 45)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (18, N'R137634H', 4402, 33)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (19, N'R137634H', 2234, 65)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (20, N'R137634H', 2234, 65)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (21, N'R137634H', 1111, 12)
INSERT [dbo].[tbl_result] ([id], [student_id], [subject_id], [score]) VALUES (22, N'R137634H', 3454, 54)
USE [master]
GO
ALTER DATABASE [dbTest] SET  READ_WRITE 
GO

//

我想通过 student_id 获取所有科目并根据科目排名

例如，一个学生写了主题 7112 我想知道该学生对该主题的排名以及有多少人写了该主题

示例 1。学科代码7112排名

在此处输入图像描述

示例 2。6118学科排名

在此处输入图像描述

示例 3。学科排名2076

在此处输入图像描述

注意：一个学生可以写一个科目，但可以写很多不同的科目，但不能每个科目写两次

我想要实现的是根据学生 ID 获取科目并获得每个科目的排名，就像获取特定学生报告时一样

在此处输入图像描述

或者

在此处输入图像描述

我试过的 

SELECT stu.* , 
(
SELECT TOP 1 + 
DENSE_RANK () OVER (PARTITION BY [tbl_result].score order by  
                [tbl_result].score DESC ) as ranking
  FROM
[dbo].[tbl_result]
Where [dbo].[tbl_result].[student_id] = stu.[student_id]
) As Rank 
FROM [dbo].[tbl_result] stu
Where stu.[student_id] = 'R135722F'

我也假设/认为

我可以使用游标，但我对他们来说仍然是新手，并且不希望由于知识较少而编写糟糕的 sql 来降低性能

为什么会得到错误的结果

如果我对所有学生的科目进行排名，我得到的排名是

SELECT *  ,
    DENSE_RANK () OVER (ORDER BY [tbl_result].score  DESC ) as ranking
      FROM
    [dbo].[tbl_result] 
    Where subject_id = 7112

但是当获得学生和他/她用他们的等级写的科目时我没有做

Edit 1 Example 3 rank 最后两行的 rame rank of 2 for score 45 在此处输入图像描述

sql-server rank
Martin Hope
broc.seib
Asked: 2018-02-12 07:56:33 +0800 CST
  • 0

我继承了一个db，需要将一个已有的父子关系转化为所有的parents。

让我先解释一下现有数据。这些表的结构类似于：

Thing                  Widget
------------           ------------
thing_id               widget_id
thing_name             widget_name
thing_rank             widget_rank
                       thing_id

数据以 s 列表的形式呈现给最终用户Things，偶尔也会以Thinghas-a 的形式呈现给最终用户Widget，并且小部件呈现为 的“下方”或“子” Thing。所以有一些等级制度。另一个关键是这个演示文稿是按thing_rank和排序的widget_rank。

这是一个具体的数据示例：

事物：

1001  "thing blue" 1
1002  "thing green" 3
1003  "thing red" 4
1004  "thing yellow" 2

小部件：

2001  "widget cucumber" 2  1002
2002  "widget bean" 1  1002

这将与Thing按等级排序的 s 一起呈现，然后两个孩子Widgets 属于“绿色事物”，并按等级排序：

1 thing blue
2 thing yellow
3 thing green
   1 widget bean
   2 widget cucumber
4 thing red

任务是删除层次结构，但保留等级顺序。例如，上述数据将被分配一个新的等级，所有数据将属于同一个关系——不再是父子关系。例如：

1 thing blue
2 thing yellow
3 thing green
4 widget bean
5 widget cucumber
6 thing red

单一关系是：

Stuff
-------------
stuff_id
stuff_type   (thing|widget)
stuff_name
stuff_rank

我正在寻找如何使用新的排名分配创建一个同时显示和数据的Stuff视图。ThingWidget

view rank
Martin Hope
Alw
Asked: 2017-08-08 16:40:32 +0800 CST
  • 1

我有下表，称为sales

+--------+--------+
| fruit  | amount |
+--------+--------+
| cherry |    124 |
| plum   |     23 |
| pear   |     19 |
| apple  |     13 |
| banana |      4 |
| orange |      2 |
+--------+--------+

并用它来获得排名

SET @rank=0; 
SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC;

.

+------+--------+--------+
| rank | fruit  | amount |
+------+--------+--------+
|    1 | cherry |    124 |
|    2 | plum   |     23 |
|    3 | pear   |     19 |
|    4 | apple  |     13 |
|    5 | banana |      4 |
|    6 | orange |      2 |
+------+--------+--------+

所以现在我想获得的排名，apple但我不知道该怎么做。

mysql rank
Martin Hope
Martin Lawrence
Asked: 2017-02-22 19:34:22 +0800 CST
  • 2

我的数据库中有以下信息：

+-----------------------+-------------+
| USERNAME        TRUST |             |
+-----------------------+-------------+
| DeaDTerra             |         390 |
| OgNasty               |         322 |
| Blazed                |         303 |
| monbux                |         260 |
| Tomatocage            |         230 |
| Stunna                |         220 |
| philipma1957          |         216 |
| John (John K.)        |         180 |
| CanaryInTheMine       |         173 |
| Mitchell              |         165 |
+-----------------------+-------------+

我想运行一个将返回以下内容的查询：

+----------------------+
| USERNAME        RANK |
+----------------------+
| Blazed          3    |
+----------------------+

我尝试了以下查询：

select top 25 username, trust, RANK() over (order by trust desc) AS Rank
from btcProfile 
where profile_name='Blazed' order by trust

我得到的是：

+----------------------+
| USERNAME        RANK |
+----------------------+
| Blazed          1    |
+----------------------+

如何只提取一条记录但根据RANK()表中的所有行计算？

sql-server rank
Martin Hope
DBeck
Asked: 2016-07-18 04:48:07 +0800 CST
  • 0

所以我想做的是根据学生的加权平均值对学生进行排名，加权平均值将通过根据类型（无论是“考试”还是“非考试”）分离成绩来计算。然后将两者的平均值相加得到总数将用于对学生进行排名。

我使用了下面的查询，但它没有使用正确的平均值，因为它没有正确地对类型进行分组。例如..它应该得到所有非考试成绩的平均值，它还包括考试成绩：

SELECT * FROM 
(SELECT student_id, avg, exam,(exam) + (avg) as total, (@rn := @rn + 1) AS rank
FROM (SELECT student_id, 
((SELECT AVG(grade) where grades.type != 'exam')/100 * 40 ) as avg, 
((SELECT AVG(grade) where type = 'exam')/100 * 60 ) as exam FROM grades 
WHERE class_id = 1 AND subject_id = 1 GROUP BY student_id ) agg 
CROSS JOIN (SELECT @rn := 0) CONST ORDER BY total desc) t

见下表截图： 在此处输入图像描述

请参阅下面的查询以创建表：

表的表结构grades

CREATE TABLE `grades` (
  `grade_id` int(11) NOT NULL,
  `assessment_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `grade` double NOT NULL,
  `teacher_comment` text,
  `type` enum('Home Work','Class','Unit Test','Assignment','Exam') DEFAULT NULL,
  `term` enum('1','2','3') DEFAULT NULL,
  `sch_year` year(4) NOT NULL,
  `entered_by` int(11) DEFAULT NULL,
  `enter_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `grades`
--

INSERT INTO `grades` (`grade_id`, `assessment_id`, `subject_id`, `student_id`, `class_id`, `grade`, `teacher_comment`, `type`, `term`, `sch_year`, `entered_by`, `enter_date`) VALUES
(1, 1, 3, 10001, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:04:38'),
(5, 1, 3, 10001, 2, 80, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:12:17'),
(6, 1, 3, 10002, 2, 60, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:12:17'),
(7, 1, 3, 10001, 2, 80, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'),
(8, 1, 3, 10002, 2, 75, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'),
(9, 1, 3, 10003, 2, 95, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:13:50'),
(10, 1, 3, 10001, 2, 85, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:53'),
(11, 1, 3, 10002, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:53'),
(12, 1, 3, 10003, 2, 90, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:22:54'),
(13, 1, 3, 10001, 2, 98, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'),
(14, 1, 3, 10002, 2, 95, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'),
(15, 1, 3, 10003, 2, 90, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:23:44'),
(16, 1, 3, 10001, 2, 96, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'),
(17, 1, 3, 10002, 2, 88, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'),
(18, 1, 3, 10003, 2, 86, NULL, 'Home Work', '3', 2015, NULL, '2016-06-21 02:24:42'),
(22, 3, 4, 10001, 2, 55, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'),
(23, 3, 4, 10002, 2, 75, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'),
(24, 3, 4, 10003, 2, 85, NULL, 'Class', '3', 2015, NULL, '2016-06-21 02:27:47'),
(25, 4, 3, 10001, 2, 85, NULL, 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'),
(26, 4, 3, 10002, 2, 90, 'Test', 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'),
(27, 4, 3, 10003, 2, 65, NULL, 'Unit Test', '3', 2015, NULL, '2016-06-21 17:41:05'),
(28, 5, 3, 10001, 2, 96, NULL, 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'),
(29, 5, 3, 10002, 2, 90, 'Test', 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'),
(30, 5, 3, 10003, 2, 98, NULL, 'Assignment', '3', 2015, NULL, '2016-06-22 19:10:34'),
(31, 6, 2, 10001, 2, 55, NULL, 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'),
(32, 6, 2, 10002, 2, 65, 'Test', 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'),
(33, 6, 2, 10003, 2, 60, NULL, 'Exam', '3', 2015, NULL, '2016-06-29 15:04:47'),
(34, 10, 3, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'),
(35, 10, 3, 10002, 2, 85, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'),
(36, 10, 3, 10003, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:53:34'),
(37, 9, 1, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'),
(38, 9, 1, 10002, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'),
(39, 9, 1, 10003, 2, 93, NULL, 'Exam', '3', 2015, NULL, '2016-06-30 15:55:20'),
(40, 12, 2, 10001, 2, 95, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'),
(41, 12, 2, 10002, 2, 96, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'),
(42, 12, 2, 10003, 2, 90, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 15:56:34'),
(43, 11, 3, 10001, 2, 85, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'),
(44, 11, 3, 10002, 2, 88, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'),
(45, 11, 3, 10003, 2, 92, NULL, 'Exam', '3', 2015, NULL, '2016-07-11 16:00:22'),
(46, 13, 4, 10001, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(47, 13, 4, 10002, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(48, 13, 4, 10003, 2, 52, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(49, 13, 4, 10004, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(50, 13, 4, 10005, 2, 74, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(51, 13, 4, 10006, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(52, 13, 4, 10007, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:53'),
(53, 13, 4, 10008, 2, 74, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(54, 13, 4, 10009, 2, 58, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(55, 13, 4, 10010, 2, 97, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(56, 13, 4, 10011, 2, 88, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(57, 13, 4, 10024, 2, 96, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(58, 13, 4, 10012, 2, 88, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(59, 13, 4, 10013, 2, 89, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(60, 13, 4, 10014, 2, 54, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(61, 13, 4, 10015, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(62, 13, 4, 10016, 2, 86, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(63, 13, 4, 10017, 2, 57, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(64, 13, 4, 10018, 2, 89, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(65, 13, 4, 10019, 2, 93, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(66, 13, 4, 10020, 2, 99, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(67, 13, 4, 10021, 2, 72, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(68, 13, 4, 10022, 2, 100, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(69, 13, 4, 10023, 2, 85, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(70, 13, 4, 10025, 2, 95, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(71, 13, 4, 10026, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(72, 13, 4, 10027, 2, 75, NULL, 'Unit Test', '1', 0000, NULL, '2016-07-15 22:35:54'),
(73, 14, 5, 10001, 2, 85, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(74, 14, 5, 10002, 2, 53, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(75, 14, 5, 10003, 2, 21, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(76, 14, 5, 10004, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(77, 14, 5, 10005, 2, 96, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(78, 14, 5, 10006, 2, 55, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(79, 14, 5, 10007, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(80, 14, 5, 10008, 2, 89, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(81, 14, 5, 10009, 2, 93, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(82, 14, 5, 10010, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(83, 14, 5, 10011, 2, 57, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(84, 14, 5, 10024, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(85, 14, 5, 10012, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(86, 14, 5, 10013, 2, 80, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(87, 14, 5, 10014, 2, 97, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(88, 14, 5, 10015, 2, 95, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(89, 14, 5, 10016, 2, 45, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(90, 14, 5, 10017, 2, 77, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(91, 14, 5, 10018, 2, 88, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(92, 14, 5, 10019, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(93, 14, 5, 10020, 2, 87, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(94, 14, 5, 10021, 2, 57, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:47'),
(95, 14, 5, 10022, 2, 85, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(96, 14, 5, 10023, 2, 75, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(97, 14, 5, 10025, 2, 82, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(98, 14, 5, 10026, 2, 78, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(99, 14, 5, 10027, 2, 99, NULL, 'Class', '1', 0000, NULL, '2016-07-15 22:42:48'),
(100, 15, 5, 10001, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(101, 15, 5, 10002, 2, 90, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(102, 15, 5, 10003, 2, 99, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(103, 15, 5, 10004, 2, 55, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(104, 15, 5, 10005, 2, 85, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(105, 15, 5, 10006, 2, 87, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(106, 15, 5, 10007, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(107, 15, 5, 10008, 2, 58, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(108, 15, 5, 10009, 2, 98, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(109, 15, 5, 10010, 2, 75, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(110, 15, 5, 10011, 2, 77, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(111, 15, 5, 10024, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(112, 15, 5, 10012, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(113, 15, 5, 10013, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(114, 15, 5, 10014, 2, 79, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(115, 15, 5, 10015, 2, 98, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:28'),
(116, 15, 5, 10016, 2, 83, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(117, 15, 5, 10017, 2, 80, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(118, 15, 5, 10018, 2, 84, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(119, 15, 5, 10019, 2, 86, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(120, 15, 5, 10020, 2, 89, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(121, 15, 5, 10021, 2, 78, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(122, 15, 5, 10022, 2, 89, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(123, 15, 5, 10023, 2, 87, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(124, 15, 5, 10025, 2, 88, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(125, 15, 5, 10026, 2, 99, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(126, 15, 5, 10027, 2, 95, NULL, 'Exam', '1', 0000, NULL, '2016-07-15 22:43:29'),
(127, 16, 1, 10028, 1, 85, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'),
(128, 16, 1, 10029, 1, 88, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'),
(129, 16, 1, 10030, 1, 89, NULL, 'Home Work', '1', 0000, NULL, '2016-07-15 23:36:33'),
(130, 17, 1, 10028, 1, 90, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18'),
(131, 17, 1, 10029, 1, 95, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18'),
(132, 17, 1, 10030, 1, 96, NULL, 'Class', '1', 0000, NULL, '2016-07-16 04:19:18');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `grades`
--
ALTER TABLE `grades`
  ADD PRIMARY KEY (`grade_id`),
  ADD KEY `subject_id` (`subject_id`),
  ADD KEY `student_id` (`student_id`),
  ADD KEY `class_id` (`class_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `grades`
--
ALTER TABLE `grades`
  MODIFY `grade_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=133;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `grades`
--
ALTER TABLE `grades`
  ADD CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
mysql rank
Martin Hope
Josh Foskett
Asked: 2016-01-26 13:52:33 +0800 CST
  • 1

表模式：

servers
- id
- user_id
- game_id
- slug
  ...

server_votes
- id
- server_id
- ip_address
- created_at
  ...

所以，我有两个表：servers和server_votes，我想根据每个服务器在一定时间内收到的票数来获得每个服务器的排名。我创建了以下查询来做到这一点（除了排名部分）：

SELECT 
    s.id,
    s.user_id,
    s.slug,
    (
        SELECT 
            COUNT(sv.id)
        FROM
            server_votes AS sv
        WHERE
            DATE(sv.created_at) > '2016-01-24 19:11:15'
        AND 
            sv.server_id = s.id
    ) AS votes
FROM
    servers AS s
LEFT JOIN
    server_votes AS sv 
ON 
    sv.server_id = s.id
WHERE 
    s.game_id = 1
GROUP BY
    s.id
ORDER BY 
    votes DESC,
    s.id ASC

上面的查询将返回以下内容：

*----*---------*---------*-------*
| id | user_id | slug    | votes |
*----*---------*---------*-------*
| 4  | 1       | Server4 | 3     |
| 2  | 1       | Server2 | 2     |
| 1  | 1       | Server1 | 0     |
| 3  | 2       | Server3 | 0     |
| 5  | 2       | Server5 | 0     |
*----*---------*---------*-------*

我想以以下内容结束：

*----*---------*---------*-------*------*
| id | user_id | slug    | votes | rank |
*----*---------*---------*-------*------*
| 4  | 1       | Server4 | 3     | 1    |
| 2  | 1       | Server2 | 2     | 2    |
| 1  | 1       | Server1 | 0     | 3    |
| 3  | 2       | Server3 | 0     | 4    |
| 5  | 2       | Server5 | 0     | 5    |
*----*---------*---------*-------*------*

或者，如果我正在查询特定用户的服务器，我会在所有服务器的上下文中获得排名：

*----*---------*---------*-------*------*
| id | user_id | slug    | votes | rank |
*----*---------*---------*-------*------*
| 3  | 2       | Server3 | 0     | 4    |
| 5  | 2       | Server5 | 0     | 5    |
*----*---------*---------*-------*------*

任何见解将不胜感激。

此外，这是对您有任何帮助的SQL Fiddle incase。

mysql rank
Martin Hope
Taylor Clark
Asked: 2015-11-30 14:47:56 +0800 CST
  • 2

我想弄清楚如何正确显示排名，如果多个作者的文章数量相同，那么他们会获得相同的排名，然后排名会在正确的位置恢复。例如，如果前 4 人的文章数相同，那么我希望他们都排名为 1，然后第 5 个人排名为 5。以下查询输出如下所示： 

SELECT @rownum:=@rownum + 1 as Rank, 
   subq1.*
FROM ( SELECT Uni.University_Name, COUNT( * ) AS ArticleCount
FROM Author_University AS AU, University AS Uni, Articles AS Art
WHERE Art.Year_Published >= '2010'
AND Uni.University_ID = AU.University_ID
AND Art.Article_ID = AU.Article_ID
GROUP BY University_Name
ORDER BY ArticleCount DESC
LIMIT 0 , 25 
  ) subq1,
(SELECT @rownum := 0) r

在此处输入图像描述

mysql rank
Martin Hope
Dannymh
Asked: 2015-01-27 06:45:11 +0800 CST
  • 1

我正在编写一个基于用户投票的 MVP 系统。桌子看起来像

投票 | 游戏 | 年
---------- ------ ------
玩家 1 | 2 | 2015年
玩家 1 | 2 | 2015年
玩家 2 | 2 | 2015年
玩家 2 | 2 | 2015年
玩家 2 | 2 | 2015年
玩家 3 | 2 | 2015年
玩家 1 | 2 | 2015年
玩家 3 | 2 | 2015年
玩家 4 | 2 | 2015年

此处的条目等同于投票。因此，我们将分组并计算出

玩家 1 = 3
玩家 2 = 3
玩家 3 = 2
玩家 4 = 1

MVP系统是一个“3-2-1”的评分系统。因此，积分以相反的顺序授予玩家。所以它应该只将获胜分数应用于前 3 个位置。IE

第 1 名得 3 分
第二名得2分
第三名得1分

当出现平局时，这会变得非常复杂，但前 3 名应该获得 MVP 积分。

在上述情况下，点差将是

玩家 1 = 2.5 分 玩家 2 = 2.5 分 玩家 3 = 1 分

这是因为前 2 名玩家实际上是第 1 名和第 2 名，没有第二名，所以下一个人获得第 3 名得 1 分。

有效（点数 = points_available / players_in_rank）

然而，这个等式是错误的。如果我们获得并列第 3 名，情况会变得更加复杂，最终可能会有很多人排在第 3 名，因此他们得到 1 分。

当你在第一或第二有领带时，这真的很复杂。我在 PHP 代码中解决这个问题很糟糕，但希望能够在 SQL 中执行此操作并取消 PHP。

只是想知道是否有人对此有任何简单的方法？

然后将这些分数添加到总体排名表中

问候丹

mysql rank