MySQL查询可以将行变成列吗?

MySQL查询可以将行变成列吗?,第1张

MySQL查询可以将行变成列吗?

有时,解决问题的第一步是知道它的名字。在那之后,这只是谷歌搜索的问题。您尝试创建的内容称为 数据透视表交叉表报表
。这是解释如何在MySQL中创建数据透视表的链接。这是更深入的教程。

更新:

现在,您已经更新了问题,对于您要完成的工作,我有了更清晰的认识。我将为您提供一个替代解决方案,该解决方案与基于MySQL的GROUP_CONCAT函数的相似但不 完全相同

select t1.FirstName, t1.LastName, group_concat(concat(t2.FirstName, ' ', t2.LastName))from member_information as t1left outer join member_dependent_information as t2 on t2.MemberID=t1.MemberIDgroup 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)


欢迎分享,转载请注明来源:内存溢出

原文地址: https://outofmemory.cn/zaji/5062605.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-11-15
下一篇 2022-11-16

发表评论

登录后才能评论

评论列表(0条)

保存