我有一个SQL-server数据库表 [英] I have a SQL-server database tablea like

问题描述

name            id
a              1,2
b              3,4
c              4,5

这个输出是....

THE OUTPUT FOR THIS IS....

name            id
a               1
a               1
b               2
b               2
c               3
c               3

我尝试过：



我尝试加入字符串函数但我无法得到它

What I have tried:

I tried joins with string functions but I cant got it

推荐答案

不要做到这一点。您在数据库设计中犯了一个错误 - 不要将您的ID存储为逗号分隔列表，因为它们很难在SQL中使用。

相反，添加第二个表链接具有ID的名称，并将每个ID添加为该表中的单独行。这样，您可以单独使用ID，而不必处理SQL相当差的字符串处理，并且如果需要，仍然可以将它们组合到togetehr。另外，你可以使用JOIN等等，这样你就可以使用SQL了。



CSV数据很容易存储，但是要使用PITA！



如果您保留数据库格式，那么每次要使用它时都必须这样做，并且它仍然是PITA rto添加或删除ID：将列中逗号分隔的数据转换为行供选择 [ ^ ]

Don't do it. You've made a mistake in your DB design - don't store your IDs as a comma separated list, as they are very difficult to work with in SQL.
Instead, add a second table which links a name with an ID, and add each ID as a separate row in that table. That way, you can work with the IDs separately without having to mess around with SQL's rather poor string handling, and still "combine" them togetehr if you need to. Plus you get JOIN's and so forth which let you use SQL teh way it was meant to be used.

CSV data is easy to store, but a PITA to use!

If you keep the DB format you have you have to do sh*t like this every time you want to use it, and it's still a PITA rto add or remove an ID: Converting comma separated data in a column to rows for selection[^]

这篇关于我有一个SQL-server数据库表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！