遍历第二个表的行以返回结果集 [英] Iterate over the rows of a second table to return resultset

问题描述

我正在尝试通过组合，复制结果来实现考虑第二张表的结果的SELECT查询.我必须遵循一定的顺序才能使用ORDER列获得所有组合.

I'm trying to achieve a SELECT query that takes in consideration the result of a second table, by making combinations, replicating a result. I must follow a certain order to don't get all combinations, using a ORDER column.

说明我要实现的目标:

直到现在，我一直尝试将SUBSELECT与JOIN结合使用，以基于第二张表复制结果.

Until now, I tried to use SUBSELECT with JOIN to replicate the results based on a second table.

SELECT a.table_a_id, b.label_x, b.label_y 
FROM table_a a
JOIN
(
    SELECT label_x, label_y
    FROM table_b
    WHERE b.table_a_id = a.table_a_id
) b
ON b.table_a_id = a.table_a_id

但是，当然，我不能从SUBSELECT内部引用table_a.

But, of course, I cant reference the table_a from inside the SUBSELECT.

要实现所需的ResultSet，下一步应该做什么?

What should be my next steps for achieving my desired ResultSet?

推荐答案

在此处在table_b表上使用自联接，联接条件为table_a_id值匹配，但label_y> label_x

Use a self join here on the table_b table, with the join condition being that the table_a_id values match, but label_y > label_x.

SELECT
    b1.table_a_id,
    b1.label_x,
    b2.label_y
FROM table_a a
INNER JOIN table_b b1
    ON b1.table_a_id = a.table_a_id
INNER JOIN table_b b2
    ON b2.table_a_id = b1.table_a_id AND
       b2.label_y > b1.label_x
ORDER BY
    b1.table_a_id,
    b1.label_x,
    b2.label_y;

演示

这篇关于遍历第二个表的行以返回结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！