MySQL - 并行合并两个具有相同行数的不相关查询 [英] MySQL - Parallel merge two unrelated queries with same # of rows

问题描述

我有两张桌子:

exam_outline_items:

jml_quiz_pool:

在我尝试过的所有事情中，这让我最接近:

Of all the things I've tried, this got me the closest:

select t1.sequence, t1.title, t2.q_cat, t2.q_count
from student_pl.exam_outline_items t1
cross join pe_joomla.jml_quiz_pool t2
where t1.exam_outline_id = 5 and t1.chapter_num > 0
    and t2.q_id = 1109 and t2.q_count > 0
group by title

产生这个结果:

我只需要那些 q_cat 值不同，就像它们在第二个查询中一样.

I just need those q_cat values to be different, like they are in the 2nd query.

预先感谢您的帮助.

推荐答案

你必须有一些东西可以将它们联系起来.如果你没有这样的一列，你可以通过创建一个带有变量的行号来模拟一列.

You have to have something to connect them with. If you don't have such a column, you can simulate one by creating a rownumber with variables.

select sequence, title, q_cat, q_count from (
    select t1.sequence, t1.title, @r1 := @r1 + 1 as rownumber
    from student_pl.exam_outline_items t1
    , (select @r1 := 0) var_init
    where t1.exam_outline_id = 5 and t1.chapter_num > 0
    order by t1.sequence
) a
inner join
(
    select t2.q_cat, t2.q_count, @r2 := @r2 + 1 as rownumber
    from pe_joomla.jml_quiz_pool t2
    , (select @r2 := 0) var_init
    where t2.q_id = 1109 and t2.q_count > 0
    order by t2.q_cat
) b on a.rownumber = b.rownumber;

另请注意，我在这些查询中使用了 order by.在数据库中，除非您使用 order by 明确设置，否则您没有排序顺序.

Also note, that I used order by in those queries. In a database you have no sort order unless you explicitly set it with order by.

这篇关于MySQL - 并行合并两个具有相同行数的不相关查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！