gpt4 book ai didi

MySQL - 在 2 个表上旋转一对多关系

转载 作者:行者123 更新时间:2023-11-30 21:59:43 24 4
gpt4 key购买 nike

我需要旋转一个表,它可以为一个 View 完成,也可以将数据转储到一个已经设置为旋转表的预先存在的表中。我的主要问题是了解如何选择和转换数据。

示例数据

表 1:

user_id       user_email       first_name       last_name
-------------------------------------------------------------
1 jdoe@huh.com John Doe
2 jcarter@aol.com Jimmy Carter

表 2:

user_id       type       job_title       job_description
---------------------------------------------------------
1 Job 1 Some Job play all day!
1 Job 2 Another Job all work no play!
2 Job 1 Clown IT's not funny

透视表
我想要实现的是:
为了便于使用,我缩短了表名和数据。

uid    email    fname    lname    jtitle-1    jdesc-1    jtitle-2    jdesc-2
----------------------------------------------------------------------------
1 jdoe@> John Doe Some Job play all> Another J> all wo>
2 jcar@> Jimmy Carter Clown IT's not> null null

我四处寻找了一些例子,但没有一个是我正在寻找的。

MySQL pivot table
http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/


更新
自然地,在发布这篇文章后不久我就找到了解决方案。
http://stratosprovatopoulos.com/web-development/mysql/pivot-table-with-dynamic-columns/

这样就可以了
MAX(IF(pa.fieldname = ‘size’, pa.fieldvalue, NULL)) AS 大小

最佳答案

您正在寻找的是 Joe Celko(Smarties 的 SQL)风格的枢轴,通过对主表的主键进行分组并根据相对“类型”有选择地挑选表 2 值.

SELECT u.user_id AS uid,
u.user_email AS email,
u.first_name AS fname,
u.last_name AS lname,
MAX(CASE WHEN j.type = 'Job 1' THEN j.job_title ELSE NULL END) AS `jtitle-1`,
MAX(CASE WHEN j.type = 'Job 1' THEN j.job_description ELSE NULL END) AS `jdesc-1`,
MAX(CASE WHEN j.type = 'Job 2' THEN j.job_title ELSE NULL END) AS `jtitle-2`,
MAX(CASE WHEN j.type = 'Job 2' THEN j.job_description ELSE NULL END) AS `jdesc-2`,
MAX(CASE WHEN j.type = 'Job 3' THEN j.job_title ELSE NULL END) AS `jtitle-3`,
MAX(CASE WHEN j.type = 'Job 3' THEN j.job_description ELSE NULL END) AS `jdesc-3`,
MAX(CASE WHEN j.type = 'Job 4' THEN j.job_title ELSE NULL END) AS `jtitle-4`,
MAX(CASE WHEN j.type = 'Job 4' THEN j.job_description ELSE NULL END) AS `jdesc-4`,
MAX(CASE WHEN j.type = 'Job 5' THEN j.job_title ELSE NULL END) AS `jtitle-5`,
MAX(CASE WHEN j.type = 'Job 5' THEN j.job_description ELSE NULL END) AS `jdesc-5`
FROM users AS u
LEFT JOIN jobs AS j ON j.user_id = u.user_id
GROUP BY u.user_id;

关于MySQL - 在 2 个表上旋转一对多关系,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43768822/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com