sql中逗号分隔值的问题 [英] Problem with Comma Seprated Values in sql

问题描述

Username       WorkArea      Scope      Qulaity%
aa,bb,cc        xyz           zyx        100
aa,bb,dd        xyz           zyx        99

现在我想要基于用户名的qulaity％的总和。

例如

Now i want the sum of the qulaity% based on the username.
For Example

Username      total
 aa            199
 bb            199
 dd            99
 cc            100

我想要总结每个用户的质量，但它们用逗号分隔。由于 aa 在两行中，其质量总和应为 199 。由于 cc 在单行中，他的总和应该 100 ，等等。

I want to sum the quality of each user but they are delimited by commas. As aa is in both the rows its Quality sum should be 199. As cc is in the single row his sum should 100, etc.

推荐答案

检查一下：

Check this:

DECLARE @tmp TABLE (Username VARCHAR(250), WorkArea VARCHAR(50), Scope VARCHAR(50), Quality INT)

INSERT INTO @tmp (Username, WorkArea, Scope, Quality)
VALUES('aa,bb,cc,ee,ff,gg,hh,ii', 'xyz', 'zyx', '100'),
('aa,bb,dd,ii,kk', 'xyz', 'zyx', '99'),
('cc,gg,ee,hh,kk', 'xyz', 'zyx', '77')

;WITH UserTotals AS
(
        -- initial values
        --multiple users
	SELECT LEFT(Username, CHARINDEX(',', Username)-1) AS SingleUser, Quality, RIGHT(Username, LEN(Username) - CHARINDEX(',', Username)) AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', Username)>0
      UNION ALL
        --single user only
	SELECT Username AS SingleUser, Quality, NULL AS Remainder
	FROM @tmp
	WHERE CHARINDEX(',', Username)=0
        -- here starts recursive part
	UNION ALL
	SELECT LEFT(Remainder, CHARINDEX(',', Remainder)-1) AS SingleUser, Quality, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM UserTotals
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT Remainder AS SingleUser, Quality, NULL AS Remainder
	FROM UserTotals
	WHERE CHARINDEX(',', Remainder)=0
)
SELECT SingleUser, SUM(Quality) AS Total
FROM UserTotals
GROUP BY SingleUser

结果：

Result:

aa	199
bb	199
cc	177
dd	99
ee	177
ff	100
gg	177
hh	177
ii	199
kk	176

注意：以上示例使用 CTE [ ^ ]（递归查询）将用户名拆分为单个用户名。有关详细信息，请参阅：使用公用表表达式 [ ^ ]





CTE查询已根据OP评论更新。

Note: above example uses CTE[^] (recursive query) to split Username into single user name. For further information, please see: Using Common Table Expressions[^]


CTE query has been updated according to OP comments.

不要在一列中使用不同的数据项，逗号分隔或以任何其他方式分隔。这通常会滥用关系模型和编程。



关系数据库中的所有属性都应该是原子的。换句话说，您应该将单独的元素表作为aa，bb或cc。其他一些表可称为组，另一表可称为groupMembership;它可以将元素与组相关联。然后，在您显示的表中，包括引用组的外键，而不是UserName。这样，所有查询都将在SQL中完成，而无需解析列表。这个架构可能会有所不同，具体取决于您的目标，约束等。我希望您有这个想法。

-SA

Don't use different data items in one column, comma-separated or separated in any other way. This a total abuse of relational model and programming in general.

All attributes in relational database should be atomic. In other words, you should have separate table of elements as aa, bb or cc. Some other table could be called "group", and another one could be called "groupMembership"; it could associate elements with groups. And then, in the table you show, include foreign key referencing a group, instead of "UserName". This way, all queries will be done in SQL, without parsing your lists. This schema could vary, depending on your goals, constraints, etc. I hope you got the idea.

—SA

这篇关于sql中逗号分隔值的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！