向ORACLE板寻求帮助：匹配规范化和非规范化表 [英] Ask for help from ORACLE board: matching normalized and denormalized tables

问题描述

我在ORACLE板上发现了这个问题。

2输入表格：


项目

Id ItemName

1电话

2桌子

3灯

4电视

5立体声


ItemsByRooms

Id房间RoomItems

单卧室电话，台灯，床，电视

2厨房电话，桌子

3 LvRoom电话，灯泡，电视，立体声


预期结果：

结果将是：


Id项目名称数量

1电话3

2表1

3灯2

4电视2

5立体声1

ORACLE董事会成员没有解决这个问题。


这是我的解决方案：

WITH T1（Id，Room，RoomItems）AS

（VALUES（1，''卧室''，''电话，灯，床，电视''），

（2，''厨房''，''电话，桌子'），

（3，''' LvRoom''，''电话，灯，电视，立体声''）），

T2（Id，ItemName）AS

（VALUES（1，''电话'） '），

（2，'''表''），

（3，''Lamp''），

（4， ''TV''），

（5，''Stereo''））

SELECT CHAR（T2.ID）|| ''''|| ITEMNAME" ID ITEM_NAME" ，count（*）AS

QTY_USED

来自T1，T2

WHERE（LENGTH（STRIP（RoomItems）） - LENGTH（REPLACE（STRIP） （RoomItems），

ITEMNAME，''''）））0

GROUP BY CHAR（T2.ID）|| ''''|| ITEMNAME;


ID ITEM_NAME QTY_USED

------------------- -----------

1电话3

2表1

3灯2

4 TV 2

5 Stereo 1


5条记录被选中。


有没有另一种解决方案？

例如使用Recursion。

提前感谢'Leny G.


-

通过DBMonster.com发布的消息
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

I found this problem on ORACLE board.
2 input TABLES:

Items
Id ItemName
1 Phone
2 Table
3 Lamp
4 TV
5 Stereo

ItemsByRooms
Id Room RoomItems
1 Bedroom Phone, Lamp, Bed, TV
2 Kitchen Phone, Table
3 LvRoom Phone, Lamp, TV, Stereo

Expected result:
Results would be:

Id ItemName QtyUsed
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1

This Problem wasn''t resolved by ORACLE board members.

Here is my solution:
WITH T1 (Id,Room,RoomItems) AS
(VALUES(1, ''Bedroom'', ''Phone, Lamp, Bed, TV''),
(2, ''Kitchen'', ''Phone, Table''),
(3, ''LvRoom'', ''Phone, Lamp, TV, Stereo'')),
T2(Id, ItemName) AS
(VALUES(1, ''Phone''),
(2, ''Table''),
(3, ''Lamp''),
(4, ''TV''),
(5, ''Stereo''))
SELECT CHAR(T2.ID)|| '' ''||ITEMNAME "ID ITEM_NAME" ,count(*) AS
QTY_USED
FROM T1,T2
WHERE (LENGTH(STRIP(RoomItems)) - LENGTH(REPLACE(STRIP(RoomItems),
ITEMNAME,''''))) 0
GROUP BY CHAR(T2.ID)|| '' ''||ITEMNAME;

ID ITEM_NAME QTY_USED
------------------- -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1

5 record(s) selected.

Is there is another solutions?
For example using Recursion.
Thank''s in advance Leny G.

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

推荐答案

lenygold通过DBMonster.com写道：


[snip]

lenygold via DBMonster.com wrote:

[snip]

还有另一个解决方案？

例如使用R ecursion。

Is there is another solutions?
For example using Recursion.

自然......（不是我赞成这种可怕的结构;-)


WITH

T1（ID，ROOM，ITEMS）AS（

VALUES

（1，''卧室''，''电话，灯，床，电视''' ），

（2，''厨房''，''电话，桌子'），

（3，''LvRoom''，''电话，灯，电视，立体声''）

），

T2（ID，ITEM）AS（

VALUES

（1，''电话''），

（2，''表''），

（3，''灯''），

（4，''TV''），

（5，''Stereo''）

），

T3 （ITEMS，I，J，ITEM）AS（

SELECT

ITEMS，

1，

CASE

WHEN LOCATE（''，''，ITEMS）= 0 THEN LENGTH（ITEMS）+ 1

ELSE LOCATE（''，''，ITEMS）+ 1

结束，

TRIM（案例

当位置（''，''，项目）= 0那么项目

ELSE SUBSTR（项目，1，LOCATE（''，' ，ITEMS） - 1）

END）

来自T1


UNION ALL


选择

项目，

我+ 1，

CASE

当位置（''，''' ，项目，J）= 0那么长（项目）+ 1

