myql 查询树形表结果：说说、说说的评论、评论的回复

叁歲伎倆 2022-04-12 10:49 208阅读 0赞

有三张表关联表：

用户的说说表（ixt\_customer\_note）

说说的评论表（ixt\_customer\_note\_comment）

评论的回复表（ixt\_customer\_note\_reply）

说说表保存的是用户发表的说说信息，评论表保存的是用户对说说发表的评论信息，回复表保存的是用户对评论及回复的回复

要求查询三张表返回结果为树形结构，如下：

发表说说:1003

发表说说:1002

发表评论:comment1002\_1

发表评论:comment1002\_2

发表回复:reply\_1002\_1

发表回复:reply\_1002\_2

发表评论:comment1002\_3

发表说说:1001

发表评论:comment1001\_1

发表评论:comment1001\_2

发表说说:1000

发表评论:comment1000\_1

发表回复:reply\_1000\_1

发表回复:reply\_1000\_2

1、设计三张表及插入相关数据

<table style="table-layout:fixed;min-width:400px;"> 
 <tbody> 
 <tr> 
 <td> ixt_customer_note&nbsp;表结构： <img src="http://dl2.iteye.com/upload/attachment/0112/3392/4874b78e-fa18-3e4a-950a-66bf02a14032.jpg" alt="" width="659" height="205"> </td> 
 </tr> 
 <tr> 
 <td> ixt_customer_note&nbsp;表sql语句： DROP TABLE IF EXISTS `ixt_customer_note`; CREATE TABLE `ixt_customer_note` ( &nbsp; `id` varchar(50) NOT NULL COMMENT '主键UUID', &nbsp; `customerId` varchar(50) NOT NULL COMMENT '用户id', &nbsp; `content` varchar(500) NOT NULL COMMENT '说说内容', &nbsp; `createUser` varchar(50) DEFAULT NULL COMMENT '创建人ID', &nbsp; `createDate` datetime DEFAULT NULL COMMENT '创建时间', &nbsp; `updateUser` varchar(50) DEFAULT NULL COMMENT '更新人ID', &nbsp; `updateDate` datetime DEFAULT NULL COMMENT '更新时间', &nbsp; `deleteFlag` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标识：0未删除，1删除', &nbsp; PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; &nbsp; INSERT INTO `ixt_customer_note` VALUES ('1000', 'user1', '1000', null, '2015-10-01 21:18:24', null, null, ''); INSERT INTO `ixt_customer_note` VALUES ('1001', 'user1', '1001', null, '2015-10-06 21:18:19', null, null, ''); INSERT INTO `ixt_customer_note` VALUES ('1002', 'user1', '1002', null, '2015-10-14 22:05:04', null, null, ''); INSERT INTO `ixt_customer_note` VALUES ('1003', 'user1', '1003', null, '2015-10-15 21:18:12', null, null, ''); </td> 
 </tr> 
 </tbody> 
</table>

<table style="table-layout:fixed;min-width:400px;"> 
 <tbody> 
 <tr> 
 <td> ixt_customer_note_comment&nbsp;表结构： <img src="http://dl2.iteye.com/upload/attachment/0112/3394/fccdf713-d12b-38be-a839-8f8a591495a8.jpg" alt="" width="655" height="221"> </td> 
 </tr> 
 <tr> 
 <td> ixt_customer_note_comment&nbsp;表sql语句： DROP TABLE IF EXISTS `ixt_customer_note_comment`; CREATE TABLE `ixt_customer_note_comment` ( &nbsp; `id` varchar(50) NOT NULL COMMENT '主键UUID', &nbsp; `customerId` varchar(50) NOT NULL COMMENT '评论用户ID', &nbsp; `dataId` varchar(50) NOT NULL COMMENT '被评论的说说ID', &nbsp; `content` varchar(1000) NOT NULL COMMENT '评论内容', &nbsp; `createUser` varchar(50) DEFAULT NULL COMMENT '创建人ID', &nbsp; `createDate` datetime DEFAULT NULL COMMENT '更新人ID', &nbsp; `updateUser` varchar(50) DEFAULT NULL COMMENT '更新时间', &nbsp; `updateDate` datetime DEFAULT NULL COMMENT '更新时间', &nbsp; `deleteFlag` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标识：0未删除，1删除', &nbsp; PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; &nbsp; INSERT INTO `ixt_customer_note_comment` VALUES ('1111', 'a1', '1001', 'comment1001_1', null, '2015-10-12 21:21:22', null, null, ''); INSERT INTO `ixt_customer_note_comment` VALUES ('1212', 'a2', '1001', 'comment1001_2', null, '2015-10-12 22:21:11', null, null, ''); INSERT INTO `ixt_customer_note_comment` VALUES ('2121', 'b3', '1002', 'comment1002_3', null, '2015-10-15 21:22:48', null, null, ''); INSERT INTO `ixt_customer_note_comment` VALUES ('321', 'b1', '1002', 'comment1002_1', null, '2015-10-14 21:21:59', null, null, ''); INSERT INTO `ixt_customer_note_comment` VALUES ('3221', 'c1', '1000', 'comment1000_1', null, '2015-10-02 21:23:19', null, null, ''); INSERT INTO `ixt_customer_note_comment` VALUES ('421', 'b2', '1002', 'comment1002_2', null, '2015-10-15 21:22:25', null, null, ''); </td> 
 </tr> 
 </tbody> 
</table>

<table style="table-layout:fixed;min-width:400px;"> 
 <tbody> 
 <tr> 
 <td> ixt_customer_note_reply&nbsp;表结构： <img src="http://dl2.iteye.com/upload/attachment/0112/3396/38da4cca-a1f7-3848-b58c-fed019ae5240.jpg" alt="" width="660" height="248"> </td> 
 </tr> 
 <tr> 
 <td> ixt_customer_note_reply&nbsp;表sql语句： DROP TABLE IF EXISTS `ixt_customer_note_reply`; CREATE TABLE `ixt_customer_note_reply` ( &nbsp; `id` varchar(50) NOT NULL COMMENT '主键UUID', &nbsp; `customerId` varchar(50) NOT NULL COMMENT '回复用户id', &nbsp; `commentDataId` varchar(50) DEFAULT NULL COMMENT '被回复的评论ID', &nbsp; `parentReplyDataId` varchar(50) DEFAULT NULL COMMENT '被回复的回复的id', &nbsp; `content` varchar(1000) NOT NULL COMMENT '回复内容', &nbsp; `createUser` varchar(50) DEFAULT NULL COMMENT '创建人ID', &nbsp; `createDate` datetime DEFAULT NULL COMMENT '更新人ID', &nbsp; `updateUser` varchar(50) DEFAULT NULL COMMENT '更新时间', &nbsp; `updateDate` datetime DEFAULT NULL COMMENT '更新时间', &nbsp; `deleteFlag` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标识：0未删除，1删除', &nbsp; PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; &nbsp; INSERT INTO `ixt_customer_note_reply` VALUES ('1212', 'v1', '3221', null, 'reply_1000_1', null, '2015-10-12 21:28:44', null, null, ''); INSERT INTO `ixt_customer_note_reply` VALUES ('3121', 'v2', '3221', '1212', 'reply_1000_2', null, '2015-10-13 21:28:49', null, null, ''); INSERT INTO `ixt_customer_note_reply` VALUES ('431', 'v3', '421', null, 'reply_1002_1', null, '2015-10-14 21:28:54', null, null, ''); INSERT INTO `ixt_customer_note_reply` VALUES ('5231', 'v4', '421', '431', 'reply_1002_2', null, '2015-10-15 21:28:57', null, null, ''); </td> 
 </tr> 
 </tbody> 
</table>

2、分别查出三张表的数据：

2.1、查询用户说说表倒序显示

select createDate, id as dataId, customerId, concat('发表说说:',content) as content from ixt\_customer\_note order by createDate desc;

![edba6c72-8130-3ae6-8ce4-84e3e6f29ea9.jpg][]

2.2、查询说说的评论正序显示

select nc.createDate, nc.dataId, nc.customerId, concat('发表评论:',nc.content) as content from ixt\_customer\_note\_comment nc left join ixt\_customer\_note n on nc.dataId=n.id order by n.createDate desc, nc.createDate asc;

![545a46fd-4fde-3adf-91f6-e41306b47468.jpg][]

2.3、查询说说的评论的回复正序显示

select nr.createDate, nc.dataId, nr.customerId, concat('发表回复:',nr.content) as content from ixt\_customer\_note\_reply nr left join ixt\_customer\_note\_comment nc on nr.commentDataId=nc.id left join ixt\_customer\_note n on nc.dataId=n.id order by n.createDate desc, nc.createDate asc, nr.createDate asc;

![dce0dadc-4b08-3eb3-be1f-e426afe1ba0d.jpg][]

3、有了这三张表数据后，如何将他们显示为一张表，最终得到树形结构？

如果想要得到树形展示，可以考虑能否将三张表返回的结果合并为一张表，因为他们的结果合并在一起后正是我们需要的所有数据，只不过最终展示的效果要调整一下。

好的，先考虑合并用户说说及说说的评论，并按树形结构展示，这时我们应该使用 union关键字，求并集。观察一下，合并之后的结果集，应该先根据说说的发表时间倒序，再根据说说的评论的发表时间正序，所以写sql执行一下：

大致的语句为：select \* from(说说的结果集 union 评论的结果集) as T order by 说说.createDate desc, 评论.createDate asc;

select \* from((select createDate as createDate1, "" as createDate2, id as dataId, customerId, concat('发表说说:',content) as content from ixt\_customer\_note order by createDate desc) union (select n.createDate as createDate1, nc.createDate as createDate2, nc.dataId, nc.customerId, concat('    发表评论:',nc.content) as content from ixt\_customer\_note\_comment nc left join ixt\_customer\_note n on nc.dataId=n.id  order by n.createDate desc, nc.createDate asc)) as T order by createDate1 desc, createDate2 asc;

![266f79de-b8db-3f03-9d50-92ead18a587c.jpg][]

4、上面合并结果集是我们想要的结果，好的，再来合并回复结果集。合并之后的结果集应该按说说的发表时间倒序，再按评论的发表时间正序，再按回复的发表时间正序。为了区分出每条记录是哪张表的，我们在结果集中添加一个字段type，表示记录的类型：t1是说说，t2是评论，t3是回复。

sql语句：select \* from(说说的结果集 union 评论的结果集 union 回复的结果集) as T order by 说说.createDate desc, 评论.createDate asc, 回复.createDate asc;

select \* from((select createDate as createDate1, "" as createDate2, "" as createDate3, "t1" as type, id as dataId, customerId, concat('发表说说:',content) as content from ixt\_customer\_note order by createDate desc) union (select n.createDate as createDate1, nc.createDate as createDate2, "" as createDate3, "t2" as type, nc.dataId, nc.customerId, concat('    发表评论:',nc.content) as content from ixt\_customer\_note\_comment nc left join ixt\_customer\_note n on nc.dataId=n.id  order by n.createDate desc, nc.createDate asc) union (select n.createDate as createDate1, nc.createDate as createDate2, nr.createDate as createDate3, "t3" as type, nc.dataId, nr.customerId, concat('        发表回复:',nr.content) as content from ixt\_customer\_note\_reply nr left join ixt\_customer\_note\_comment nc on nr.commentDataId=nc.id left join ixt\_customer\_note n on nc.dataId=n.id order by n.createDate desc, nc.createDate asc, nr.createDate asc)) as T order by createDate1 desc, createDate2 asc, createDate3 asc;

![6be67f4a-5355-3283-acd7-b96c31286fb9.jpg][]

5、上面结果集是我们想要的，不过createDate最终应该只有一个，可以继续改进，将createDate合并为一列，说说显示createDate1，评论显示createDate2，回复显示createDate3。

改进后的语句如下：

select if(T.type='t1',T.createDate1,(if(T.type='t2',T.createDate2,T.createDate3))) as createDate, T.type, T.dataId, T.customerId, T.content from((select createDate as createDate1, "" as createDate2, "" as createDate3, "t1" as type,customerId, id as dataId, concat('发表说说:',content) as content from ixt\_customer\_note order by createDate desc) union (select n.createDate as createDate1, nc.createDate as createDate2, "" as createDate3, "t2" as type, nc.customerId, nc.dataId, concat('    发表评论:',nc.content) as content from ixt\_customer\_note\_comment nc left join ixt\_customer\_note n on nc.dataId=n.id  order by n.createDate desc, nc.createDate asc) union (select n.createDate as createDate1, nc.createDate as createDate2, nr.createDate as createDate3, "t3" as type, nr.customerId, nc.dataId, concat('        发表回复:',nr.content) as content from ixt\_customer\_note\_reply nr left join ixt\_customer\_note\_comment nc on nr.commentDataId=nc.id left join ixt\_customer\_note n on nc.dataId=n.id order by n.createDate desc, nc.createDate asc, nr.createDate asc)) as T order by createDate1 desc, createDate2 asc, createDate3 asc;

![e968b824-f142-3e0c-a9c4-c1f5a7f08d82.jpg][]

好的，到此content返回的结果为我们最终想要的结果了！

6、补充：如果要根据说说id求出对应的评论及回复列表：

求说说id=1000的评论及回复sql语句如下：

select if(T.type='t2',T.createDate2,T.createDate3) as createDate, T.type, T.dataId, T.customerId, T.content from((select nc.createDate as createDate2, "" as createDate3, "t2" as type, nc.customerId, nc.dataId, concat('发表评论:',nc.content) as content from ixt\_customer\_note\_comment nc where nc.dataId='1000' order by nc.createDate asc) union (select nc.createDate as createDate2, nr.createDate as createDate3, "t3" as type, nr.customerId, nc.dataId, concat('    发表回复:',nr.content) as content from ixt\_customer\_note\_reply nr left join ixt\_customer\_note\_comment nc on nr.commentDataId=nc.id where nc.dataId='1000' order by nc.createDate asc, nr.createDate asc)) as T order by createDate2 asc, createDate3 asc;

![4ec4022b-f30d-30ad-bcc7-8e15ea07937e.jpg][]

[edba6c72-8130-3ae6-8ce4-84e3e6f29ea9.jpg]: /images/20220412/e9f26dcda5654b819750a720634f9062.png
[545a46fd-4fde-3adf-91f6-e41306b47468.jpg]: /images/20220412/71ebf4420239436ca1e4745313b6fd1e.png
[dce0dadc-4b08-3eb3-be1f-e426afe1ba0d.jpg]: /images/20220412/510d261d140e4f8c936fc51e340319e8.png
[266f79de-b8db-3f03-9d50-92ead18a587c.jpg]: /images/20220412/7459c8ee18d64e339b30f6b4a24bb0db.png
[6be67f4a-5355-3283-acd7-b96c31286fb9.jpg]: /images/20220412/7fcb715a792047a5bfc949a0c9e1d729.png
[e968b824-f142-3e0c-a9c4-c1f5a7f08d82.jpg]: /images/20220412/cccff181b5aa48c5877a1e8550cea0fd.png
[4ec4022b-f30d-30ad-bcc7-8e15ea07937e.jpg]: /images/20220412/69d2cfb396184516b992a7d62a3b7a7d.png