如何使用 SQL (SQL Server) 将数字列表转换为(临时)表 [英] How to convert list of numbers into (temp) table using SQL (SQL Server)

问题描述

这是一个例子；

我有数字列表 (1,5,8,36)，我希望这些值作为(临时)表行.一种方法是如下

I have list of numbers (1,5,8,36) and I want these values as a (temp) table rows. One of the way to do is as follow

select 1 as n into ##temp
union
select 5  as n 
union 
select 8 as n
union 
select 36 as n

问题是号码列表是动态的.它可以有任何值.所以我需要一种适当的系统方法将这些值转换为临时表行.

The problem is number list is dynamic . it can have any no of values. So I need a proper systematic way to convert these values into temp table rows.

推荐答案

我经常使用的解决方案...

A solution I use alot...

以 VARCHAR(MAX) 逗号分隔字符串的形式提供您的数字列表，然后使用人们在线编写的众多 dbo.fn_split() 函数之一.

Supply your list of numbers as a VARCHAR(MAX) comma delimeted string, then use one of the many dbo.fn_split() functions that people have written on line.

许多在线示例之一... SQL-User-Defined-Function-to-Parse-a-Delimited-Str

这些函数以一个字符串作为参数，并返回一个表.

These functions take a string as a parameter, and return a table.

然后你可以做这样的事情......

Then you can do things like...

INSERT INTO @temp SELECT * FROM dbo.split(@myList)

SELECT
  *
FROM
  myTable
INNER JOIN
  dbo.split(@myList) AS list
    ON list.id = myTable.id

另一种方法是查看表值参数.这些允许您将整个表作为参数传递给存储过程.如何取决于您使用的框架.您是否使用 .NET、Java、Ruby 等，您是如何与数据库通信的?

An alternative is to look into Table Valued Parameters. These allow you to pass a whole table in to a stored procedure as a parameter. How depends on the framework you're using. Are you in .NET, Java, Ruby, etc, and how are you communicating with the database?

一旦我们了解有关您的应用程序代码的更多详细信息，我们就可以向您展示用于使用表值参数的客户端代码和 SQL 存储过程模板.

Once we know more details about your applicaiton code we can show you both the client code, and the SQL stored procedure template, for using Table Valued Parameters.

这篇关于如何使用 SQL (SQL Server) 将数字列表转换为(临时)表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！