oracle 联接依赖于另一个表的表

jhdbpxl9 于 2022-11-22 发布在 Oracle

关注(0)|答案(2)|浏览(168)

我有一个POST表格、一个CATEGORY表格、一个TAG表格和一个MIGTATION_TAG表格，我解释了MIGTATION_TAG表格包含标签在类别之间的移动，例如，ID = 1的标签属于l 'ID = 10的类别，如果我将其类别更改为12，一行将添加到MIGTATION_TAG表格，如下所示：标识1标签标识1类别标识12
POST表

id         title       content     tag_id
----------  ----------  ----------  ----------
 1          title1      Text...     1
 2          title2      Text...     3
 3          title3      Text...     1
 4          title4      Text...     2
 5          title5      Text...     5
 6          title6      Text...     4

CATEGORY表格

id         name      
----------  ----------  
 1          category_1      
 2          category_2
 3          category_3

TAG表

id         name        fist_category_id
----------  ----------  ----------------
 1          tag_1       1
 2          tag_2       1
 3          tag_3       3
 4          tag_4       1
 5          tag_5       2

“迁移标记”表格

id         tag_id      category_id
----------  ----------  ----------------
 9          1           3
 8          5           1
 7          1           2
 5          3           1
 4          2           2
 3          5           3
 2          3           3
 1          1           3

所以，我想知道每个类别有多少个职位。
在某些情况下，如果标签的类别没有变化，则它保持其第一类别，我设法通过LEFT JOIN将TAG表连接到POST表，但问题是连接必须依赖于MIGTATION_TAG表，该表必须检查是否存在迁移，如果存在，则它必须为每个标签返回最后的MAX（tag_id），
这是我的问题

select category, COUNT(*) AS numer_of_posts
                  
        from(
            select CATEGORY.name,               
                case
                when POST.tag_id is not null then CATEGORY.name
                end as category              

                from POST
                left join TAG ON POST.tag_id = TAG.id
                
                left join  (
                    select id, MAX(tag_id) tag_id 
                    from MIGTATION_TAG 
                    group by id, tag_id
                ) MIGTATION_TAG 
                ON TAG.id = MIGTATION_TAG.tag_id

                left join CATEGORY on MIGTATION_TAG.category_id = CATEGORY.id         
            )
            GROUP BY category
;

这是我想在查询中显示的结果
重要！对于id = 6的帖子，tag_id = 4未更改，因此将使用TAG表中的fist_category_id

category   numer_of_posts     
----------  --------------  
category_1  3    
category_2  1
category_3  2

顺祝商祺

oracle

来源：https://stackoverflow.com/questions/74296943/join-a-table-that-depends-on-another-table

2条答案

按热度按时间

khbbv19g1#

您可以用途：

SELECT MAX(c.name) AS category,
       COUNT(*)
FROM   post p
       INNER JOIN tag t
       ON (p.tag_id = t.id)
       LEFT OUTER JOIN (
         SELECT tag_id,
                MAX(category_id) KEEP (DENSE_RANK LAST ORDER BY id) AS category_id
         FROM   migration_tag
         GROUP BY tag_id
       ) m
       ON (t.id = m.tag_id)
       INNER JOIN category c
       ON ( COALESCE(m.category_id, t.first_category_id) = c.id )
GROUP BY c.id
ORDER BY category

其中，对于示例数据：

CREATE TABLE POST ( id, title, content, tag_id ) AS
  SELECT 1, 'title1', 'Text...', 1 FROM DUAL UNION ALL
  SELECT 2, 'title2', 'Text...', 3 FROM DUAL UNION ALL
  SELECT 3, 'title3', 'Text...', 1 FROM DUAL UNION ALL
  SELECT 4, 'title4', 'Text...', 2 FROM DUAL UNION ALL
  SELECT 5, 'title5', 'Text...', 5 FROM DUAL UNION ALL
  SELECT 6, 'title6', 'Text...', 4 FROM DUAL;

CREATE TABLE CATEGORY  ( id, name ) AS
  SELECT 1, 'category_1' FROM DUAL UNION ALL
  SELECT 2, 'category_2' FROM DUAL UNION ALL
  SELECT 3, 'category_3' FROM DUAL;

CREATE TABLE TAG (id, name, first_category_id) AS
  SELECT 1, 'tag_1', 1 FROM DUAL UNION ALL
  SELECT 2, 'tag_2', 1 FROM DUAL UNION ALL
  SELECT 3, 'tag_3', 3 FROM DUAL UNION ALL
  SELECT 4, 'tag_4', 1 FROM DUAL UNION ALL
  SELECT 5, 'tag_5', 2 FROM DUAL;

CREATE TABLE  MIGRATION_TAG ( id, tag_id, category_id ) AS
  SELECT 9, 1, 3 FROM DUAL UNION ALL
  SELECT 8, 5, 1 FROM DUAL UNION ALL
  SELECT 7, 1, 2 FROM DUAL UNION ALL
  SELECT 5, 3, 1 FROM DUAL UNION ALL
  SELECT 4, 2, 2 FROM DUAL UNION ALL
  SELECT 3, 5, 3 FROM DUAL UNION ALL
  SELECT 2, 3, 3 FROM DUAL UNION ALL
  SELECT 1, 1, 3 FROM DUAL;

输出：
| 产品类别|计数（*）|
| - -|- -|
| 类别_1|三个|
| 类别_2|一个|
| 类别_3| 2个|
fiddle

赞(0）回复(0）举报 2022-11-22

67up9zun2#

一个选项使用left join来获取标签表，如果有，则使用横向连接来查找最新的迁移。然后，我们可以使用条件逻辑：

select coalesce(t2.category_id, t.first_category_id) category, count(*) number_of_posts
from post p
inner join tag t on t.id = p.tag_id
outer apply (
    select mt.category_id
    from migration_tag mt
    where mt.tag_id = p.tag_id
    order by mt.id desc fetch first row only
) t2
group by coalesce(t2.category_id, t.first_category_id)

赞(0）回复(0）举报 2022-11-22

我来回答

oracle 联接依赖于另一个表的表

2条答案

相关问题

热门标签

最新问答