ELSE LOCATE（''，''，ITEMS，J）+ 1

END，
TRIM（案例

当位置（''，''，项目，J）= 0那么SUBSTR（项目，J）

ELSE SUBSTR（项目） ，J，LOCATE（''，''，ITEMS，J） - J）

END）

来自T3

我在哪里< 20

和J< = LENGTH（项目）

）

SELECT

T2.ID，

T2.ITEM，

COUNT（*）AS COUNT

FROM

T2 INNER JOIN T3 ON T2.ITEM = T3.ITEM

GROUP BY

T2.ID，

T2.ITEM;

注意I列在递归T3 CTE中只包括

防止无限递归警告。 J列是有趣的

，它计算下一个子串的开始。


上述查询的结果如下： br $>

ID项目数

----------- ------ -----------

1电话3

2表1

3灯2

4电视2

5立体声1


选择5条记录。

只是为了证明递归分裂了ITEMS专栏

正确，并观察J列递增，用SELECT ITEMS，J，ITEM FROM T3替换主要的
查询，结果变为：


项目J项目

----------------------- ----------- - ---------------------

手机，台灯，床，电视7电话

电话，表7电话

手机，台灯，电视，立体声7电话

电话，台灯，床，电视13灯

电话，表13表

电话，灯泡，电视，立体声13灯

电话，灯，床，电视18床

电话，台灯，电视，立体声17电视

电话，台灯，床，电视21电视

电话，灯泡，电视，立体声24立体声


选择10条记录。

如果WHERE ID = 1，则更清楚一点。添加到T3查询的初始部分

，在这种情况下你得到：


项目J项目

- --------------------- ----------- ------------------ -----

手机，台灯，床，电视7电话

手机，台灯，床，电视13灯

手机，台灯，床，电视18床

电话，台灯，床，电视21电视


4条记录被选中。

干杯，


戴夫。

Naturally... (not that I approve of such horrid structures ;-)

WITH
T1 (ID, ROOM, ITEMS) AS (
VALUES
(1, ''Bedroom'', ''Phone, Lamp, Bed, TV''),
(2, ''Kitchen'', ''Phone, Table''),
(3, ''LvRoom'', ''Phone, Lamp, TV, Stereo'')
),
T2 (ID, ITEM) AS (
VALUES
(1, ''Phone''),
(2, ''Table''),
(3, ''Lamp''),
(4, ''TV''),
(5, ''Stereo'')
),
T3 (ITEMS, I, J, ITEM) AS (
SELECT
ITEMS,
1,
CASE
WHEN LOCATE('','', ITEMS) = 0 THEN LENGTH(ITEMS) + 1
ELSE LOCATE('','', ITEMS) + 1
END,
TRIM(CASE
WHEN LOCATE('','', ITEMS) = 0 THEN ITEMS
ELSE SUBSTR(ITEMS, 1, LOCATE('','', ITEMS) - 1)
END)
FROM T1

UNION ALL

SELECT
ITEMS,
I + 1,
CASE
WHEN LOCATE('','', ITEMS, J) = 0 THEN LENGTH(ITEMS) + 1
ELSE LOCATE('','', ITEMS, J) + 1
END,
TRIM(CASE
WHEN LOCATE('','', ITEMS, J) = 0 THEN SUBSTR(ITEMS, J)
ELSE SUBSTR(ITEMS, J, LOCATE('','', ITEMS, J) - J)
END)
FROM T3
WHERE I < 20
AND J <= LENGTH(ITEMS)
)
SELECT
T2.ID,
T2.ITEM,
COUNT(*) AS COUNT
FROM
T2 INNER JOIN T3 ON T2.ITEM = T3.ITEM
GROUP BY
T2.ID,
T2.ITEM;
Note that the I column in the recursive T3 CTE is only included to
prevent infinite recursion warnings. The J column is the "interesting"
one which calculates the start of the next substring.

The result of the above query is as follows:

ID ITEM COUNT
----------- ------ -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1

5 record(s) selected.
Just to prove the recursion is splitting out the ITEMS column
correctly, and to observe the J column incrementing, replace the main
query with "SELECT ITEMS, J, ITEM FROM T3", and the result becomes:

