[SQL ] access nested table attributes
一欄裡要放入多屬性的方法 在網路上查到
有用nested table 或是 varray
nested table 是存物件的形式存入一欄中
我現在有個例子
CREATE TYPE "tag_obj" AS OBJECT (tag VARCHAR(20));
/
CREATE TYPE tag_type AS TABLE OF "tag_obj";
/
CREATE TABLE po(pid INT,
tag tag_type)
NESTED TABLE tag STORE AS tag_nested;
INSERT INTO po VALUES(1,tag_type("tag_obj"('dad'),
"tag_obj"('mom'),"tag_obj"('son')));
INSERT INTO po VALUES(2,tag_type("tag_obj"('daughter')
,"tag_obj"('mom'),"tag_obj"('son')));
INSERT INTO po VALUES(3,tag_type("tag_obj"('family')
,"tag_obj"('mom'),"tag_obj"('dad')));
輸入了三筆資料
select p.pid,p.tag
from po p;
PID TAG(TAG)
------ -------------------------------------------------------
1 TAG_TYPE(tag_obj('dad'), tag_obj('mom'), tag_obj('son'))
2 TAG_TYPE(tag_obj('daughter'), tag_obj('mom'), tag_obj('son'))
3 TAG_TYPE(tag_obj('family'), tag_obj('mom'), tag_obj('dad'))
知道他資料儲存的方式 但是不知道怎麼處理物件裡的字串或數字
例如 我想讓query變成以下的樣子
TAG TIMES (次數)
-------- --------
DAD 2
MOM 3
SON 2
FAMILY 1
有查到可能是用COLUMN_VALUE
但是要如何做呢
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 216.73.196.55
推
06/25 21:32, , 1F
06/25 21:32, 1F
→
06/25 23:46, , 2F
06/25 23:46, 2F
→
06/26 09:13, , 3F
06/26 09:13, 3F