sqlserver数组_如何在SQLServer中实现类似数组的功能sql server 数组
介绍 (Introduction)
I was training some Oracle DBAs in T-SQL and they asked me how to create arrays in SQL Server.
我在T-SQL中培训了⼀些Oracle DBA，他们问我如何在SQL Server中创建阵列。
I told them that there were no arrays in SQL Server like the ones that we have in Oracle (). They were disappointed and asked me how was this problem handled.
我告诉他们，SQL Server中没有像Oracle（ ）中那样的数组。 他们感到失望，问我该问题如何处理。
Some developers asked me the same thing. Where are the arrays in SQL Server?
⼀些开发⼈员问我同样的事情。 SQL Server中的数组在哪⾥？
The short answer is that we use temporary tables or TVPs (Table-valued parameters) instead of arrays or we use other functions to replace the used of arrays.
简短的答案是我们使⽤临时表或TVP（表值参数）⽽不是数组，或者使⽤其他函数来替换数组。
The use of temporary tables, TVPs and table variables is explained in another article:
另⼀篇⽂章中说明了临时表，TVP和表变量的使⽤：
In this article, we will show:
在本⽂中，我们将显⽰：
How to use a table variable instead of an array
如何使⽤表变量⽽不是数组
The function STRING_SPLIT function which will help us to replace the array functionality
函数STRING_SPLIT函数将帮助我们替换阵列功能
How to work with older versions of SQL Server to handle a list of values separated by commas
如何使⽤旧版本SQL Server处理以逗号分隔的值列表
Requirements
要求
1. SQL Server 2016 or later with SSMS installed
安装了SSMSSQL Server 2016或更⾼版本
2. The Adventureworks database installed
已安装Adventureworks数据库
⼊门 (Getting started)
How to use a table variable instead of an array
如何使⽤表变量⽽不是数组
In the first demo, we will show how to use a table variable instead of an array.
在第⼀个演⽰中，我们将展⽰如何使⽤表变量⽽不是数组。
We will create a table variable using T-SQL:
我们将使⽤T-SQL创建⼀个表变量：
DECLARE @myTableVariable TABLE (id INT, name varchar(20))
insert into @myTableVariable values(1,'Roberto'),(2,'Gail'),(3,'Dylan')
select * from @myTableVariable
We created a table variable named myTableVariable and we inserted 3 rows and then we did a select in the table variable.
我们创建了⼀个名为myTableVariable的表变量，并插⼊了3⾏，然后在该表变量中进⾏了选择。
The select will show the following values:
选择将显⽰以下值：
Now, we will show information of the table Person.person of the adventureworks database that match with the nameS of the table variable:
现在，我们将显⽰与表变量的nameS匹配的Adventureworks数据库的表Person.person的信息：
DECLARE @myTableVariable TABLE (id INT, name varchar(20))
insert into @myTableVariable values(1,'Roberto'),(2,'Gail'),(3,'Dylan')
SELECT  [BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
FROM [Adventureworks].[Person].[Person] where
FirstName
IN (Select name from @myTableVariable)
The results will display the names and information of the table Person.person with the names of Roberto, Gail and Dylan:
结果将显⽰表Person.person的名称和信息，表名为Roberto，Gail和Dylan：
Note that in SQL Server, it is better to use SQL sentences to compare values. It is more efficient. We do not use loops (WHILE) in general because it is slower and it is not efficient.
请注意，在SQL Server中，最好使⽤SQL语句⽐较值。 效率更⾼。 通常，我们不使⽤循环（WHILE），因为它速度较慢且效率不⾼。
You can use the id to retrieve values from a specific row. For example, for Roberto, the id is 1 for Dylan the id is 3 and for Gail the id is 2.
您可以使⽤ID从特定⾏中检索值。 例如，对于Roberto，ID为1，对于Dylan，ID为3，对于Gail，ID为2。
In C# for example if you want to list the second member of an array, you should run something like this:
例如，在C＃中，如果要列出数组的第⼆个成员，则应运⾏以下命令：
Array[1];
You use the brackets and the number 1 displays the second number of the array (the first one is 0).
使⽤⽅括号，数字1显⽰数组的第⼆个数字（第⼀个为0）。
In a table variable, you can use the id. If you want to list the second member (id=2) of the table variable, you can do something like this:
在表变量中，可以使⽤id。 如果要列出表变量的第⼆个成员（id = 2），则可以执⾏以下操作：
DECLARE @myTableVariable TABLE (id INT, name varchar(20))
insert into @myTableVariable values(1,'Roberto'),(2,'Gail'),(3,'Dylan')
select * from @myTableVariable where id=2
In other words, you can use the id to get a specific member of the table variable.
换句话说，您可以使⽤id获取表变量的特定成员。
The problem with table variables is that you need to insert values and it requires more code to have a simple table with few rows.
表变量的问题在于，您需要插⼊值，并且需要更多的代码才能创建具有少量⾏的简单表。
In C# for example, to create an array, you only need to write the elements and you do not need to insert data into the table:
例如，在C＃中，要创建⼀个数组，只需编写元素，⽽⽆需在表中插⼊数据：
string[] names = new string[] {"Gail","Roberto","Dylan"};
It is just a single line of code to have the array with elements. Can we do something similar in SQL Server?
使数组包含元素只是⼀⾏代码。 我们可以在SQL Server中做类似的事情吗？
The next solution will help us determine this
下⼀个解决⽅案将帮助我们确定这⼀点
The function STRING_SPLIT function
函数STRING_SPLIT
Another solution is to replace arrays with the use of the new function STRING_SPLIT. This function is applicable in SQL Server 2016 or later versions and applicable in Azure SQL.
另⼀种解决⽅案是使⽤新功能STRING_SPLIT替换阵列。 此功能适⽤于SQL Server 2016或更⾼版本，适⽤于Azure SQL。
If you use the function in an old adventureworks database or in SQL Server 2014 or older, you may receive an error message. The following example will try to split 3 names separated by commas:
如果在旧的Adventureworks数据库或SQL Server 2014或更早版本中使⽤该功能，则可能会收到错误消息。 以下⽰例将尝试分割3个名称，并⽤逗号分隔：
SELECT value FROM STRING_SPLIT('Roberto,Gail,Dylan', ',');
A typical error message would be the following:
典型的错误消息如下：sql自学难吗
Msg 208, Level 16, State 1, Line 8
Invalid object name ‘STRING_SPLIT’
消息208，第16级，状态1，第8⾏
⽆效的对象名称“ STRING_SPLIT”
If you receive this error in SQL Server 2016, check your database compatibility level:
如果在SQL Server 2016中收到此错误，请检查数据库兼容性级别：
SELECT compatibility_level
FROM sys.databases WHERE name = 'AdventureWorks';
GO
If your compatibility level is lower than 130, use this T-SQL sentence to change the compatibility level:
如果您的兼容性级别低于130，请使⽤以下T-SQL语句更改兼容性级别：
ALTER DATABASE [Adventureworks] SET COMPATIBILITY_LEVEL = 130
If you do not like T-SQL, you can right click the database in SSMS and go to options and change the compatibility level:
如果您不喜欢T-SQL，则可以右键单击SSMS中的数据库，然后转到选项并更改兼容性级别：
The T-SQL sentence will convert the values separated by commas in rows:
T-SQL语句将转换⽤逗号分隔的⾏中的值：
SELECT value FROM STRING_SPLIT('Roberto,Gail,Dylan', ',');
The values will be converted to rows:
这些值将转换为⾏：
In the STRING_SPLIT function, you need to specify the separator.
在STRING_SPLIT函数中，您需要指定分隔符。
The following query will show the information of people in the person.person table that matches the names used in the STRING_SPLIT function:
以下查询将在person.person表中显⽰与STRING_SPLIT函数中使⽤的名称匹配的⼈员信息：
SELECT  [BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
FROM [Adventureworks].[Person].[Person] where
FirstName
IN (SELECT value FROM STRING_SPLIT('Roberto,Gail,Dylan', ','));
The query will show information about the people with the names equal to Roberto or Gail or Dylan:
该查询将显⽰有关姓名等于Roberto或Gail或Dylan的⼈员的信息：
If you want to retrieve a specific member of the string, you can assign a row # to each member row of the STRING_SPLIT. The following code shows how retrieve the information
如果要检索字符串的特定成员，可以将⾏＃分配给STRING_SPLIT的每个成员⾏。 以下代码显⽰了如何检索信息
WITH fakearray AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY value DESC) AS ID,value FROM STRING_SPLIT('Roberto,Gail,Dylan', ',')
)
SELECT ID, value
FROM fakearray
WHERE ID =3
ROW_NUMBER is used to add an id to each name. For example, Roberto has the id =1, Gail id=2 and Dylan 3.
ROW_NUMBER⽤于为每个名称添加⼀个ID。 例如，Roberto的ID为= 1，盖尔的ID为2，⽽Dylan为3。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。