SQL Server 總結(jié)復(fù)習(xí) （二）

--所有數(shù)據(jù)
SELECT * FROM dbo.student AS a INNER JOIN dbo.ScoreTB AS b ON a.Id = b.stuid
WHERE scorename = '語(yǔ)文'
-------------------------------------------
--ROW_NUMBER() 的使用 生成列從1開(kāi)始依次增加
-------------------------------------------
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 = '語(yǔ)文'
--也可以在后面再加一個(gè)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 = '語(yǔ)文' ORDER BY a.Id
--要在分組統(tǒng)計(jì)后使用排名函數(shù)，則先進(jìn)行分組，用cte或嵌套查詢表整出結(jié)果集，再用row_number函數(shù)處理
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函數(shù)想同，只是在出現(xiàn)order by同級(jí)時(shí)，排名會(huì)設(shè)置成一樣，而下一個(gè)會(huì)根據(jù)之前的記錄數(shù)生成序號(hào)
--例如前面三個(gè)是一樣的，那么都是1，下一個(gè)則是4，示例略
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--DENSE_RANK() 密集排名 用法與ROW_NUMER、RANK函數(shù)相同，只是在生成序號(hào)時(shí)是連續(xù)的，而rank函數(shù)生成的序號(hào)有可能不連續(xù)
--例如前面三個(gè)是一樣的，那么都是1，下一個(gè)則是2，示例略
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--ntile函數(shù)可以對(duì)序號(hào)進(jìn)行分組處理。這就相當(dāng)于將查詢出來(lái)的記錄集放到指定長(zhǎng)度的數(shù)組中，每一個(gè)數(shù)組元素存放一定數(shù)量的記錄。
--為每條記錄生成的序號(hào)就是這條記錄所有的數(shù)組元素的索引（從1開(kāi)始）。也可以將每一個(gè)分配記錄的數(shù)組元素稱為“桶”。
--它有一個(gè)參數(shù)，用來(lái)指定桶數(shù)，例如
----------------------------------------------------------------------------
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 = '語(yǔ)文'
--------------------------------------------------------------------------
--PARTITION BY 類似于向排名函數(shù)應(yīng)用一個(gè)group by，分組后對(duì)每一個(gè)組單獨(dú)排名
--------------------------------------------------------------------------
--統(tǒng)計(jì)各個(gè)學(xué)科的排名依次為：
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 --必須用括號(hào)括起來(lái)
SELECT TOP (@num) percent * FROM Student ORDER BY Id --只接受float并且1-100之間的數(shù)，如果傳入其他則會(huì)報(bào)錯(cuò)

View Code
--準(zhǔn)備數(shù)據(jù)
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,'語(yǔ)文',22)
INSERT INTO [scoretb] VALUES (1,'數(shù)學(xué)',32)
INSERT INTO [scoretb] VALUES (1,'外語(yǔ)',42)
INSERT INTO [scoretb] VALUES (2,'語(yǔ)文',52)
INSERT INTO [scoretb] VALUES (2,'數(shù)學(xué)',62)
INSERT INTO [scoretb] VALUES (2,'外語(yǔ)',72)
INSERT INTO [scoretb] VALUES (3,'語(yǔ)文',82)
INSERT INTO [scoretb] VALUES (3,'數(shù)學(xué)',92)
INSERT INTO [scoretb] VALUES (3,'外語(yǔ)',72)
--創(chuàng)建表值函數(shù)
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
--開(kāi)始使用
SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
CROSS APPLY [dbo].[fGetScore](A.Id) B --相當(dāng)于inner join效果
SELECT A.id,A.name,B.scorename,B.score FROM [Student] A
OUTER APPLY [dbo].[fGetScore](A.Id) B --相當(dāng)于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

DECLARE BillMsgCursor CURSOR FOR
SELECT TypeNo,TabDetailName FROM dbo.BillType
OPEN BillMsgCursor
DECLARE @TypeNo CHAR(5)
DECLARE @DetailName VARCHAR(50)
FETCH NEXT FROM BillMsgCursor INTO @TypeNo,@DetailName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DataFieldName VARCHAR(50)
DECLARE ColumnName CURSOR FOR
SELECT name FROM syscolumns WHERE id = OBJECT_ID(@DetailName)
OPEN ColumnName
FETCH NEXT FROM ColumnName INTO @DataFieldName
PRINT '單據(jù)編號(hào):' + @TypeNo
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ListDetailDataFiled.Add('''+@DataFieldName+''');'
FETCH NEXT FROM ColumnName INTO @DataFieldName
END
CLOSE ColumnName
DEALLOCATE ColumnName
FETCH NEXT FROM BillMsgCursor INTO @TypeNo,@DetailName
END
CLOSE BillMsgCursor
DEALLOCATE BillMsgCursor