在Oracle中将表联接到自身 [英] Joining a table to itself in Oracle

问题描述

对此很困惑.

我正在尝试将表联接到自身.我有以下详细信息:

I'm trying to join a table to itself. I have the following details:

PRODUCT NO           FORMAT NO             FORMAT CODE              NUMBER
1000                 1111                  P                        0
1000                 1112                  O                        0
1000                 1113                  H                        546

我想创建一个输出所有"O"格式代码的SELECT语句，而不是在"NUMBER"列中输出"0"，而不是输出"546".看起来像这样:

I want to create a SELECT statement that outputs all the "O" format codes, but rather than outputting "0" in the "NUMBER" column, I want to output "546". It would look like this:

PRODUCT NO           FORMAT NO             FORMAT CODE              NUMBER
1000                 1112                  O                        546

所以我认为我在这里将桌子有效地连接到了桌子上?只是不确定如何执行此操作...

So I think here I'm effectively joining the table to itself? Just not sure how to do this...

谢谢！

推荐答案

从以上所述，我认为您想要这样的东西:

I think from what has been stated above that you want something like this:

SELECT p1.product_no, p1.format_no, p1.format_code, p2.h_number
  FROM myproducts p1 INNER JOIN myproducts p2
    ON p1.product_no = p2.product_no
   AND p1.format_code <> p2.format_code
   AND p2.format_code = 'H'
 WHERE p1.format_code = 'O';

我必须在编辑上补充一点，我认为这种设计有点怪异，我不确定有人可以称其为规范化".

I have to add editorially that I think this design is a bit weird and I'm not sure one could call it "normalized."

这篇关于在Oracle中将表联接到自身的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！