将字符串拆分为数组并在SQL Server 2014中获取数组 [英] Split string into array and get array in SQL server 2014

问题描述

嗨朋友们，



我用逗号将值存储到一个表格列中，我希望用逗号分割，并且需要在另一个表格中更新这些值。



第一列表格：

 EmpID casualLeaves SickLeaves 
 1 3 2 

第二列表格：

 EmpID LeavesBalance 
 1 1 CL，1 SL 

现在第一个表需要通过第二个表值更新输出将是

 EmpID casualLeaves SickLeaves 
 1 2 1 
如何通过sqlserver获得上述输出

我有什么试过：

 DECLARE @Leaves varchar（50）
 
 SELECT @Leaves ='1 CL ，2 SL'
 
 DECLARE @str varchar（50）
 
 SET @str ='SELECT'''+ REPLACE（@Leaves，'，'，'''， '''）+''''
 Exec @str 

解决方案

我真的建议你改变你的表格结构。您尝试将数据存储在单个列中，该列应存储为行。



桌子的正确设计类似于

员工
  -  empid 
  - 名称... 
 
离开
  -  empid 
  -  leavetype（例如1 =休闲，2 =病假）
  - 休假

现在您可以单独插入，删除和修改每个假期，而不需要在employee表中存储叶子数量，只需计算它们即可。例如

  SELECT  e.Name，
（ SELECT  COUNT（*）
  FROM 保留l 
  WHERE  l.empid = e.empid 
  AND  l.leavetype =  1 ） AS  casual，
（ SELECT  COUNT（*）
  FROM 保留l 
  WHERE  l.empid = e.empid 
  AND  l.leavetype =  2 ） AS 生病
  FROM 员工e 

嗯...作为米卡文德利斯 [ ^ ]说，你必须重新思考你的数据库结构。



平均而言，请看一下这个解决方案：

  DECLARE   @ tab1  表（EmpID  INT  ，casualLeaves  INT ，SickLeaves  INT ）
 
  INSERT   INTO   @ tab1 （EmpID，casualLeaves，SickLeaves）
  VALUES （ 1 ， 3 ， 2 ）
 
  DECLARE   @ tab2  表（EmpID  INT ，LeavesBalance  VARCHAR （ 50 ））
  INSERT   INTO   @ tab2 （EmpID，LeavesBalance）
  VALUES （ 1 ，'  1 CL，1 SL'）
 
 
;  WITH  BalanceToLeaves  AS  
（
   -   初始值 
  SELECT  EmpID， LEFT （LeavesBalance，CHARINDEX（ ' ，'，LeavesBalance）-1） AS  SingleLeave， RIGHT （LeavesBalance，LEN（LeavesBalance）-CHARINDEX（' ，'，LeavesBalance）） AS 剩余
  FROM   @ tab2  
  WHERE  CHARINDEX（ ' ，'，LeavesBalance）> 0 
   -   递归部分 
  UNION  所有 
  SELECT  EmpID， LEFT （剩余，CHARINDEX（' ，'，剩余）-1） AS  SingleLeave，正确（剩余，LEN（剩余）-CHARINDEX（' ，'，剩余）） AS 剩余
  FROM  BalanceToLeaves 
  WHERE  CHARINDEX（' ， '，剩余）> 0 
  UNION  所有 
 < span class =code-keyword> SELECT  EmpID，剩余 AS  SingleLeave， NULL  < span class =code-keyword> AS 剩余
  FROM  BalanceToLeaves 
  WHERE  CHARINDEX（' ，'，Remainder）= 0 
）
 更新 C  SET  C.casualLeaves = C.casualLeaves + B.CasualLeave，
 C. SickLeaves = C.SickLeaves + B.SickLeave 
  FROM （
  SELECT  EmpID， SUM（CasualLeave） AS  Casua lLeave，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' 那么  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） [ ^ ]

使用公用表表达式 [ ^ ]

使用公用表表达式的递归查询 [ ^ ]

使用SQL Server从SELECT更新 - Stack Overflow [ ^ ]

hi friends,

I am storing a value with comma into one table column I want to split by comma and that values need to be updated in another table.

First table columns:

EmpID casualLeaves  SickLeaves
  1        3             2

Second table columns:

EmpID  LeavesBalance
1       1 CL,1 SL

Now the first table need to update by the second table values output will be

EmpID casualLeaves  SickLeaves
  1        2            1
how to get the above output through sqlserver

What I have tried:

DECLARE @Leaves  varchar(50)

SELECT @Leaves = '1 CL,2 SL'

DECLARE @str varchar(50)

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

解决方案

I really would advice you to change the structure of your tables. You try to store data inside a single column which should be stored as rows.

The proper design for the tables would be something like

Employee
- empid
- name...

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

Now you can insert, delete and modify each leave separately and you don't need to store the amount of leaves in the employee table, you simply count them. For example

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

Well... As Mika Wendelius[^] said, you have to re-think your database structure.

In the mean-while, have a look at this solution:

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

After update @tabl contains:

EmpID	casualLeaves	SickLeaves
1		4				3

For further details, please see:
WITH common_table_expression (Transact-SQL)[^]
Using Common Table Expressions[^]
Recursive Queries Using Common Table Expressions[^]
UPDATE from SELECT using SQL Server - Stack Overflow[^]

这篇关于将字符串拆分为数组并在SQL Server 2014中获取数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！