从子查询 MSSQL 返回 ID 以分号分隔的字符串的行 [英] Return rows where ID is in semicolon separated string from subquery MSSQL

问题描述

我正在尝试查询我的 sql 数据库以返回 ID 包含在单独表列中的所有行.项目 ID 列表保存在 Project_ID 列的 Feedback 表中，数据类型为 varchar.我试图从 Projects 表中返回行，其中 ID 保存在 Project_ID 列中，数据类型为 varchar.

I'm trying to query my sql database to return all the rows where the ID is contained in a separate tables column. The list of project IDs is kept in the Feedback table in the Project_ID Column with datatype varchar. I am trying to return the rows from the Projects table where the IDs are kept in the Project_ID column with datatype varchar.

我正在使用查询执行此操作

I am doing this using the query

SELECT * FROM Projects WHERE Project_ID IN (
    SELECT Project_ID FROM Feedback WHERE ID = 268 and Project_ID IS NOT NULL
)

当我运行这个查询时，我会收到以下消息:

When I run this query I am returned with the message:

Conversion failed when converting the varchar value '36;10;59' to data type int

推荐答案

这是规范化数据重要性的另一个例子.
将多个数据点保存在单列中几乎从来都不是正确的设计，我的意思几乎从来都不是 99.9999%.

This is yet another example of the importance of normalizing your data.
Keeping multiple data points in a single column is almost never the correct design, and by almost never I mean about 99.9999%.

如果您无法规范化您的数据库，您可以使用如下解决方法:

If you can't normalize your database, you can use a workaround like this:

SELECT * 
FROM Projects p
WHERE EXISTS (
    SELECT Project_ID 
    FROM Feedback F WHERE ID = 268 
    AND Project_ID IS NOT NULL
    AND ';'+ F.Project_ID +';' LIKE '%;'+ CAST(p.Project_ID as varchar) +';%'
)

您不能使用 IN 运算符，因为它需要一个以逗号分隔的值列表，而您尝试为其提供一个以分号分隔的值.即使 Project_ID 中的值用逗号分隔，它仍然不起作用.

You can't use the IN operator since it's expecting a list of values delimited by a comma, while you try to supply it with a single value that is delimited by a semicolon. Even if the values in Project_ID was delimited by a comma it would still not work.

我在两个表中 Project_ID 的每一侧都添加了 ; 的原因是这样 LIKE 运算符将返回true 对于在 Feedback.Project_Id 中找到 Projects.Project_Id 的任何位置.您必须将 ; 添加到 Projects.Project_Id 以防止 LIKE 在您查找时返回 true与分隔字符串中的数字部分匹配的数字.考虑在包含 1;112;455 的字符串中查找 12 - 不将分隔符添加到搜索值(在此示例中为 12)，LIKE 运算符将返回 true.

The reason I've added the ; on each side of the Project_ID in both tables is that this way the LIKE operator will return true for any location it finds the Projects.Project_Id inside the Feedback.Project_Id. You must add the ; to the Projects.Project_Id to prevent the LIKE to return true when you are looking for a number that is a partial match to the numbers in the delimited string. Consider looking for 12 in a string containing 1;112;455 - without adding the delimiter to the search value (12 in this example) the LIKE operator would return true.

这篇关于从子查询 MSSQL 返回 ID 以分号分隔的字符串的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！