Billy Watsy's questions

我有一个样本数据

// 


   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

假设我们有一张桌子

CREATE TABLE [dbo].[Product](
    [ProductId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL
) 

CREATE TABLE [ProductPrice]( 
    [ProductPriceId] [int] NOT NULL,
    [ProductId] [int] NOT NULL,
    [Price] [decimal](19, 4) NOT NULL,
    [StartDate] [datetime2](7) NOT NULL,
    [EndDate] [datetime2](7) NULL
)

和样本数据

INSERT INTO ProductPrice
    ([ProductPriceId] ,[ProductId], [Price], [StartDate], [EndDate])
VALUES 
    ( 1 ,1 , 23 , '2018-12-13' , '2018-12-27'), 
    ( 2 ,1 , 26 , '2018-12-18' , '2018-12-20'), 
    ( 3 ,1 , 21 , '2018-12-10' , null), 
    ( 4 ,1 , 22 , '2018-12-28' , '2018-12-30'), 
    ( 5 ,1 , 27 , '2019-1-01' , '2019-01-18')  
;


INSERT INTO [Product]
           ([ProductId]
           ,[Name])
     VALUES
           (1 ,'Burger') ,
           (2 ,'Coke 2L') 
           ;

productID 是另一个表 Product 的外键

这张表有我们产品的价格。

问题是假设今天的日期是 2018-12-19（2018 年 12 月 19 日）

与默认日期相比，我们如何获得日期（2018-12-19）的当前价格 EndDate 不为空，价格 EndDate 将为空

问题 1 如何根据 EndDate 获得具有所需输出的价格趋势，如下所示

因此在我们的例子中，基于结束日期的最接近的日期是正确的顺序：

   -1- EndDate 2018-12-20 ( 20 Decemeber 2018 )  ProductPriceID : 2   Price : 26
   -2- EndDate 2018-12-27 ( 27 Decemeber 2018 )  ProductPriceID : 1   Price : 23
   -3- EndDate 2018-12-30 ( 30 Decemeber 2018 )  ProductPriceID : 4   Price : 22
   -4- EndDate 2019-1-01 ( 1 January 2019 )      ProductPriceID : 5   Price : 27    
   -5- EndDate NULL                              ProductPriceID : 3   Price : 21

如果范围内没有价格，则默认值为空字段的日期：因此 [EndDate NULL ProductPriceID : 3 Price : 21]

问题 2 是如何根据 EndDate 上面提到的优先级获得当前价格

当前想要的价格是：26

但是如果我这样做

 /****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [ProductPriceId]
      ,[ProductId]
      ,[Price]
      ,[StartDate]
      ,[EndDate]
  FROM [ExcelDumps].[dbo].[ProductPrice] As p
  Where  
   (
    p.[EndDate] >= '2018-12-18' 
    OR
    p.[EndDate] is null 
   )
   order by p.[EndDate] asc

我得到：

-----------------------------------------------------------------------
ProductPriceId  ProductId   Price   StartDate   EndDate
--------------------------------------------------------------------------
3               1           21.0000 2018-12-10  NULL
2               1           26.0000 2018-12-18  2018-12-19 
1               1           23.0000 2018-12-13  2018-12-27
4               1           22.0000 2018-12-28  2018-12-30
5               1           27.0000 2019-01-01  2019-01-18

但是 null 应该在最后

问题3：以当前价格检索整个产品列表：

我试过了什么

/****** Script for SelectTopNRows command from SSMS  ******/

;with ctaCurrentPrice As
(
  -- tried gettign current price for product 
    SELECT *
  FROM [ExcelDumps].[dbo].[ProductPrice] As p
  Where  
   ( 
    p.[EndDate] >= '2018-12-18' 
    OR
    p.[EndDate] is null 
   ) 
)
Select * , 
    (
      Select Top 1 Price From  ctaCurrentPrice where ProductId = pro.[ProductId]
    ) As Price ,
    (
     Select Top 1 StartDate From  ctaCurrentPrice where ProductId = pro.[ProductId]
    ) As StartDate,
    (
     Select Top 1 EndDate From  ctaCurrentPrice where ProductId = pro.[ProductId]
    ) As EndDate
 From Product As pro

得到什么

ProductId 名称 价格 开始日期 结束日期

1       Burger  23.0000 2018-12-13  2018-12-27
2       Coke 2L NULL    NULL        NULL

其中想要的是

ProductId 名称 价格 开始日期 结束日期

1       Burger  26.0000 2018-12-18  2018-12-19 
2       Coke 2L NULL    NULL        NULL

编辑 2：

我找到了解决方案希望

SELECT Top 1 *
  FROM [ExcelDumps].[dbo].[ProductPrice] As p
  Where  
    p.[ProductId] = 1
    AND
   ( 
    p.[EndDate] >= '2018-12-18' 
    OR
    p.[EndDate] is null 
   ) 
   order by case when p.[EndDate] is null then 2 else 1 end, p.[EndDate]  asc 

我有以下数据样本

CREATE TABLE IF NOT EXISTS `tbl_result` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` varchar(10) NOT NULL,
  `subject_id` int(5) NOT NULL,
  `score` int(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;


INSERT INTO `tbl_result` (`id`, `student_id`, `subject_id`, `score`) VALUES
(3, 'R135722F', 7112, 43),
(4, 'R135722F', 6118, 55),
(5, 'R135722F', 2076, 45),
(6, 'R135722F', 4402, 76),
(7, 'R135722F', 2234, 34),
(8, 'R134567Y', 6118, 65),
(9, 'R134567Y', 2076, 87),
(10, 'R134567Y', 4402, 43),
(11, 'R134567Y', 2234, 65),
(12, 'R134567Y', 1111, 34),
(13, 'R134567Y', 3454, 65),
(14, 'R134567Y', 3456, 87),
(15, 'R137634H', 7112, 98),
(16, 'R137634H', 6118, 54),
(17, 'R137634H', 2076, 45),
(18, 'R137634H', 4402, 33),
(19, 'R137634H', 2234, 65),
(20, 'R137634H', 2234, 65),
(21, 'R137634H', 1111, 12),
(22, 'R137634H', 3454, 54);

我如何检索以下有多少学生通过了以下科目即

科目代码7112、6118等3科或科目及格者 科目代码1111、3454等
4科及以上者

我曾尝试使用 in 函数，但没有产生预期的结果

    SELECT COUNT( * ) 
FROM  `tbl_result` 
WHERE  `subject_id` 
IN (
 '7112',  '6118'
)

和以下但失败了。也试过这个

SELECT * 
FROM  `tbl_result` 
WHERE  `subject_id` =  '7112'
OR  `subject_id` =  '6118'
AND  `score` >49
GROUP BY  `student_id