我有一个从应用程序中提取的数据库(我没有创建它)。我需要提取数据以在其他应用程序中使用。主表包含3个外键,每个外键都链接到其他表。我想用其他表中的特定列值替换FK ID。主表如下所示:
数据:
+------------+------------+--------+----------+-----------+----------+------+
| startdate | enddate | status | employee | cause | duration | type |
+------------+------------+--------+----------+-----------+----------+------+
| 2018-01-05 | 2018-01-07 | 2 | 1 | some text | 3 | 1 |
+------------+------------+--------+----------+-----------+----------+------+
| 2018-02-03 | 2018-02-04 | 1 | 2 | some text | 2 | 2 |
+------------+------------+--------+----------+-----------+----------+------+
状态,员工和类型都是外键。它们链接到具有以下布局的表:
用户:
+----+-----------+----------+
| id | firstname | lastname |
+----+-----------+----------+
| 1 | John | Doe |
+----+-----------+----------+
| 2 | Dylan | Smith |
+----+-----------+----------+
类型:
+----+-----------+
| id | name |
+----+-----------+
| 1 | Annual |
+----+-----------+
| 2 | Quarterly |
+----+-----------+
状态:
+----+----------+
| id | name |
+----+----------+
| 1 | Accepted |
+----+----------+
| 2 | Rejected |
+----+----------+
我希望主表看起来像这样
+------------+------------+-----------+--------------+-----------+----------+-----------+
| startdate | enddate | status | employee | cause | duration | type |
+------------+------------+-----------+--------------+-----------+----------+-----------+
| 2018-01-05 | 2018-01-07 | Rejected | John Doe | some text | 3 | Annual |
+------------+------------+-----------+--------------+-----------+----------+-----------+
| 2018-02-03 | 2018-02-04 | Accepted | Dylan Smith | some text | 2 | Quarterly |
+------------+------------+-----------+--------------+-----------+----------+-----------+
我已经尝试过在Google以及SO上找到的各种功能,但是我无法以我想要的方式实现它。我来自PHP,但是我有一些SQL方面的经验,但是我不是专家。我尝试获取与ID对应的值并将其保存在其他表中,但是我不知道如何使用这些返回值更新表:
INSERT into stat
SELECT b.name stat
FROM data a JOIN status b
ON a.status = b.id
这将在称为“ stat”的表中以正确的顺序创建具有正确值的列,如下所示:
+----------+
| status |
+----------+
| Rejected |
+----------+
| Accepted |
+----------+
对于“用户”(我也将名字和姓氏串联到“用户”表中的“名称”列中。我想使用串联值)和“类型”表,其工作方式类似。如何用上表中的相应值更新“数据”表中的每个FK值?
从data
表连接到其他三个表应该在这里起作用:
SELECT
d.startdate,
d.enddate,
s.name AS status,
CONCAT(u.firstname, ' ', u.lastname) AS employee,
d.cause,
d.duration,
t.name AS type
FROM data d
LEFT JOIN status s
ON d.status = s.id
LEFT JOIN users u
ON d.employee = u.id
LEFT JOIN types t
ON d.type = t.id;
如果缺少来自联接之一的给定值,并且您想显示一个占位符值(而不是default NULL
),则可以使用该COALESCE
函数。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句