mysql - 具有多种数据类型的闭包表？

Question

我最近一直在复习我的 MySQL，我需要制作一个包含分层数据的数据库。

我有几种不同类型的数据需要以树格式表示，但不知道如何去做。

例如，假设我有一个人可以雇用或被其他人雇用。这些人可能每个人都有设备托运给他们，每件设备都必须有名称、描述和更换零件 list ，每个更换零件必须有成本等等。

我看到的大多数闭包表示例都集中在它们在处理论坛或线程评论方面有多棒。如何着手制作具有多种数据类型的闭包表？

Answer 1

这是一个简单而肮脏的例子:

select * from person

| pID | name      | employedBy |
+-----+-----------+------------+
|   1 | John Doe  |          2 |
|   2 | Joe Smith |       NULL |
|   3 | Meg Ryan  |          3 |

select * from equipment

| eqID | eqName   | eqDescription     | eqOwner | eqCheckedOutTo |
+------+----------+-------------------+---------+----------------+
|    1 | stuff    | just some stuff   |       3 |           NULL |
|    2 | table    | a table           |       1 |           NULL |
|    3 | computer | PC computer       |       3 |              2 |
|    4 | 3table   | table with 3 legs |       2 |           NULL |

select * from parts;

| partID | partName     | partCost |
+--------+--------------+----------+
|      1 | desktop1     |   499.99 |
|      2 | monitor13x13 |   109.95 |
|      3 | windows95    |    10.00 |
|      4 | speakers     |    30.00 |
|      5 | tabletop     |   189.99 |
|      6 | table leg    |    59.99 |

select * from equipmentParts

| epID | eqID | partID | quantity |
+------+------+--------+----------+
|    1 |    3 |      1 |        1 |
|    2 |    3 |      2 |        2 |
|    3 |    3 |      3 |        1 |
|    4 |    2 |      5 |        1 |
|    5 |    2 |      6 |        4 |
|    6 |    4 |      5 |        1 |
|    7 |    4 |      6 |        3 |

您可以像这样查询它们:

select name,eqName,e.eqID,partName,partCost,quantity,(quantity*partCost) AS totCost
from person p
inner join equipment e ON e.eqOwner=p.pID
inner join equipmentParts ep ON ep.eqID=e.eqID
inner join parts pa ON ep.partID=pa.partID

| name      | eqName   | eqID | partName     | partCost | quantity | totCost |
+-----------+----------+------+--------------+----------+----------+---------+
| John Doe  | table    |    2 | tabletop     |   189.99 |        1 |  189.99 |
| John Doe  | table    |    2 | table leg    |    59.99 |        4 |  239.96 |
| Meg Ryan  | computer |    3 | desktop1     |   499.99 |        1 |  499.99 |
| Meg Ryan  | computer |    3 | monitor13x13 |   109.95 |        2 |  219.90 |
| Meg Ryan  | computer |    3 | windows95    |    10.00 |        1 |   10.00 |
| Joe Smith | 3table   |    4 | tabletop     |   189.99 |        1 |  189.99 |
| Joe Smith | 3table   |    4 | table leg    |    59.99 |        3 |  179.97 |

或者总结一下每台设备的成本:

select name,eqName,sum(quantity*partCost) AS totCost
from person p
inner join equipment e ON e.eqOwner=p.pID
inner join equipmentParts ep ON ep.eqID=e.eqID
inner join parts pa ON ep.partID=pa.partID
group by e.eqID

| name      | eqName   | totCost |
+-----------+----------+---------+
| John Doe  | table    |  429.95 |
| Meg Ryan  | computer |  729.89 |
| Joe Smith | 3table   |  369.96 |