长话短说:我在多个表中需要收集数据,为了不必绘制大表,我对其进行了简化。
我需要在一个查询中执行此操作,并且不能使用PHP或任何其他语言来处理结果。(如果我可以简单地解决这一问题,我会使用PHP)
如果我要有一个将t1行连接到t2的链接表,这不会是一个问题,但是不幸的是,我也没有,也不能引入任何一个。
User table: (alias t1)
user(varchar 150),resources(varchar 250)
+-------+-------+
| user1 | 1;2;4 |
+-------+-------+
| user2 | 2 |
+-------+-------+
| user3 | 3;4 |
+-------+-------+
Resources table: (alias t2)
id(int 11 AI), data(text)
+---+-------+
| 1 | data1 |
+---+-------+
| 2 | data2 |
+---+-------+
| 3 | data3 |
+---+-------+
| 4 | data4 |
+---+-------+
| 5 | data5 |
+---+-------+
可以将多个用户连接到同一资源,并且用户可以访问一个或多个资源。
我想要一个接近的结果:
user,data
+-------+-------+
| user1 | data1 |
+-------+-------+
| user1 | data2 |
+-------+-------+
| user1 | data4 |
+-------+-------+
| user2 | data2 |
+-------+-------+
.... etc等
我有基本的mysql知识,但是这一知识超出了我的知识范围。有什么办法可以让我内部加入t2吗?
在撰写本文之前,我已阅读以下主题:
如何在联接字段中使用逗号分隔列表联接两个表
MySQL连接两个表用逗号分隔的ID
如果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.id
as 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');
我的mysql db jdbc驱动程序的版本是5.1.25。 我想这样执行sql查询: 我总是收到例外: 但当我从命令行运行这个相同的查询(用分号分隔)时,它工作得很好,并按预期输出了两个表。
问题内容: 嗨,大家好,我需要在我的数据库项目中使用纯SQL查询,同时我想使用ASP.net MVC,这样我才能学习一种新技术!我可以在不使用“ LINQ to SQL”的情况下将SQL与ASP.net MVC一起使用吗?我仍在学习,如果使用OLD SCHOOL的方法如此愚蠢,我感到抱歉,但我的老师坚持认为他想查看PURE SQL查询 问题答案: 是的。您可以为模型层使用任何内容。注意,您也可以将
问题内容: 我需要这个问题的帮助。 在MYSQL表中,我有一个字段: 我需要找到艺术家uid 401的所有记录 我做这个 我所有记录的artist_list字段值仅是“ 401”,但是如果我有11,401,则此查询不匹配。 任何的想法 ? (我无法使用LIKE方法,因为如果艺术家uid为3(匹配30、33、3333)… 问题答案: 短期解决方案 使用FIND_IN_SET函数: 长期解决方案 标准
问题内容: 当我想在网络上显示一些数据时,这些数据需要补充,而且我不知道如何实现,下面是代码: 它提示:’RowProxy’对象不支持项目分配。 我该怎么办? 表“文章”包含“关键字”列,但不包含“标签”列。 问题答案: 您可以在RowProxy中做出命令,以支持项目分配。 例如:
问题内容: 我正在尝试在我的SQL Server数据库中合并这样的内容: 变成这个: 我需要在SQL Server和Oracle中都这样做。 我发现MySQL的功能完全可以满足我的需要,但是MySQL并不是这里的选择。 编辑: 测试台: 问题答案: 这是在SQL Server 2005+中可以使用的解决方案: 参考: STUFF(Transact-SQL)
本文向大家介绍MySQL 可以用localhost 连接,但不能用IP连接的问题解决方法,包括了MySQL 可以用localhost 连接,但不能用IP连接的问题解决方法的使用技巧和注意事项,需要的朋友参考一下 MySQL localhost 连接,但不能用IP连接问题解决方案 主要涉及到MySQL 可以用localhost 连接,但不能用IP连接的问题 方面的内容,对于MySQL 可以用loca