#sql #sql-server #tsql #split
#sql #sql-server #tsql #разделение
Вопрос:
У меня очень длинная строка случайных чисел, например, 234 364 …, 632. Я хочу вставить эту строку во временную таблицу SQL, чтобы я мог использовать ее в IN (SELECT * FROM #MYTABLE)
инструкции различных запросов. Как я могу это сделать? По-видимому, нет простого способа вставить этот список в столбец в таблице. Я думал вставить его в виде строки, а затем повернуть таблицу. Любое решение, пожалуйста?
Комментарии:
1. Пожалуйста, не делайте этого; вместо этого сохраните каждое число в отдельной записи.
2. В tsql нет таких понятий, как «список» или «массив». У вас есть строка (скалярное значение — возможно, локальная переменная или параметр), содержащая числа, разделенные запятыми?
3. Вы сидите и смотрите на реляционную систему хранения данных и пытаетесь понять, как не использовать ее в качестве реляционного хранилища. Почему? Делайте то, что говорит @TimBiegeleisen.
4. Хорошо, но каково ваше предложение на самом деле? Числа в строке меняются, и я хочу легко обновить список. Затем я хочу использовать обновленный список в различных операторах в рамках одного и того же запроса. Каков наилучший подход? Очевидно, что я не могу вставить каждое число в таблицу, это создаст запрос в 4000 строк, поскольку мой список содержит 4000 чисел.
5. Используйте табличный параметр
Ответ №1:
Если вы используете достаточно последнюю версию SQL Server (2016 или более позднюю), STRING_SPLIT
может быть полезно:
CREATE TABLE #MyTempTable (RandomNumber int)
INSERT INTO #MyTempTable
SELECT
ss.Value
FROM
STRING_SPLIT('1234,5678',',') ss
SELECT
tt.RandomNumber
FROM
#MyTempTable tt
Ответ №2:
Один из способов справиться с этим — выполнить то, что я называю «индексированным разделением«.
Для этого вам понадобится таблица подсчета (она же numbers).
SET NOCOUNT ON;
USE tempdb;
GO
--==== 1. Numbers table setup
-- Create the table
IF OBJECT_ID('dbo.tally') IS NOT NULL DROP TABLE dbo.tally;
CREATE TABLE dbo.tally (N INT NOT NULL);
-- Add Primary Key (I do it here so that I can name it)
ALTER TABLE dbo.tally
ADD CONSTRAINT pk_cl_tally PRIMARY KEY CLUSTERED(N)
WITH FILLFACTOR=100;
-- Add a Unique Index (the optimizer will pick this one)
ALTER TABLE dbo.tally
ADD CONSTRAINT uq_tally UNIQUE NONCLUSTERED(N);
-- Add rows (100K should do)
INSERT dbo.tally
SELECT TOP(100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns, sys.all_columns a
Далее приведены некоторые примеры данных, которые будут использоваться для разделения.
--==== 2. Sample data
CREATE TABLE dbo.Test (ID INT, Column1 VARCHAR(20));
INSERT INTO dbo.Test (ID,Column1)
VALUES (1,'ORM;PS;SUP'),(2,'ABC;XYZ;999;123');
Теперь, когда у нас есть наши данные, давайте используем нашу таблицу подсчета для «предварительного разделения» строки:
--==== 3. Indexed view to perform the "split"
-- The view
CREATE OR ALTER VIEW dbo.TestSplit WITH SCHEMABINDING AS
SELECT
Id = t.ID,
item =
SUBSTRING
(
t.Column1,
tt.N SIGN(tt.N-1),
ISNULL(NULLIF((CHARINDEX(';',t.Column1,tt.N 1)),0),LEN(t.Column1) 1)-(tt.N)-SIGN(tt.N-1)
),
ItemIndex = tt.N 1
FROM dbo.Test AS t
CROSS JOIN dbo.tally AS tt
WHERE tt.N <= LEN(t.Column1)
AND (tt.N = 1 OR SUBSTRING(t.column1,tt.N,1) = ';');
GO
-- The index
CREATE UNIQUE CLUSTERED INDEX uq_cl__testSplit ON dbo.TestSplit(Id,Item);
GO
Теперь у вас есть строки в таблице без изменений, в то время как индексированное представление дает вам правильно нормализованное представление с вашими значениями, «предварительно разделенными» и готовыми к извлечению.
--==== 4. Test and review execution plan
SELECT sv.*
FROM dbo.TestSplit AS sv;
Результаты:
Id item ItemIndex
----------- -------------------- -----------
1 ORM 2
1 PS 5
1 SUP 8
2 123 13
2 999 9
2 ABC 2
2 XYZ 5
Обратите внимание на план выполнения:
Бережливый и чистый.