prasanna204 Ответов: 3

Разделить строку на массив и получить массив в SQL server 2014

привет друзья,

Я храню значение с запятой в одном столбце таблицы, который я хочу разделить на запятую, и эти значения должны быть обновлены в другой таблице.

Первые столбцы таблицы:
EmpID casualLeaves  SickLeaves
  1        3             2


Столбцы второй таблицы:
EmpID  LeavesBalance
1       1 CL,1 SL


Теперь первую таблицу нужно обновить ПО второй таблице значений выходных данных будет
EmpID casualLeaves  SickLeaves
  1        2            1
how to get the above output through sqlserver


Что я уже пробовал:

DECLARE @Leaves  varchar(50)

SELECT @Leaves = '1 CL,2 SL'

DECLARE @str varchar(50)

SET @str = 'SELECT ''' + REPLACE(@Leaves,',',''',''') + ''''
Exec @str

3 Ответов

Рейтинг:
2

Wendelius

Я бы действительно посоветовал вам изменить структуру ваших таблиц. Вы пытаетесь хранить данные внутри одного столбца, который должен храниться в виде строк.

Правильный дизайн для столов будет выглядеть примерно так

Employee
- empid
- name...

leave
- empid
- leavetype (e.g. 1=casual, 2 = sick leave)
- leavedate

Теперь вы можете вставлять, удалять и изменять каждый отпуск отдельно, и вам не нужно хранить количество отпусков в таблице сотрудников, вы просто их подсчитываете. Например
SELECT e.Name,
       (SELECT COUNT(*)
        FROM leave l
        WHERE l.empid = e.empid
        AND l.leavetype = 1) AS casual,
       (SELECT COUNT(*)
        FROM leave l
        WHERE l.empid = e.empid
        AND l.leavetype = 2) AS sick
FROM Employee e

prasanna204

окей Мика, но мне нужно разделить 2-й столбец таблицы LeavesBalance на запятую, и я хочу, чтобы разница между ними была такой..3-1 для causual и 2-1 для больничного листа результат обновляется в первой таблице.

Рейтинг:
2

tcsekhar

Или вы можете сделать это просто, как это:


-- Here is the String Array you want to convert to a Table
declare @StringArray varchar(max)
set @StringArray = 'First item,Second item,Third item';

-- Here is the table which is going to contain the rows of each item in the String array
declare @@mytable table (EachItem varchar(50))

-- Just create a select statement appending UNION ALL to each one of the item in the array
set @StringArray = 'select ''' + replace(@StringArray, ',', ''' union all select ''') + ''''
-- Push the data into your table
insert into @@mytable exec (@StringArray)

-- You now have the data in an an array inside a table that you can join to other objects
select * from @@mytable

Richard Deeming

Очень плохая идея. Если значение, которое вы разделяете, каким-либо образом контролируется пользователем, вы только что открыли себя для SQL-инъекция[^] уязвимость.

Если вы используете SQL Server 2016 или более позднюю версию, используйте STRING_SPLIT[^] функция. В противном случае используйте одну из многочисленных реализаций, доступных для более ранних версий SQL, ни одна из которых не содержит критической уязвимости безопасности во имя "простоты".

Рейтинг:
13

Maciej Los

Ну... Как Мика Венделиус[^] сказал, что вы должны переосмыслить структуру своей базы данных.

А пока взгляните на это решение:

DECLARE @tab1 TABLE(EmpID INT, casualLeaves INT, SickLeaves INT)

INSERT INTO @tab1 (EmpID, casualLeaves , SickLeaves)
VALUES(1, 3, 2)

DECLARE @tab2 TABLE(EmpID INT, LeavesBalance VARCHAR(50))
INSERT INTO @tab2 (EmpID,  LeavesBalance)
VALUES(1, '1 CL,1 SL')


;WITH BalanceToLeaves AS
(
	--initial value
	SELECT EmpID, LEFT(LeavesBalance, CHARINDEX(',', LeavesBalance)-1) AS SingleLeave, RIGHT(LeavesBalance, LEN(LeavesBalance) -CHARINDEX(',', LeavesBalance)) AS Remainder
	FROM @tab2
	WHERE CHARINDEX(',', LeavesBalance)>0
	--recursive part
	UNION ALL
	SELECT EmpID, LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SingleLeave, RIGHT(Remainder, LEN(Remainder) -CHARINDEX(',', Remainder)) AS Remainder
	FROM BalanceToLeaves
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT EmpID, Remainder AS SingleLeave, NULL AS Remainder
	FROM BalanceToLeaves
	WHERE CHARINDEX(',', Remainder)=0
)
UPDATE C SET C.casualLeaves = C.casualLeaves + B.CasualLeave, 
	C.SickLeaves  = C.SickLeaves  + B.SickLeave 
FROM (
	SELECT EmpID, SUM(CasualLeave) AS CasualLeave, SUM(SickLeave) AS SickLeave
	FROM (
		SELECT EmpID, SingleLeave, CASE WHEN SingleLeave LIKE '%CL' THEN CONVERT(INT, LEFT(SingleLeave, CHARINDEX(' ', SingleLeave)-1)) END AS CasualLeave,
				CASE WHEN SingleLeave LIKE '%SL' THEN CONVERT(INT, LEFT(SingleLeave, CHARINDEX(' ', SingleLeave)-1)) END AS SickLeave
		FROM BalanceToLeaves) AS A
	GROUP BY EmpID ) AS B INNER JOIN @tab1 AS C ON B.EmpID = C.EmpID


После обновления @tabl содержит:
EmpID	casualLeaves	SickLeaves
1		4				3


Для получения более подробной информации, пожалуйста, смотрите:
С common_table_expression (Transact-SQL)[^]
Использование Общих Табличных Выражений[^]
Рекурсивные Запросы, Использующие Обобщенные Табличные Выражения[^]
Обновление от SELECT с помощью SQL Server - переполнение стека[^]

prasanna204

Спасибо, Мацей.Мне нужно различать значения.

Maciej Los

Что ж... Есть ли какие-то проблемы с заменой [+] с [-] в UPDATE заявление?
Пожалуйста, примите мой ответ как решение (зеленая кнопка) - формально, чтобы удалить ваш вопрос из списка без ответа.
Твое Здоровье, Мацей

prasanna204

Нет проблем, Мацей.Спасибо

Maciej Los

Всегда пожалуйста.