如何使用交叉联接查找行>列组合?[SQL] [英] How to find rows>columns combinations with Cross Join? [SQL]

问题描述

我想创建来自不同类别ID的项目组合.

I want to create a combination of items from different categories ids.

你能帮我吗?

表

+---------+-------------+------------+
| post_id | category_id | post_title |
+---------+-------------+------------+
| 1       | 1           | Red        |
| 2       | 1           | Black      |
| 3       | 2           | Medium     |
| 4       | 3           | Male       |
| 5       | 3           | Female     |
+---------+-------------+------------+

我想要的查询结果如下:

Red-Medium-Male
Black-Medium-Male
Red-Medium-Female
Black-Medium-Female

例如，如果有属于6个不同类别的项目，则如下所示:

For example, if there were items belonging to 6 different categories, it would be like below:

Red-Medium-Male-Other1-Other2-Other3

推荐答案

您可以使用交叉连接和过滤:

select t1.post_title, t2.post_title, t3.post_title
from t t1 cross join
     t t2 cross join
     t t3
where t1.category_id = 1 and
      t2.category_id = 2 and
      t3.category_id = 3;

您可以使用递归CTE对此进行概括:

You can generalize this using a recursive CTE:

with recursive tt as (
      select t.*, dense_rank() over (order by category_id) as cat_seqnum
      from t
     ),
     cte as (
      select cat_seqnum, post_title
      from tt
      where cat_seqnum = 1
      union all
      select  tt.cat_seqnum, concat_ws('-', cte.post_title, tt.post_title)
      from cte join
           tt
           on tt.cat_seqnum = cte.cat_seqnum + 1
     )
select *
from cte
where cat_seqnum = (select max(cat_seqnum) from tt);

此处是db小提琴.

这篇关于如何使用交叉联接查找行>列组合?[SQL]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！