我遇到了一个问题,我不知道如何解决它。
我有以下结构:
- PIECE表
- 表PIECE_CHARACTHERISTIC
- 表CHARACTERISTIC
表PIECE_CHARACTHERISTIC表示PIECE和CHARACTERISTIC之间的关系NxN。
因此,海贼王可以有很多特点。我需要获取其中一些特征,并将它们显示为查询结果中的一列:
SELECT PC.CHARACTERISTIC_ID, PC.CHAR_VALUE FROM PIECE P
INNER JOIN PIECE_CHARACTHERISTIC PC ON P.PIECE_ID = PC.PIECE_ID
WHERE
PC.CHARACTERISTIC_ID = 1 OR
PC.CHARACTERISTIC_ID = 2 OR
PC.CHARACTERISTIC_ID = 3;
作为这个查询的结果,我有这样的东西:
PIECE CHARACTERISTIC_ID CHAR_VALUE
P1 1 A
P1 2 B
P1 3 C
P2 1 D
P2 2 E
P3 1 X
...
我希望结果是这样的:
PIECE CHAR_1 CHAR_2 CHAR_3
P1 A B C
P2 D E NULL
P3 X NULL NULL
继续讲,我想将一列特征的结果转换为多列中的每个特征的值。非常感谢你的帮助!!
这应该有效,您只需要为的每一列特征添加左连接
SELECT
P.PIECE_ID,
PC1.CHAR_VALUE AS CHAR_1,
PC2.CHAR_VALUE AS CHAR_2,
PC3.CHAR_VALUE AS CHAR_3
FROM PIECE P
LEFT JOIN PIECE_CHARACTHERISTIC PC1
ON PC1.PIECE_ID = P.PIECE_ID AND PC1.CHARACTERISTIC_ID = 1
LEFT JOIN PIECE_CHARACTHERISTIC PC2
ON PC2.PIECE_ID = P.PIECE_ID AND PC1.CHARACTERISTIC_ID = 2
LEFT JOIN PIECE_CHARACTHERISTIC PC3
ON PC3.PIECE_ID = P.PIECE_ID AND PC1.CHARACTERISTIC_ID = 3
你可以试试这个。这有点难看,但应该行得通。
with
A as (
select PIECE, listagg(CHAR_VALUE,'|') within group (order by CHAR_VALUE) TXT
from
PIECE P, PIECE_CHARACTHERISTIC PC
where
P.PIECE_ID = PC.PIECE_ID
group by PIECE
)
select
A.PIECE,
substr(A.TXT, 1, 1) CHAR_1,
substr(A.TXT, 3, 1) CHAR_2,
substr(A.TXT, 5, 1) CHAR_3
from A