SQL选项存储和参考

Question

如果我有一个表：

CREATE TABLE Kids (
    kid_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    kid_name varchar(45) NOT NULL,
    kid_favcolors (text) NULL,
    PRIMARY_KEY(kid_id)
    )

并且我有一个表：

CREATE TABLE Colors (
    color_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    color_name varchar(45) NOT NULL,
    PRIMARY_KEY(color_id)
)

通过逗号分隔的 id 引用最喜欢的颜色的行：

INSERT INTO Kids(kid_name, kid_favcolors) VALUES('Joe','1,2,3,4,5');
INSERT INTO Kids(kid_name, kid_favcolors) VALUES('Mary','1,2,3');

我将如何创建一个语句，该语句将返回 Kid 数据库中的每个条目以及 Kid_name 和所有引用的 id 的 color_name在 Kid_favcolors 列中。

例如：

COLORS:

color_id    color_name
1           yellow
2           green
3           blue
4           purple
5           red
6           brown
7           black

KIDS:

kid_id      kid_name    kid_favcolors
1           Joe         1,2,3,4,5
2           Mary        1,2,3

我想

kid_id      kid_name    favorite_colors
1           Joe         yellow, green, blue, purple, red
2           Mary        yellow, green, blue

检索正在对现有数据和数据库结构执行的查询。如果没有可行的解决方案，可以重新排列数据结构，但我认为这会增加解决方案的大量时间。无论哪种方式，都需要一些帮助。

我可以使用以下方法从数据库中检索颜色：

SELECT STUFF((SELECT ', ' + color_name FROM colors WHERE color_id IN (1,2,3,4,5) FOR XML PATH('')),1, 2, '') AS colors

        colors
1       yellow, green, blue, purple, red

但是当我尝试更复杂的查询时，我似乎找不到通过从 Kids 表中提取 id 来合并上述语句的方法。

SELECT kids.kid_id, kids.kid_name, favorite_colors FROM kids JOIN colors ON colors.id IN kids.kid_favcolors as favorite_colors

虽然它看起来对我有用，但不起作用。不确定我是离得很远还是很接近。

Answer 1

在原始解决方案中，在字段中存储 CSV 字符串违反了 1NF；实际上——根据定义——该表甚至不符合关系的资格。因此，在这种情况下，将字符串视为一个原子数据，并在应用层上对其进行分解。

要在DB层解决，只需修复设计即可。

Answer 2

我已经想出了一个解决方案；虽然它不漂亮，但它有效。

SELECT 
  kid_id,
  kid_name, 
  (SELECT STUFF(
     (SELECT ', '+color_name FROM colors WHERE color_id in 
        (SELECT s from SplitString(kid_favcolors,',')) 
     FOR XML PATH(''))
   ,1,2,'')) AS favorite_colors 
FROM KIDS

kid_id      kid_name    favorite_colors
1           Joe         yellow, green, blue, purple, red
2           Mary        yellow, green, blue

我已经与他们讨论了重新设计数据库并实现当前管理站点以适应更改

我使用的拆分字符串函数：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[SplitString] 
(
    @str nvarchar(4000), 
    @separator char(1)
)
returns table
AS
return (
    with tokens(p, a, b) AS (
        select 
            1, 
            1, 
            charindex(@separator, @str)
        union all
        select
            p + 1, 
            b + 1, 
            charindex(@separator, @str, b + 1)
        from tokens
        where b > 0
    )
    select
        p-1 zeroBasedOccurance,
        substring(
            @str, 
            a, 
            case when b > 0 then b-a ELSE 4000 end) 
        AS s
    from tokens
  )