mysql - 笛卡尔加入 MySQL 查询

Question

假设我有以下数据到表中:

'1','nid'
'1','step.nid'
'2','step.nid'
'3','step.nid'

这意味着每个 nid 可以有多个步骤，在这种情况下，对于 nid=1，我们有 3 个步骤。

如何编写查询以检索以下结果:

'1','nid'
'1','step.nid'
'1','nid'
'2','step.nid'
'1','nid'
'3','step.nid'

所以我需要对每个步骤组合 nid

Answer 1

尝试:

select case o.type when 'nid' then n.column1 else s.column1 end output1,
       case o.type when 'nid' then n.column2 else s.column2 end output2
from yourTable n
cross join yourTable s
cross join (select 'nid' type union all select 'step') o
where n.column2='nid' and s.column2='step.nid'
order by n.column1, s.column1, o.type

SQLFiddle here .