SQL Server 总结复习 （二）

这篇文章主要为大家详细介绍了SQL Server 总结复习 （二），具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

 
--所有数据 
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 

代码如下:

 
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 '单据编号:' + @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 

注：关于SQL Server 总结复习 （二）的内容就先介绍到这里，更多相关文章的可以留意