我有很多表要与联接合并,但是这样,结果将返回许多行,而我希望将它们作为新列生成。
MemberID | FirstName | LastName
---------------------------------
1 | John | Harris
2 | Sarah | Thompson
3 | Zack | Lewis
MemberID | FirstName | LastName | Type
---------------------------------------
1 | Amy | Harris | 1
2 | Bryan | Thompson | 1
2 | Dewey | Thompson | 2
2 | Tom | Thompson | 2
3 | Harry | Lewis | 2
3 | Minka | Lewis | 1
SELECT
t1.FirstName,
t1.LastName,
t1.MemberID,
IF(t2.Type = '1',CONCAT(t2.FirstName,' ',t2.LastName),'') AS Spouse_Name,
IF(t2.Type = '2',CONCAT(t2.FirstName,' ',t2.LastName),'') AS Child_Name,
FROM
member_dependent_information t2
INNER JOIN
member_information t1
USING
(MemberID)
ORDER BY
t1.LastName ASC,
t1.MemberID ASC;
MemberID | FirstName | LastName | Spouse_Name | Child_Name1 | Child_Name2
--------------------------------------------------------------------------------
1 | John | Harris | Amy Harris | NULL | NULL
2 | Sarah | Thompson | Bryan Thompson | Dewey Thompson | Tom Thompson
3 | Zack | Lewis | Mika Lewis | Harry Lewis | NULL
MemberID | FirstName | LastName | Spouse_Name | Child_Name
-------------------------------------------------------------------
1 | John | Harris | Amy Harris | NULL
2 | Sarah | Thompson | Bryan Thompson | NULL
2 | Sarah | Thompson | NULL | Dewey Thompson
2 | Sarah | Thompson | NULL | Tom Thompson
3 | Zack | Lewis | Mika Lewis | NULL
3 | Zack | Lewis | NULL | Harry Lewis
虽然我的查询在多行中返回“正确”数据,但并没有根据需要将结果合并为一行。
下面已经提到了有关数据透视表/交叉表的建议,但是我能够找到的每个参考都建议使用数学计算,或者知道要返回的字段数。我将不知道此信息,因为单个成员最多可能有100个受抚养人(尽管更像是4-8)
我觉得我越来越接近最终解决方案。我在查询中添加了GROUP_CONCAT函数,该函数在单个列中返回所有名字,在单个列中返回所有姓氏,但仍然需要将它们分解为自己的单独列。
新功能是:
SELECT
t1.MemberID,
t1.FirstName,
t1.LastName,
GROUP_CONCAT(t2.FirstName) AS Dep_Firstnames,
GROUP_CONCAT(t2.LastName) AS Dep_LastNames
FROM
member_information t1
LEFT OUTER JOIN member_dependent_information t2
ON t1.MemberID = t2.MemberID
WHERE
t1.Status = 1
GROUP BY
t1.MemberID
有时,解决问题的第一步是知道它的名字。在那之后,这只是谷歌搜索的问题。您尝试创建的内容称为 数据透视表 或 交叉表报表
。这是解释如何在MySQL中创建数据透视表的链接。这是更深入的教程。
现在,您已经更新了问题,对于您要完成的工作,我有了更清晰的认识。我将为您提供一个替代解决方案,该解决方案与基于MySQL的GROUP_CONCAT函数的相似但不 完全相同
。
select t1.FirstName, t1.LastName, group_concat(concat(t2.FirstName, ' ', t2.LastName))
from member_information as t1
left outer join member_dependent_information as t2 on t2.MemberID=t1.MemberID
group by t1.MemberID;
我已验证此查询,如下所示。首先设置:
create table member_information (
MemberID int unsigned auto_increment primary key,
FirstName varchar(32) not null,
LastName varchar(32) not null
) engine=innodb;
create table member_dependent_information (
MemberID int unsigned not null,
FirstName varchar(32) not null,
LastName varchar(32) not null,
Type int unsigned not null,
foreign key (MemberID) references member_information(MemberID)
) engine=innodb;
insert into member_information (MemberID, FirstName, LastName) values
(1, 'John', 'Harris'),
(2, 'Sarah', 'Thompson'),
(3, 'Zack', 'Lewis');
insert into member_dependent_information (MemberID, FirstName, LastName, `Type`) values
(1, 'Amy', 'Harris', 1),
(2, 'Bryan', 'Thompson', 1),
(2, 'Dewey', 'Thompson', 2),
(2, 'Tom', 'Thompson', 2),
(3, 'Harry', 'Lewis', 2),
(3, 'Minka', 'Lewis', 1);
现在查询和结果:
mysql> select t1.FirstName, t1.LastName, group_concat(concat(t2.FirstName, ' ', t2.LastName))from member_information as t1
-> left outer join member_dependent_information as t2 on t2.MemberID=t1.MemberID
-> group by t1.MemberID;
+-----------+----------+------------------------------------------------------+
| FirstName | LastName | group_concat(concat(t2.FirstName, ' ', t2.LastName)) |
+-----------+----------+------------------------------------------------------+
| John | Harris | Amy Harris |
| Sarah | Thompson | Bryan Thompson,Dewey Thompson,Tom Thompson |
| Zack | Lewis | Harry Lewis,Minka Lewis |
+-----------+----------+------------------------------------------------------+
3 rows in set (0.00 sec)
问题内容: 我有一个简单的查询,它产生以下结果: 我想将行转置为列。 我了解mysql没有枢轴功能,因此需要工会,但不能100%确定。 先谢谢您的帮助。 问题答案: 您可以使用这样的交叉表来做到这一点- 另外,您应该将transporttype值移动到查找表中,并在该表中包含transporttype_id。
问题内容: MySQL是否可以将列转换为行,从而动态添加行所需的列数。我认为我的问题可能与数据透视表有关,但是我不确定,除了给出以下示例之外,我不知道如何构造此问题。 给定两个表A和B,它们看起来像 表A 我想编写一个如下查询: 结果表 基本上,我想将表B中的每一行变成结果表中的一列。如果有一个新条目被添加到表B中,id = 1,那么我希望结果表自动扩展一列以容纳这个额外的数据点。 问题答案: 您
问题内容: 一个菜鸟MYSQL用户....我有一个简单的MySQL查询,该查询返回值,并使用GROUP_CONCAT函数: 但是,我需要转置查询,以便它以单独的列而不是行的形式返回“名称”。MySQL完全有可能吗? 问题答案: 您需要执行一项操作,MySQL本身不支持该操作(与某些其他RDBMS不同)。 您可以获得的最接近的结果是按照以下几行构造SQL: 如果可能的值是动态的,则可以从以下结果中以
问题内容: 我有一张桌子:student_marks 预期产量: 使用mysql用户定义的变量,可以使用查询来完成: 在不使用用户定义变量的情况下,有什么方法可以在msyql中实现? 问题答案: 基于您不想使用用户定义变量的原因,因为要避免有两个查询,一个用于初始化,一个用于使用它,您可以使用以下方法:
问题内容: 我正在处理数据透视表查询。架构如下 Sno,名称,地区 同一名称可能在许多地区出现,例如以样本数据为例 如您所见,我有一组4个不同的地区(CA,JB,MN,LP)。现在,我想通过将名称映射到区域来获取为其生成的数据透视表 我为此写了以下查询 但是,地区可能会增加,在这种情况下,我将不得不再次手动编辑查询并向其中添加新的地区。 我想知道是否存在一个查询,该查询可以动态获取不同地区的名称并
问题内容: 我正在寻找一种方法来限制mysql服务器上查询的最大运行时间。我认为可以通过配置文件完成此操作,但在文档中找不到任何相关内容。有人知道这是否可以做到吗?谢谢。 问题答案: 将查询发送到服务器以运行时,无法指定最大运行时间。 但是,在数据库服务器上每秒运行一次cron作业并连接并执行以下操作并不少见: 显示流程清单 查找查询时间大于您的最大期望时间的所有连接 对每个进程运行KILL [进