如果user_resources（t1）是一个“归一化表”，每个user => resource组合有一行，那么获取答案的查询将与将joining表放在一起一样简单。

las，是denormalized通过将resources列作为：“资源ID列表”，并用“;”分隔 字符。

如果我们可以将’resources’列转换为行，那么随着表联接变得简单，很多困难就会消失。

查询生成的输出要求：

SELECT user_resource.user, 
       resource.data

FROM user_resource 
     JOIN integerseries AS isequence 
       ON isequence.id <= COUNT_IN_SET(user_resource.resources, ';') /* normalize */

     JOIN resource 
       ON resource.id = VALUE_IN_SET(user_resource.resources, ';', isequence.id)      
ORDER BY
       user_resource.user,  resource.data

输出：

user        data    
----------  --------
sampleuser  abcde   
sampleuser  azerty  
sampleuser  qwerty  
stacky      qwerty  
testuser    abcde   
testuser    azerty

怎么样：

“技巧”是要有一个表格，其中包含从1到某个限制的数字。我叫它integerseries。它可以用于将“水平”事物转换';' delimited strings为rows。

这种工作方式是，当您与进行“联接” 时integerseries，您正在执行cross join，这就是“内部联接”“自然”发生的事情。

每行都integerseries使用与表不同的“序列号”重复，我们将其用作列表中“资源”的“索引”，以用于此目的row。

这个想法是：

• 计算列表中的项目数。
• 根据项目在列表中的位置提取每个项目。
• 使用integerseries一个行转换成一组行提取个人“资源ID”的user。resources随着我们的前进。

我决定使用两个功能：

• 给定“分隔字符串列表”和“索引”的函数将返回列表中该位置的值。我称之为：VALUE_IN_SET。即给定“ A； B； C”和“索引”为2，则返回“ B”。

• 给定“分隔字符串列表”的函数将返回列表中项目数的计数。我称之为：COUNT_IN_SET。即给定“ A; B; C”将返回3

事实证明，这两个功能integerseries应该为delimited items list in a column。

它行得通吗？

从中创建“规范化”表的查询';' delimited string in column。它显示所有列，包括由于’cross_join’（isequence.idas resources_index）而产生的值：

SELECT user_resource.user, 
       user_resource.resources,
       COUNT_IN_SET(user_resource.resources, ';')                AS resources_count, 
       isequence.id                                              AS resources_index,
       VALUE_IN_SET(user_resource.resources, ';', isequence.id)  AS resources_value
FROM 
     user_resource 
     JOIN  integerseries AS isequence 
       ON  isequence.id <= COUNT_IN_SET(user_resource.resources, ';')
ORDER BY
       user_resource.user, isequence.id

“规范化”表输出：

user        resources  resources_count  resources_index  resources_value  
----------  ---------  ---------------  ---------------  -----------------
sampleuser  1;2;3                    3                1  1                
sampleuser  1;2;3                    3                2  2                
sampleuser  1;2;3                    3                3  3                
stacky      2                        1                1  2                
testuser    1;3                      2                1  1                
testuser    1;3                      2                2  3

使用上面的“规范化” user_resources表，它是提供所需输出的简单联接：

所需的功能 （ 这些通用功能可在任何地方使用 ）

注意：这些函数的名称与mysql
FIND_IN_SET函数有关。即他们在字符串列表方面做类似的事情？

该 COUNT_IN_SET 函数：返回的数character delimited items列。

DELIMITER $$

DROP FUNCTION IF EXISTS `COUNT_IN_SET`$$

CREATE FUNCTION `COUNT_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1)
                               ) RETURNS INTEGER
BEGIN
      RETURN CHAR_LENGTH(haystack) - CHAR_LENGTH( REPLACE(haystack, delim, '')) + 1;
END$$

DELIMITER ;

的 VALUE_IN_SET 功能：在对待delimited list作为one based array并返回在给定的“索引”的值。

DELIMITER $$

DROP FUNCTION IF EXISTS `VALUE_IN_SET`$$

CREATE FUNCTION `VALUE_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1), 
                               which INTEGER
                               ) RETURNS VARCHAR(255) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
      RETURN  SUBSTRING_INDEX(SUBSTRING_INDEX(haystack, delim, which),
                     delim,
                     -1);
END$$

DELIMITER ;

相关信息：

• 最终弄清楚了如何获得SQLFiddle-工作代码来编译函数。

• 有一个适用于SQLite数据库的版本，也适用于SQLite-规范级联的字段并与之联接？

表格（含数据）：

CREATE TABLE `integerseries` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `integerseries` */

insert  into `integerseries`(`id`) values (1);
insert  into `integerseries`(`id`) values (2);
insert  into `integerseries`(`id`) values (3);
insert  into `integerseries`(`id`) values (4);
insert  into `integerseries`(`id`) values (5);
insert  into `integerseries`(`id`) values (6);
insert  into `integerseries`(`id`) values (7);
insert  into `integerseries`(`id`) values (8);
insert  into `integerseries`(`id`) values (9);
insert  into `integerseries`(`id`) values (10);

资源：

CREATE TABLE `resource` (
  `id` int(11) NOT NULL,
  `data` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `resource` */

insert  into `resource`(`id`,`data`) values (1,'abcde');
insert  into `resource`(`id`,`data`) values (2,'qwerty');
insert  into `resource`(`id`,`data`) values (3,'azerty');

用户资源：

CREATE TABLE `user_resource` (
  `user` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `resources` varchar(250) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `user_resource` */

insert  into `user_resource`(`user`,`resources`) values ('sampleuser','1;2;3');
insert  into `user_resource`(`user`,`resources`) values ('stacky','3');
insert  into `user_resource`(`user`,`resources`) values ('testuser','1;3');