ITEMS J ITEM
----------------------- ----------- -----------------------
Phone, Lamp, Bed, TV 7 Phone
Phone, Table 7 Phone
Phone, Lamp, TV, Stereo 7 Phone
Phone, Lamp, Bed, TV 13 Lamp
Phone, Table 13 Table
Phone, Lamp, TV, Stereo 13 Lamp
Phone, Lamp, Bed, TV 18 Bed
Phone, Lamp, TV, Stereo 17 TV
Phone, Lamp, Bed, TV 21 TV
Phone, Lamp, TV, Stereo 24 Stereo

10 record(s) selected.
It''s a bit clearer if "WHERE ID = 1" is added to the initial part of
the T3 query, in which case you get:

ITEMS J ITEM
----------------------- ----------- -----------------------
Phone, Lamp, Bed, TV 7 Phone
Phone, Lamp, Bed, TV 13 Lamp
Phone, Lamp, Bed, TV 18 Bed
Phone, Lamp, Bed, TV 21 TV

4 record(s) selected.
Cheers,

Dave.

谢谢Dave。

我们拥有世界上最好的董事会

干杯。 Leny G.


Dave Hughes写道：

Thank you Dave.
We have the best board in world
Cheers. Leny G.

Dave Hughes wrote:

> [snip]

>[snip]

>还有其他解决方案吗？
例如使用Recursion。

>Is there is another solutions?
For example using Recursion.

当然......（不是我赞成这种可怕的结构;-)

与T1（ID，ROOM，ITEMS）AS （

VALUES

（1，''卧室''，''电话，灯，床，电视''），

（2 ，''厨房'，''电话，桌子'），

（3，''LvRoom''，''电话，灯，电视，立体声''）
） ，
T2（ID，ITEM）AS（

VALUES

（1，''电话''），

（2 ，''表''），

（3，''灯''），

（4，''电视''），

（5，''Stereo''）
），
T3（ITEMS，I，J，ITEM）AS（

SELECT

项目，

1，

CASE

当位置（''，''，项目）= 0那么长（项目）+ 1

ELSE LOCATE（''，''，ITEMS）+ 1

END，

TRIM（CASE

WHEN LOCA TE（''，''，ITEMS）= 0那些项目

ELSE SUBSTR（项目，1，定位（''，''，项目） - 1）

结束）

来自T1


UNION ALL


SELECT

ITEMS，

我+ 1，

案例

当位置（''，''，项目，J）= 0那么长（项目）+ 1

ELSE LOCATE（''，''，ITEMS，J）+ 1

END，

TRIM（CASE

WHEN LOCATE（''，''，ITEMS，J）= 0那么SUBSTR（ITEMS，J）

ELSE SUBSTR（项目，J，LOCATE（''，''，ITEMS，J ） - J）

END）

来自T3

我在哪里< 20

AND J< = LENGTH（ITEMS）

$

T2.ID，

T2。项目，

COUNT（*）作为计数
来自

T2 INNER JOIN T3 ON T2.ITEM = T3.ITEM
GROUP BY

T2.ID，

T2.ITEM;

请注意，递归T3 CTE中的I列仅包括在内以防止无限递归警告。 J列是有趣的
，它计算下一个子串的开始。

上述查询的结果如下：

ID项目数量
----------- ------ -----------

1电话3

2表1

3灯2

4电视2

5立体声1


5个记录被选中。

只是为了证明递归正确分割出ITEMS列，并观察J列递增，用主要的
查询替换SELECT ITEMS，J，ITEM FROM T3，结果变为：

ITEMS J ITEM
------------------ ----- ----------- -----------------------
手机，台灯，床，电视7手机
手机，表7手机
手机，台灯，电视，立体声7手机
手机，台灯，床，电视13灯
电话，表13表
手机，台灯，电视，立体声13灯
手机，台灯，床，电视18床
手机，台灯，电视，立体声17电视
手机，台灯，床，电视21电视
手机，台灯，电视，立体声24立体声


10条记录被选中。
<如果WHERE ID = 1，则更清楚一点。添加到T3查询的初始部分，在这种情况下你得到：

ITEMS J ITEM
-------------- --------- ----------- -----------------------
电话，灯泡，床，电视7电话
电话，台灯，床，电视13灯
电话，台灯，床，电视18床
电话，台灯，床，电视21电视


选择了4条记录。

欢呼，

