这可能与SQL有关吗？ [英] Is this possible with SQL?
问题描述
您好，
我需要以某种方式将某些记录关联在一个表格中，我想知道这在SQL中是否可行。考虑下面的表格
定义。无论是通过id1还是id2，我都需要找到所有具有关联关系的对。
。对于样本数据集，结果应该是两个分组：（1,3,5）和（2,4）。结果可以是任何
格式（行和/或列），只要有一些
分组的指示，这样我就可以打印出一份报告。 />
这是用于创建带有少量数据样本的测试用例的sql：
创建表对（id1整数，id2整数）;
插入成对值（1,1）;
插入成对值（1,5）;
插入对值（3,3）;
插入成对值（3,5）;
插入成对值（5,1）;
插入成对值（5,3）;
插入成对值（5,5）;
插入成对值（2,4）;
Hello,
I need to somehow relate certain records together in a table and I am
wondering if this is possible in SQL at all. Consider the table
definition below. I need to find all pairs that have associations
whether through id1 or id2. For the sample data set, the result should
be two groupings: (1, 3, 5) and (2,4). The result could be in any
format (rows and/or columns) as long as there is some indication of the
groupings so I can print out a report.
Here is the sql to create a test case with a small sample of the data:
create table pairs (id1 integer, id2 integer);
insert into pairs values (1, 1);
insert into pairs values (1, 5);
insert into pairs values (3, 3);
insert into pairs values (3, 5);
insert into pairs values (5, 1);
insert into pairs values (5, 3);
insert into pairs values (5, 5);
insert into pairs values (2, 4);
推荐答案
de ******* @ yahoo.com 写道：
您好，
我需要以某种方式将某些记录在表格中联系起来，而且我很想知道这是不是完全可以在SQL中使用。考虑下面的表格
定义。无论是通过id1还是id2，我都需要找到所有具有关联关系的对。
。对于样本数据集，结果应该是两个分组：（1,3,5）和（2,4）。结果可以是任何
格式（行和/或列），只要有一些
分组的指示，这样我就可以打印出一份报告。 />
Hello,
I need to somehow relate certain records together in a table and I am
wondering if this is possible in SQL at all. Consider the table
definition below. I need to find all pairs that have associations
whether through id1 or id2. For the sample data set, the result should
be two groupings: (1, 3, 5) and (2,4). The result could be in any
format (rows and/or columns) as long as there is some indication of the
groupings so I can print out a report.
一般来说我会说不，这在SQL中实际上不可能*。
你所描述的是一个不受限制的图形和sql不是
处理得很好。例如，参见本文：
*维护SQL中图形的传递闭包。
郭国栋，Leonid Libkin，苏建文和Limsoon Wong。 />
Int。 Journal of Information Technology，5（1999），46-78。
http://citeseer.ist.psu.edu/cache/pa...aintaining.pdf
特别是第4章：任意有向图的传递闭包
如果你能以某种方式限制你的图形，可能会更容易
解决方案潜伏着。树木很容易处理，
并且有许多众所周知的方法。
/ Lennart
In general I would say no, this is not *practically* possible in SQL.
What you are describing is an unrestricted graph and sql does not
handle this very well. See for example this article:
* Maintaining the transitive closure of graphs in SQL.
Guozhu Dong, Leonid Libkin, Jianwen Su and Limsoon Wong.
Int. Journal of Information Technology, 5 (1999), 46-78.
http://citeseer.ist.psu.edu/cache/pa...aintaining.pdf
Especially chapter 4: Transitive closure of arbitrary directed graphs
If you could restrict your graph somehow, there might be easier
solutions lurking around. Trees for example are quite easy to handle,
and there are a number of well known methods.
/Lennart
>我需要以某种方式将某些记录[原文如此]关联在一个表中，我想知道这在SQL中是否可行。考虑下面的表定义。我需要通过id1或id2找到所有具有关联关系的对。 <
我可以假设您的DDL应该是：
CREATE TABLE对
（ id1 INTEGER NOT NULL，
id2 INTEGER NOT NULL，
PRIMARY KEY（id1，id2））;
你能定义吗你的意思是联想 ？我认为{1,3,5}所有
都在一个组中（id1 = 5），但由于你没有（2,2）对，
你怎么得到{2,4}？
>I need to somehow relate certain records [sic] together in a table and I am wondering if this is possible in SQL at all. Consider the table definition below. I need to find all pairs that have associations whether through id1 or id2. <<
Can I assume that your DDL should have been:
CREATE TABLE Pairs
(id1 INTEGER NOT NULL,
id2 INTEGER NOT NULL,
PRIMARY KEY(id1, id2));
Can you define what you mean by "association" ? I sse that {1,3,5} all
are in a group with (id1 = 5) , but since you do not have a (2,2) pair,
how did you get {2,4} ?
Lennart写道：
Lennart wrote:
一般来说我会说不，这在SQL中实际上是不可能的。
你所描述的是一个不受限制的图形，而sql没有
处理得非常好。例如，参见本文：
*维护SQL中图形的传递闭包。
郭国栋，Leonid Libkin，苏建文和Limsoon Wong。 />
Int。 Journal of Information Technology，5（1999），46-78。
http://citeseer.ist.psu.edu/cache/pa...aintaining.pdf
特别是第4章：任意有向图的传递闭包
In general I would say no, this is not *practically* possible in SQL.
What you are describing is an unrestricted graph and sql does not
handle this very well. See for example this article:
* Maintaining the transitive closure of graphs in SQL.
Guozhu Dong, Leonid Libkin, Jianwen Su and Limsoon Wong.
Int. Journal of Information Technology, 5 (1999), 46-78.
http://citeseer.ist.psu.edu/cache/pa...aintaining.pdf
Especially chapter 4: Transitive closure of arbitrary directed graphs
非常感谢参考。我现在正在阅读它，并将看到我们可以对数据做出哪些假设来简化解决方案。
Thanks a lot for the reference. I am just reading it now and will see
what assumptions we can make about the data to simplify the solution.
这篇关于这可能与SQL有关吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
