是否有可能将2个表与空格分隔的字符串与int连接 [英] Is there a possibility join 2 tables with space separated string with int

问题描述

我有两张桌子，我们称之为桌子A和桌子B.他们看起来像：



我尝试了什么： < br $>

 A：
 SLNAccessGroup AssignedDoorGroups 
 1 1 2 3 4 
 2 1 2 
 3 1 
 
 B：
 SlnDoorGroup 
 1 
 2 
 3 
 4 

我想加入AssignDoorGroups与SlnDoorGroup

解决方案

不要这样做。虽然它可以做到这是一个巨大的faff练习 - 效率非常低。

相反，表A应该有多行，每一行都配对一个SLNAccessGroup和一个AssignedDoorGroups：

 A：
 SLNAccessGroup AssignedDoorGroup 
 1 1 
 1 2 
 1 3 
 1 4 
 2 1 
 2 2 
 3 1 

然后用它做大部分事情变得很简单。

按照你拥有的方式和你尝试做的所有事情离开它超出基本的SELECT和INSERT操作，因为非常复杂。



你想继续你的 - 非常糟糕的 - 数据设计，那么你将不得不阅读： 将列中逗号分隔的数据转换为行以供选择 [ ^ ]然后将其修改为启动做你想做的事。但我怀疑是否有人会为你做这一切，因为支持糟糕的决定是一个坏主意。

使用SQL Server 2017中的模糊连接与R匹配数据集 [ ^

I have two tables, lets call it table A and table B. They looks like:

What I have tried:

A:
SLNAccessGroup    AssignedDoorGroups
1                 1 2 3 4 
2                 1 2
3                 1

B:
SlnDoorGroup
1
2
3
4

I want to join AssignedDoorGroups with SlnDoorGroup

解决方案

Don't do it like that. While it's possible to do it's an enormous faff exercise - which gets horribly inefficient.
Instead, Table A should have multiple rows, each of which pair up one SLNAccessGroup with a single AssignedDoorGroups:

A:
SLNAccessGroup    AssignedDoorGroup
1                 1 
1                 2 
1                 3 
1                 4 
2                 1
2                 2
3                 1

Then it becomes simple to do most things with it.
Leaving it the way you have it and everything you try to do beyond basic SELECT and INSERT operations because horribly complicated.

You want to continue with your - very poor - data design, then you'r going to have to read this: Converting comma separated data in a column to rows for selection[^] and then modify it to start doing what you want. But I doubt if anyone will do it all for you as it's a bad idea to prop up bad decisions.

Match datasets using Fuzzy Joins in SQL Server 2017 with R[^]

这篇关于是否有可能将2个表与空格分隔的字符串与int连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！