戴夫。

Naturally... (not that I approve of such horrid structures ;-)

WITH
T1 (ID, ROOM, ITEMS) AS (
VALUES
(1, ''Bedroom'', ''Phone, Lamp, Bed, TV''),
(2, ''Kitchen'', ''Phone, Table''),
(3, ''LvRoom'', ''Phone, Lamp, TV, Stereo'')
),
T2 (ID, ITEM) AS (
VALUES
(1, ''Phone''),
(2, ''Table''),
(3, ''Lamp''),
(4, ''TV''),
(5, ''Stereo'')
),
T3 (ITEMS, I, J, ITEM) AS (
SELECT
ITEMS,
1,
CASE
WHEN LOCATE('','', ITEMS) = 0 THEN LENGTH(ITEMS) + 1
ELSE LOCATE('','', ITEMS) + 1
END,
TRIM(CASE
WHEN LOCATE('','', ITEMS) = 0 THEN ITEMS
ELSE SUBSTR(ITEMS, 1, LOCATE('','', ITEMS) - 1)
END)
FROM T1

UNION ALL

SELECT
ITEMS,
I + 1,
CASE
WHEN LOCATE('','', ITEMS, J) = 0 THEN LENGTH(ITEMS) + 1
ELSE LOCATE('','', ITEMS, J) + 1
END,
TRIM(CASE
WHEN LOCATE('','', ITEMS, J) = 0 THEN SUBSTR(ITEMS, J)
ELSE SUBSTR(ITEMS, J, LOCATE('','', ITEMS, J) - J)
END)
FROM T3
WHERE I < 20
AND J <= LENGTH(ITEMS)
)
SELECT
T2.ID,
T2.ITEM,
COUNT(*) AS COUNT
FROM
T2 INNER JOIN T3 ON T2.ITEM = T3.ITEM
GROUP BY
T2.ID,
T2.ITEM;

Note that the I column in the recursive T3 CTE is only included to
prevent infinite recursion warnings. The J column is the "interesting"
one which calculates the start of the next substring.

The result of the above query is as follows:

ID ITEM COUNT
----------- ------ -----------
1 Phone 3
2 Table 1
3 Lamp 2
4 TV 2
5 Stereo 1

5 record(s) selected.

Just to prove the recursion is splitting out the ITEMS column
correctly, and to observe the J column incrementing, replace the main
query with "SELECT ITEMS, J, ITEM FROM T3", and the result becomes:

ITEMS J ITEM
----------------------- ----------- -----------------------
Phone, Lamp, Bed, TV 7 Phone
Phone, Table 7 Phone
Phone, Lamp, TV, Stereo 7 Phone
Phone, Lamp, Bed, TV 13 Lamp
Phone, Table 13 Table
Phone, Lamp, TV, Stereo 13 Lamp
Phone, Lamp, Bed, TV 18 Bed
Phone, Lamp, TV, Stereo 17 TV
Phone, Lamp, Bed, TV 21 TV
Phone, Lamp, TV, Stereo 24 Stereo

10 record(s) selected.

It''s a bit clearer if "WHERE ID = 1" is added to the initial part of
the T3 query, in which case you get:

ITEMS J ITEM
----------------------- ----------- -----------------------
Phone, Lamp, Bed, TV 7 Phone
Phone, Lamp, Bed, TV 13 Lamp
Phone, Lamp, Bed, TV 18 Bed
Phone, Lamp, Bed, TV 21 TV

4 record(s) selected.

Cheers,

Dave.

-

通过DBMonster.com发布消息
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

一个问题戴夫。

TRIM正在做什么。我知道LTRIM，RTRIM。

感谢'的Leny G.


lenygold写道：

One question Dave.
What fumction TRIM is doing. I know LTRIM,RTRIM.
Thank''s Leny G.

lenygold wrote:

>谢谢Dave。
我们拥有世界上最好的董事会
干杯。 Leny G.

>Thank you Dave.
We have the best board in world
Cheers. Leny G.

>> [snip]

>>[snip]

>>还有另一个解决方案？

>>Is there is another solutions?

[引用文字剪辑--108行]

[quoted text clipped - 108 lines]

>>
戴夫。

>>
Dave.

-

通过DBMonster.com发布消息
http://www.dbmonster.com/Uwe/Forums....m-db2 / 200806/1

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

这篇关于向ORACLE板寻求帮助：匹配规范化和非规范化表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！