如果
user_resources（t1）是一个“归一化表”，每个
user =>resource组合有一行，那么获取答案的查询将与将
joining表放在一起一样简单。
las，是
denormalized通过将
resources列作为：“资源ID列表”，并用“;”分隔 字符。
如果我们可以将’resources’列转换为行，那么随着表联接变得简单，很多困难就会消失。
查询生成的输出要求：
SELECt user_resource.user, resource.dataFROM user_resource JOIN integerseries AS isequence ON isequence.id <= COUNT_IN_SET(user_resource.resources, ';') 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。它可以用于将“水平”事物转换
';' delimitedstrings为
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 incolumn。它显示所有列，包括由于’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_valueFROM 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;32 1 1 testuser 1;32 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 INTEGERBEGIN 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_unipre_ciBEGIN 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_unipre_ci;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_unipre_ci DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unipre_ci;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_unipre_ci NOT NULL, `resources` varchar(250) COLLATE utf8_unipre_ci DEFAULT NULL, PRIMARY KEY (`user`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unipre_ci;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');
