復制代碼 代碼如下:

--所有數據
SELECT * FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid
WHERE scorename = '語文'
-------------------------------------------
--ROW_NUMBER() 的使用 生成列從1開始依次增加
-------------------------------------------
SELECT ROW_NUMBER() OVER (ORDER BY B.SCORE DESC) AS ROWNUMBER ,A.NAME, B.SCORE, a.Id
FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid
WHERE scorename = '語文'
--也可以在后面再加一個order by，則表示前面生成后的全部列又被以最后的列重新排列（排名列值不變）
SELECT ROW_NUMBER() OVER (ORDER BY B.SCORE DESC) AS ROWNUMBER ,A.NAME, B.SCORE, a.Id
FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid
WHERE scorename = '語文' ORDER BY a.Id
--要在分組統計后使用排名函數，則先進行分組，用cte或嵌套查詢表整出結果集，再用row_number函數處理
WITH b AS
(
SELECT stuid, SUM(score) AS score FROM ScoreTB GROUP BY stuid
)
SELECT * ,ROW_NUMBER() OVER (ORDER BY b.score desc) AS rownumer
FROM dbo.student AS a INNER JOIN b ON a.id = b.stuid
----------------------------------------------------------------------------
--RANK() 用法與ROW_NUMER函數想同，只是在出現order by同級時，排名會設置成一樣，而下一個會根據之前的記錄數生成序號
--例如前面三個是一樣的，那么都是1，下一個則是4，示例略
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--DENSE_RANK() 密集排名 用法與ROW_NUMER、RANK函數相同，只是在生成序號時是連續的，而rank函數生成的序號有可能不連續
--例如前面三個是一樣的，那么都是1，下一個則是2，示例略
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--ntile函數可以對序號進行分組處理。這就相當于將查詢出來的記錄集放到指定長度的數組中，每一個數組元素存放一定數量的記錄。
--為每條記錄生成的序號就是這條記錄所有的數組元素的索引（從1開始）。也可以將每一個分配記錄的數組元素稱為“桶”。
--它有一個參數，用來指定桶數，例如
----------------------------------------------------------------------------
SELECT ntile(2) OVER (ORDER BY B.SCORE DESC) AS GROUPID ,A.NAME, ISNULL(B.SCORE,0) SCORE, a.Id
FROM dbo.student AS a LEFT JOIN dbo.ScoreTB AS b ON a.Id = b.stuid AND scorename = '語文'
--------------------------------------------------------------------------
--PARTITION BY 類似于向排名函數應用一個group by，分組后對每一個組單獨排名
--------------------------------------------------------------------------
--統計各個學科的排名依次為：
SELECT RANK() OVER (PARTITION BY b.scorename ORDER BY B.SCORE DESC) AS ROWNUMBER,b.scorename,
b.score, A.NAME, a.Id FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid ORDER BY SCORENAME

復制代碼 代碼如下:

DECLARE @num INT = 101
SELECT TOP (@num) * FROM Student ORDER BY Id --必須用括號括起來
SELECT TOP (@num) percent * FROM Student ORDER BY Id --只接受float并且1-100之間的數，如果傳入其他則會報錯

復制代碼 代碼如下:

View Code
--準備數據
CREATE TABLE [dbo].[Student](
[Id] [int] NULL,
[Name] [varchar](50) NULL
)
go
INSERT INTO dbo.Student VALUES (1, '張三')
INSERT INTO dbo.Student VALUES (2, '李斯')
INSERT INTO dbo.Student VALUES (3, '王五')
INSERT INTO dbo.Student VALUES (4, '神人')
go
CREATE TABLE [dbo].[scoretb](
[stuId] [int] NULL,
[scorename] [varchar](50) NULL,
[score] INT NULL
)
go
INSERT INTO [scoretb] VALUES (1,'語文',22)
INSERT INTO [scoretb] VALUES (1,'數學',32)
INSERT INTO [scoretb] VALUES (1,'外語',42)
INSERT INTO [scoretb] VALUES (2,'語文',52)
INSERT INTO [scoretb] VALUES (2,'數學',62)
INSERT INTO [scoretb] VALUES (2,'外語',72)
INSERT INTO [scoretb] VALUES (3,'語文',82)
INSERT INTO [scoretb] VALUES (3,'數學',92)
INSERT INTO [scoretb] VALUES (3,'外語',72)
--創建表值函數
CREATE FUNCTION [dbo].[fGetScore](@stuid int)
RETURNS @score TABLE
(
[stuId] [int] NULL,
[scorename] [varchar](50) NULL,
[score] INT NULL
)
as
BEGIN
INSERT INTO @score
SELECT stuid,scorename,score FROM dbo.scoretb WHERE stuId = @stuid
RETURN;
END
GO
--開始使用
SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
CROSS APPLY [dbo].[fGetScore](A.Id) B --相當于inner join效果
SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
OUTER APPLY [dbo].[fGetScore](A.Id) B --相當于left join效果
--而不能這樣使用
--SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
-- INNER JOIN [dbo].[fGetScore](A.Id) B ON A.Id = B.stuid
-- SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
-- INNER JOIN (SELECT * FROM [dbo].[fGetScore](A.Id)) B ON A.Id = B.stuid

復制代碼 代碼如下: