SQL Server - 使用聚集索引时如何存储数据页

Question

Billy Watsy

Asked: 2019-03-02 02:42:52 +0800 CST2019-03-02 02:42:52 +0800 CST 2019-03-02 02:42:52 +0800 CST

根据sql server中某个主题的学生分数的每行获取排名

772

我有一个样本数据

// 


   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

1 个回答

Voted

Akina · Answer 1 · 2019-03-02T03:13:53+08:00

Best Answer

Akina

2019-03-02T03:13:53+08:002019-03-02T03:13:53+08:00

尝试类似的东西

WITH cte AS (
SELECT student_id, subject, score, 
CAST(RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS CHAR(3))
    + ' out of ' +
    CAST(COUNT(student_id) OVER (PARTITION BY subject) AS CHAR(3)) AS [rank]
FROM table
-- ORDER BY student, subject
)
SELECT * /* subject, score, rank */
FROM cte
WHERE student_id = 'R135722F'

3

根据sql server中某个主题的学生分数的每行获取排名

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

根据sql server中某个主题的学生分数的每行获取排名

1 个回答

相关问题