sql-server - SQL 服务器 : column with list of foreign keys

Question

SQL 服务器问题:

我有一个表，其中有一列包含外键列表

| ID | PRICE | LIST_OF_FOREIGN_IDS |
------------------------------------
| 3 | 89 | 67,68,69 |
| 4 | 120 | 45,46 |

我需要一个具有单个 ID 和每行单个 FOREIGN_ID 的 View 。

| ID | PRICE | FOREIGN_ID |
---------------------------
| 3 | 89 | 67 |
| 3 | 89 | 68 |
| 3 | 89 | 69 |
| 4 | 120 | 45 |
| 4 | 120 | 46 |

有人知道从第二个表中获取数据的解决方案吗？

Answer 1

如果可能，您需要修复此表上的数据库设计，这样您就不会存储以逗号分隔的值列表。这将很难维护。

理想情况下，您的表结构可以更改为如下所示:

create table item_details
(
  id int,
  price int
);

create table foreign_details
(
  id int,
  details varchar(50)
);

create table item_foreign
(
  item_id int,
  foreign_id int
);

然后您将使用查询获得结果:

select i.id, i.price, f.id 
from item_details i
inner join item_foreign ifd
  on i.id = ifd.item_id
inner join foreign_details fd
  on ifd.foreign_id = f.id

如果您无法修复当前的表结构，那么您可以使用拆分功能将数据分成多行。示例函数可以是:

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return 
end;

然后您将使用 CROSS APPLY 为每一行传递以逗号分隔的列表，类似于:

select t.id, t.price,
  c.items foreign_id
from yt t
cross apply dbo.split(t.LIST_OF_FOREIGN_IDS, ',') c;

参见 SQL Fiddle with Demo