字符串拆分列并连接到另一个表 [英] String split column and join to another table

问题描述

假设我有 2 个这样的表:

工作机会:

+----+------------+------------+|身份证 |姓名 |分类 |+----+------------+------------+|1 |程序员 |1,2 ||2 |分析师 |3 |+----+------------+------------+

类别:

+----+-----------------+|身份证 |姓名 |+----+-----------------+|1 |编程||2 |网络编程||3 |分析 |+----+-----------------+

我们有一个字符串拆分，它接受一个字符串、一个分隔符并返回一个表，我的问题是我真的不知道如何将表集成到我的查询中以加入工作机会和类别.

我的想法是它会是这样的:

SELECTO.[ID] AS OfferID,O.[名称] AS OfferName,CAT.[类别名称] AS 类别名称，CAT.[CategoryID] AS CategoryID从工作机会 AS O左加入(选择O.[ID] AS OfferID,C.[CategoryID] AS CategoryID,C.[名称] AS 名称从(选择 *FROM [dbo].[Split](O.[Categories], ',')) 作为 CJ左加入[类别] AS CON C.CategoryID = CJ.items) 作为 CAT ON CAT.OfferID = O.[ID]

目前我有两个错误说:

• 多部分标识符O.[ID]不能绑定
• multi-part identifier O.[Categories] 不能绑定
• AS 附近的语法错误(最后一行)

很明显，问题在于我如何构建我的子查询.

解决方案

您可以将其大大简化为类似这样的事情.

SELECTO.[ID] AS OfferID,O.[名称] AS OfferName,c.[类别名称] AS 类别名称，c.[CategoryID] AS CategoryID从工作机会 AS O外应用 [dbo].[Split](O.[Categories], ',') s在 c.CategoryID = s.Items 上将类别左连接为 C

我担心的是你的分离器.如果有多个选择语句，性能将受到严重影响.有关可用的各种拆分器的详细说明，您可以访问这篇文章.

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

Let's say I have 2 tables like this :

Job Offers:

+----+------------+------------+
| ID |    Name    | Categories |
+----+------------+------------+
|  1 | Programmer | 1,2        |
|  2 | Analyst    | 3          |
+----+------------+------------+

Categories:

+----+-----------------+
| ID |      Name       |
+----+-----------------+
|  1 | Programming     |
|  2 | Web Programming |
|  3 | Analysis        |
+----+-----------------+

We've got a string split that takes a string, a delimiter and returns a table, my problem is I'm really not sure how to integrate the table in my query to join the job offers and the categories.

My thinking is that it would be something like this :

SELECT
    O.[ID]                  AS OfferID,
    O.[Name]                AS OfferName,
    CAT.[CategoryName]      AS CategoryName,
    CAT.[CategoryID]        AS CategoryID
FROM
    JobOffers AS O
LEFT JOIN
(
    SELECT 
        O.[ID]              AS OfferID,
        C.[CategoryID]      AS CategoryID,
        C.[Name]            AS Name
    FROM
        (
            SELECT *
            FROM [dbo].[Split](O.[Categories], ',')
        ) AS CJ
    LEFT JOIN
        [Categories] AS C
        ON C.CategoryID = CJ.items  
) AS CAT ON CAT.OfferID = O.[ID]

Currently I have two errors saying:

• multi-part identifier O.[ID] cannot be bound
• multi-part identifier O.[Categories] cannot be bound
• incorrect syntax near AS (last line)

So clearly the problem is how I construct my subquery.

解决方案

You can greatly simplify this to something like this.

SELECT
    O.[ID]                  AS OfferID,
    O.[Name]                AS OfferName,
    c.[CategoryName]      AS CategoryName,
    c.[CategoryID]        AS CategoryID
FROM
    JobOffers AS O
outer apply [dbo].[Split](O.[Categories], ',') s
left join Categories as C on c.CategoryID = s.Items

The concern I have is your splitter. If there is more than a single select statement the performance is going to suffer horribly. For a good explanation of various splitters available you can visit this article.

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

这篇关于字符串拆分列并连接到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！