如何在两个或多个嵌套循环中迭代连接数据？

Some time ago I asked a question about nested loops on SO and as it was, there were queries inside the loops of my example and I got a clear answer:

前段时间我在SO上问了一个关于嵌套循环的问题,因为在我的例子的循环中有查询,我得到了一个明确的答案:

NEVER EVER NEVER put an SQL query inside a loop

永远不要在循环中放入SQL查询

I've tried ever since and mostly it works. Just need to make an effort and write a query that retrieves all you need at once.

从那时起我就一直尝试过,而且大多数都有效。只需要努力并编写一个查询,一次检索所需的一切。

BUT what do you do when you have a dataset from a JOIN query which contains nested data which you need to output in a nested way?

但是当你有一个来自JOIN查询的数据集时,你会怎么做,它包含你需要以嵌套方式输出的嵌套数据?

Example join from table A and B:

来自表A和B的示例连接:

A.a     |  B.a     |  B.b
--------|----------|-------------
fruits  |  banana  |  yellow
fruits  |  apple   |  red
animals |  zebra   |  black&white
animals |  elefant |  gray
animals |  fox     |  red
planets |  earth   |  blue
planets |  mars    |  red

ok, now I got that all in an array or rowset and now I need to display something like that:

好的,现在我把它全部放在数组或行集中,现在我需要显示类似的东西:

fruits

yellow banana
red apple

animals

black&white zebra
gray elefant
red fox

planets

blue earth
red mars

it seems obvious that it should work but I've tried to wrap my mind around it several times now and I just can't come up with a solution.

很明显,它应该可以工作,但我已经尝试过多次思考它并且我无法想出一个解决方案。

At the moment I do it my old way:

目前我以旧的方式做到这一点:

query groups

foreach groups
{
    query animals in group
    foreach animal
}

but hey, NEVER EVER NEVER put sql inside a loop. so what shold I do? I do PHP but I think this is a meta question.

但是,嘿,永远不要把sql放在循环中。那么我做什么呢?我做PHP,但我认为这是一个元问题。

2 个解决方案

#1

Use the control break algorithm.

使用控制中断算法。

I'd return a result set exactly as you show in the question:

我将完全按照您在问题中显示的结果集返回结果集:

A.a     |  B.a     |  B.b
--------|----------|-------------
fruits  |  banana  |  yellow
fruits  |  apple   |  red
animals |  zebra   |  black&white
animals |  elefant |  gray
animals |  fox     |  red
planets |  earth   |  blue
planets |  mars    |  red

loop over all the rows:

遍历所有行:

when A.a changes, output the title

当A.a改变时,输出标题

then always output the B.b + B.a value

然后总是输出B.b + B.a值

pseudo code for application calling SQL:

应用程序调用SQL的伪代码:

set last_A = null
exec query

loop over result set {
    if last_A == null or fetch_A!=last_A {
        last_A=fetch_A
        display fetch_a
    }
    display fetch_Bb + fetch_Ba
    }
}//loop

#2

If what you have is a hierarchy, a "directed acyclic graph". SQL does not do these.

如果您拥有的是层次结构,则是“有向无环图”。 SQL不会这样做。

There are other graph-theory things SQL does not do.

SQL没有其他的图论理论。

Since SQL does not do this, the "never put SQL in a loop" rule goes out the window.

由于SQL没有这样做,所以“永远不会把SQL放在循环中”规则就会消失。

You must put the SQL in a loop for hierarchies and other graph-connection problems involving lattices and networks.

您必须将SQL放在循环中,以用于层次结构和涉及格子和网络的其他图形连接问题。

Indeed, for hierarchies, you must use recursive loops to connect all elements of the hierarchy to arbitrary depth.

实际上,对于层次结构,必须使用递归循环将层次结构的所有元素连接到任意深度。

If, on the other hand, you're just reformatting the query result to look like a nested hierarchy, then you're just reformatting a single SQL result set into what appears to be nested lists.

另一方面,如果您只是将查询结果重新格式化为嵌套层次结构,那么您只需将单个SQL结果集重新格式化为看似嵌套的列表。

This will be one select with complex loops around the result set. One select -- not in a loop -- and a complex loop to process one result set.

这将是一个选择,结果集周围有复杂的循环。一个选择 - 不在循环中 - 和一个复杂的循环来处理一个结果集。

#1