相交同一表的两列与列表/东西
以下结构的
| ID | 相关 | 过程 |
|---|---|---|
| 1 | 13271,13272,513008,513009 | 13200,13271,19353,21101,21101,21105,21140 |
| ： | 表 | 我在SQL Server中 |
创建了一个具有 to make an intersect between pertinent and procedure, and have the following result:
| id | pertinent | procedure | procedures pertinents |
|---|---|---|---|
| 1 | 13271,13272,513008,513009 | 13200,13271,19353,21101,21105,21140 | 13271 |
| 2 | 18236 | 18235,19290,19749,21102, 21105,21140 | null |
，但我还没有弄清楚它如何迭代它或在SQL中划分它，有什么想法吗？先感谢您。
I created a table with the following structure in sql server:
| id | pertinent | procedure |
|---|---|---|
| 1 | 13271,13272,513008,513009 | 13200,13271,19353,21101,21105,21140 |
| 2 | 18236 | 18235,19290,19749,21102,21105,21140 |
And I want to make an intersect between pertinent and procedure, and have the following result:
| id | pertinent | procedure | procedures pertinents |
|---|---|---|---|
| 1 | 13271,13272,513008,513009 | 13200,13271,19353,21101,21105,21140 | 13271 |
| 2 | 18236 | 18235,19290,19749,21102,21105,21140 | NULL |
But i haven't figure it out how can i iterate it or split it in sql, any ideas? thank you in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
第一步是将数据库标准化为1NF，每个元组具有一个值。然后，您将有两个表格，一个称为相关的表格，有5行，一个称为过程，有12行。之后，简单的选择语句与这些表之间的联接可以足够，从而产生所需的结果。
正如其他人提到的那样，在这个非常旧的SQL Server中，这将不必要地在此表上执行此操作。由于模式归一化可能是最好的，因此同时将数据库现代化可能是一个很好的电话。一些流行的选择将是Postgres和Snowflake，而我个人最喜欢的是Singlestore。
The first step would be to normalize the database to 1NF, having one value per tuple. Then you'd have two tables, one called pertinent, with 5 rows, and one called procedure, with 12 rows. After that, a simple SELECT statement with a JOIN between these tables would suffice, producing the desired results.
As others have mentioned, this would be unnecessarily hard to do on this table in this quite old version of SQL Server. Since a schema normalization is probably the best, modernizing the database at the same time might be a good call. Some popular choices would be Postgres and Snowflake, and my personal favorite is SingleStore.