mysql - MySQL 记录之间的笛卡尔积

Question

我有这个请求选择:

SELECT entry_id, value_id, level_id
FROM entries t1, levels t2
WHERE entry_id =  '1'
AND t2.level_id
IN (
   SELECT value_level_id
   FROM values
   WHERE value_id = t1.value_id
)

结果:

entry_id                |  value_id  |  level_id
1                       |  a         |  l1
1                       |  b         |  l1
1                       |  c         |  l2
1                       |  d         |  l2
...

我想请求获取同一条目的 l1 和 l2 元素的笛卡尔积，如下所示:

Entry_id  |  product
1         |  a,c 
1         |  a,d 
1         |  b,c 
1         |  b,d 

我尝试过使用CROSS JOIN来做到这一点，但没有机会!

有没有办法发出请求(过程)以在其他(完美的 View )表中获取此笛卡尔积？

Answer 1

这是另一个答案(参见 http://sqlfiddle.com/#!2/b2524/8 )您需要添加更多数据进行测试。我猜到了你的值(value)观可能是什么。为了便于阅读，我放入了一个 View 。如果这不起作用，您必须提供更多示例来说明数据的外观以及最终结果集的外观。基本概念是通过 COALESCE 和串联函数不断地对每个潜在级别和嵌套的最终列进行 LEFT JOIN 自身。

SELECT l1.entry_id
  ,l1.value_id AS l1_value
  ,l2.value_id AS l2_value
  ,l3.value_id AS l3_value
  ,l4.value_id AS l4_value
  ,l5.value_id AS l5_value
  ,CONCAT(l1.value_id, 
    COALESCE(CONCAT(',',l2.value_id,
                   COALESCE(CONCAT(',',l3.value_id,
                                  COALESCE(CONCAT(',',l4.value_id),
                                           COALESCE(CONCAT(',',l5.value_id),''),'')
                                  ),'')
                   ),'')) AS listvalue
FROM vw_entries_levels AS l1
LEFT JOIN vw_entries_levels AS l2
  ON l2.value_level_id = 'l2'
    AND l1.entry_id = l2.entry_id
LEFT JOIN vw_entries_levels AS l3
  ON l3.value_level_id = 'l3'
    AND l1.entry_id = l3.entry_id
LEFT JOIN vw_entries_levels AS l4
  ON l4.value_level_id = 'l4'
    AND l1.entry_id = l4.entry_id
LEFT JOIN vw_entries_levels AS l5
  ON l5.value_level_id = 'l5'
    AND l1.entry_id = l5.entry_id
WHERE l1.entry_id = '3'
  AND l1.value_level_id = 'l1'
ORDER BY listvalue