SQL 一列中有多个值的SQL查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2800104/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Query with multiple values in one column
提问by lp1
I've been beating my head on the desk trying to figure this one out. I have a table that stores job information, and reasons for a job not being completed. The reasons are numeric,01,02,03,etc. You can have two reasons for a pending job. If you select two reasons, they are stored in the same column, separated by a comma. This is an example from the JOBIDtable:
我一直在桌子上敲我的头，试图弄清楚这一点。我有一个表格，用于存储工作信息以及工作未完成的原因。原因有数字、01、02、03等。待处理工作可能有两个原因。如果您选择两个原因，它们将存储在同一列中，用逗号分隔。这是JOBID表中的一个示例：
Job_Number User_Assigned PendingInfo
1 user1 01,02
There is another table named Pending, that stores what those values actually represent. 01=Not enough info, 02=Not enough time, 03=Waiting Review. Example:
还有另一个名为Pending 的表，用于存储这些值实际代表的内容。01=信息不足，02=时间不够，03=等待审核。例子：
Pending_Num PendingWord
01 Not Enough Info
02 Not Enough Time
What I'm trying to do is query the database to give me all the job numbers, users, pendinginfo, and pending reason. I can break out the first value, but can't figure out how to do the second. What my limited skills have so far:
我想要做的是查询数据库以提供所有工作编号、用户、待处理信息和待处理原因。我可以打破第一个值，但无法弄清楚如何做第二个。到目前为止，我的技能有限：
select Job_number,user_assigned,SUBSTRING(pendinginfo,0,3),pendingword
from jobid,pending
where
SUBSTRING(pendinginfo,0,3)=pending.pending_num and
pendinginfo!='00,00' and
pendinginfo!='NULL'
What I would like to see for this example would be:
对于这个例子，我想看到的是：
Job_Number User_Assigned PendingInfo PendingWord PendingInfo PendingWord
1 User1 01 Not Enough Info 02 Not Enough Time
Thanks in advance
提前致谢
回答by paxdiablo
You really shouldn'tstore multiple items in one column if your SQL is ever going to want to process them individually. The "SQL gymnastics" you have to perform in those cases are both ugly hacks and performance degraders.
如果您的 SQL 想要单独处理它们，您真的不应该在一列中存储多个项目。在这些情况下，您必须执行的“SQL 体操”既是丑陋的黑客攻击，又是性能下降器。
The ideal solution is to split the individual items into separate columns and, for 3NF, move those columns to a separate table as rows if you reallywant to do it properly (but baby steps are probably okay if you're sure there will never be more than two reasons in the short-medium term).
理想的解决方案是将单个项目拆分为单独的列，对于 3NF，如果您真的想正确地将这些列作为行移动到单独的表中（但如果您确定永远不会有小步骤可能没问题）中短期原因不止两个）。
Then your queries will be both simpler and faster.
那么您的查询将既简单又快捷。
However, if that's not an option, you can use the afore-mentioned SQL gymnastics to do something like:
但是，如果这不是一个选项，您可以使用上述 SQL 体操来执行以下操作：
where find ( ',' |fld| ',', ',02,' ) > 0
assuming your SQL dialect has a string search function (findin this case, but I think charindexfor SQLServer).
假设您的 SQL 方言具有字符串搜索功能（find在这种情况下，但我认为charindex是 SQLServer）。
This will ensure all sub-columns begin and start with a comma (comma plus field plus comma) and look for a specific desired value (with the commas on either side to ensure it's a full sub-column match).
这将确保所有子列都以逗号（逗号加字段加逗号）开头和开头，并查找特定的所需值（两边都有逗号以确保它是完整的子列匹配）。
If you can'tcontrol what the application puts in that column, I would opt for the DBA solution - DBA solutions are defined as those a DBA has to do to work around the inadequacies of their users :-).
如果您无法控制应用程序在该列中放置的内容，我会选择 DBA 解决方案 - DBA 解决方案被定义为 DBA 为解决其用户的不足而必须做的那些 :-)。
Create two new columns in that table and make an insert/update trigger which will populate them with the two reasons that a user puts into the original column.
在该表中创建两个新列并创建一个插入/更新触发器，该触发器将使用用户放入原始列的两个原因填充它们。
Then query those two newcolumns for specific values rather than trying to split apart the old column.
然后查询这两个新列的特定值，而不是尝试拆分旧列。
This means that the cost of splitting is only on row insert/update, not on _every single select`, amortising that cost efficiently.
这意味着拆分的成本仅在行插入/更新上，而不是在 _every single select` 上，从而有效地摊销了该成本。
Still, my answer is to re-do the schema. That will be the best way in the long term in terms of speed, readable queries and maintainability.
不过，我的答案是重新做架构。从长远来看，这将是速度、可读查询和可维护性方面的最佳方式。
回答by Cesar
I hope you are just maintaining the code and it's not a brand new implementation.
Please consider to use a different approach using a support table like this:
我希望您只是维护代码，而不是全新的实现。
请考虑使用不同的方法使用支持表，如下所示：
JOBS TABLE
jobID | userID
--------------
1 | user13
2 | user32
3 | user44
--------------
PENDING TABLE
pendingID | pendingText
---------------------------
01 | Not Enough Info
02 | Not Enough Time
---------------------------
JOB_PENDING TABLE
jobID | pendingID
-----------------
1 | 01
1 | 02
2 | 01
3 | 03
3 | 01
-----------------
You can easily query this tables using JOIN or subqueries.
If you need retro-compatibility on your software you can add a view to reach this goal.
您可以使用 JOIN 或子查询轻松查询此表。
如果您的软件需要复古兼容性，您可以添加一个视图来实现此目标。
回答by freds873487
I have a tables like:
我有一个表，如：
Events
---------
eventId int
eventTypeIds nvarchar(50)
...
EventTypes
--------------
eventTypeId
Description
...
Each Event can have multiple eventtypes specified.
每个事件可以指定多个事件类型。
All I do is write 2 procedures in my site code, not SQL code
我所做的只是在我的站点代码中编写 2 个过程，而不是 SQL 代码
One procedure converts the table field (eventTypeIds) value like "3,4,15,6" into a ViewState array, so I can use it any where in code.
This procedure does the opposite it collects any options your checked and converts it in
一个过程将表字段 (eventTypeIds) 值（如“3,4,15,6”）转换为 ViewState 数组，因此我可以在代码中的任何位置使用它。
此过程执行相反的操作，它会收集您选中的所有选项并将其转换为
回答by Dwaine Bailey
If changing the schema isan option (which it probably should be) shouldn't you implement a many-to-many relationship here so that you have a bridging table between the two items? That way, you would store the number and its wording in one table, jobs in another, and "failure reasons for jobs" in the bridging table...
如果更改架构是一个选项（它可能应该是），您是否应该在这里实现多对多关系，以便在两个项目之间有一个桥接表？这样，您可以将数字及其措辞存储在一个表中，将作业存储在另一个表中，并将“作业失败原因”存储在桥接表中......
回答by Chris Bednarski
Have a look at a similar question I answered here
看看我在这里回答的类似问题
;WITH Numbers AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS N
FROM JobId
),
Split AS
(
SELECT JOB_NUMBER, USER_ASSIGNED, SUBSTRING(PENDING_INFO, Numbers.N, CHARINDEX(',', PENDING_INFO + ',', Numbers.N) - Numbers.N) AS PENDING_NUM
FROM JobId
JOIN Numbers ON Numbers.N <= DATALENGTH(PENDING_INFO) + 1
AND SUBSTRING(',' + PENDING_INFO, Numbers.N, 1) = ','
)
SELECT *
FROM Split JOIN Pending ON Split.PENDING_NUM = Pending.PENDING_NUM
The basic idea is that you have to multiply each row as many times as there are PENDING_NUMs. Then, extract the appropriate part of the string
基本思想是，您必须将每一行乘以PENDING_NUMs 的次数。然后，提取字符串的适当部分
回答by Fabrice Duche
While I agree with DBA perspective not to store multiple values in a single field it is doable, as bellow, practical for application logic and some performance issues.
Let say you have 10000 user groups, each having average 1000 members. You may want to have a table user_groups with columns such as groupID and membersID. Your membersID column could be populated like this:
(',10,2001,20003,333,4520,') each number being a memberID, all separated with a comma. Add also a comma at the start and end of the data. Then your select would use like '%,someID,%'.
If you can not change your data ('01,02,03') or similar, let say you want rows containing 01 you still can use " select ... LIKE '01,%' OR '%,01' OR '%,01,%' " which will insure it match if at start, end or inside, while avoiding similar number (ie:101).
虽然我同意 DBA 的观点，不要在单个字段中存储多个值，但如下所示，它对于应用程序逻辑和一些性能问题是可行的。
假设您有 10000 个用户组，每个用户组平均有 1000 名成员。您可能希望有一个表 user_groups，其中包含 groupID 和 membersID 等列。您的 membersID 列可以这样填充：(',10,2001,20003,333,4520,') 每个数字都是一个 memberID，所有数字都用逗号分隔。在数据的开头和结尾添加一个逗号。然后你的选择将使用像'%,someID,%'。
如果您无法更改数据 ('01,02,03') 或类似数据，假设您想要包含 01 的行，您仍然可以使用“ select ... LIKE '01,%' OR '%,01' OR '% ,01,%' " 这将确保它在开始时匹配，
