myql 查询树形表结果:说说、说说的评论、评论的回复
有三张表关联表:
用户的说说表(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、设计三张表及插入相关数据
ixt_customer_note 表结构: |
ixt_customer_note 表sql语句: DROP TABLE IF EXISTS `ixt_customer_note`; CREATE TABLE `ixt_customer_note` ( `id` varchar(50) NOT NULL COMMENT '主键UUID', `customerId` varchar(50) NOT NULL COMMENT '用户id', `content` varchar(500) NOT NULL COMMENT '说说内容', `createUser` varchar(50) DEFAULT NULL COMMENT '创建人ID', `createDate` datetime DEFAULT NULL COMMENT '创建时间', `updateUser` varchar(50) DEFAULT NULL COMMENT '更新人ID', `updateDate` datetime DEFAULT NULL COMMENT '更新时间', `deleteFlag` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标识:0未删除,1删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 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, ''); |
ixt_customer_note_comment 表结构: |
ixt_customer_note_comment 表sql语句: DROP TABLE IF EXISTS `ixt_customer_note_comment`; CREATE TABLE `ixt_customer_note_comment` ( `id` varchar(50) NOT NULL COMMENT '主键UUID', `customerId` varchar(50) NOT NULL COMMENT '评论用户ID', `dataId` varchar(50) NOT NULL COMMENT '被评论的说说ID', `content` varchar(1000) NOT NULL COMMENT '评论内容', `createUser` varchar(50) DEFAULT NULL COMMENT '创建人ID', `createDate` datetime DEFAULT NULL COMMENT '更新人ID', `updateUser` varchar(50) DEFAULT NULL COMMENT '更新时间', `updateDate` datetime DEFAULT NULL COMMENT '更新时间', `deleteFlag` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标识:0未删除,1删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `ixt_customer_note_comment` VALUES ('1111', 'a1', '1001', INSERT INTO `ixt_customer_note_comment` VALUES ('1212', 'a2', '1001', INSERT INTO `ixt_customer_note_comment` VALUES ('2121', 'b3', '1002', INSERT INTO `ixt_customer_note_comment` VALUES ('321', 'b1', '1002', INSERT INTO `ixt_customer_note_comment` VALUES ('3221', 'c1', '1000', INSERT INTO `ixt_customer_note_comment` VALUES ('421', 'b2', '1002', |
ixt_customer_note_reply 表结构: |
ixt_customer_note_reply 表sql语句: DROP TABLE IF EXISTS `ixt_customer_note_reply`; CREATE TABLE `ixt_customer_note_reply` ( `id` varchar(50) NOT NULL COMMENT '主键UUID', `customerId` varchar(50) NOT NULL COMMENT '回复用户id', `commentDataId` varchar(50) DEFAULT NULL COMMENT '被回复的评论ID', `parentReplyDataId` varchar(50) DEFAULT NULL COMMENT '被回复的回复的id', `content` varchar(1000) NOT NULL COMMENT '回复内容', `createUser` varchar(50) DEFAULT NULL COMMENT '创建人ID', `createDate` datetime DEFAULT NULL COMMENT '更新人ID', `updateUser` varchar(50) DEFAULT NULL COMMENT '更新时间', `updateDate` datetime DEFAULT NULL COMMENT '更新时间', `deleteFlag` bit(1) NOT NULL DEFAULT b'0' COMMENT '删除标识:0未删除,1删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `ixt_customer_note_reply` VALUES ('1212', 'v1', '3221', INSERT INTO `ixt_customer_note_reply` VALUES ('3121', 'v2', '3221', INSERT INTO `ixt_customer_note_reply` VALUES ('431', 'v3', '421', INSERT INTO `ixt_customer_note_reply` VALUES ('5231', 'v4', '421', |
2、分别查出三张表的数据:
2.1、查询用户说说表倒序显示
select createDate, id as dataId, customerId, concat('发表说说:',content) as content from ixt_customer_note order by createDate desc;
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;
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;
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;
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;
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;
myql 查询树形表结果:说说、说说的述评、评论的回复的更多相关文章
-
myql查询创建表语句SHOW CREATE TABLE table_name
技术背景:刚开始学习MySQL时候,有时偷懒,会用SHOW CREATE TABLE 表名\G来复制表创建语句,可是当运行的时候总会因为"表名和列名上有单引号",提示语法错误不能运 ...
-
oracle 树形表结构查询 排序
oracle 树形表结构排序 select * from Table start with parentid is null connect by prior id=parentid order SI ...
-
SQL Server 树形表非循环递归查询
很多人可能想要查询整个树形表关联的内容都会通过循环递归来查...事实上在微软在SQL2005或以上版本就能用别的语法进行查询,下面是示例. --通过子节点查询父节点WITH TREE AS( ...
-
sql查询指定表外键约束
//////////////////查询指定表外键约束select a.name as 约束名, object_name(b.parent_object_id) as 外键表, d.name as 外 ...
-
SQL查询每个表的字段数量
--SQL查询每个表的字段数量select b.[name], count(*) As AllCount,ISNULL(ISNULL(sum(case when isnullable=0 then 1 ...
-
SQL server 查询某个表在哪些存储过程(SP)中使用到
1.查询某个表被哪些存储过程(以下简称 SP)使用到 : select distinct object_name(id) from syscomments where id in (select ob ...
-
SQL 查询所有表名、字段名、类型、长度、存储过程、视图
-- 获得存储过程创建语句 select o.xtype,o.name,cm.text from syscomments cm inner join sysobjects o on o.id=cm.i ...
-
【转】oracle查询用户表,函数,储存过程,
◆Oracle查询用户表空间:select * from user_all_tables ◆Oracle查询所有函数和储存过程:select * from user_source ◆Oracle查询所 ...
-
SQL语句查询某表的所有字段及数据类型
SQL语句查询某表的所有字段及数据类型 SELECT name AS column_name , TYPE_NAME(system_type_id) AS column_type , max_leng ...
随机推荐
-
扩展 ASP.NET MVC 模型扩展 – ASP.NET MVC 4 系列
大部分人不能将核心运行时(System.Web 中的类)和 ASP.NET Web Forms 应用程序平台(System.Web.UI 中的类)区分开来. ASP.NET ...
-
Linux 下Valgrind 使用
Valgrind包括如下一些工具: Memcheck.这是valgrind应用最广泛的工具,一个重量级的内存检查器,能够发现开发中绝大多数内存错误使用情况,比如:使用未初始化的内存,使用已经释放了的内 ...
-
NYOJ-73 比大小 AC 分类: NYOJ 2014-01-17 21:29 195人阅读 评论(0) 收藏
典型的大数题目,这只是大数的比较,到时还有大数加减乘除,更加还有乘方,对于大数,一般用数组或者字符串,因为其他的结构类型一般都没有那么大 的范围!! 这道题目需要你仔细回想怎么比较俩个数字的大小,考虑 ...
-
React组件-mixin
一.组件 二.代码 <!DOCTYPE html> <html lang="zh-cn"> <head> <meta charset=&q ...
-
不要在精确计算中使用float和double类型
http://blog.csdn.net/androiddevelop/article/details/8478879 一 问题描述 float和double类型不能用于精确计算,其主要目的是为了科 ...
-
spring_boot打jar包及打包错误的解决方法
问题产生及解决 对于刚接手spring_boot的人,可能会感觉到很茫然无措,看到书上写的spring_boot是直接打包成jar包,使用java -jar *运行.然而却不知道该怎么打包.而创建sp ...
-
(八)python的简单数据类型和变量
什么是数据类型? 程序的本质就是驱使计算机去处理各种状态的变化,这些状态分为很多种. 例如英雄联盟游戏,一个人物角色有名字,钱,等级,装备等特性,大家第一时间会想到这么表示 名字:德玛西亚------ ...
-
深入理解Spring中bean的生命周期
[Spring中bean的生命周期] bean的生命周期 1.以ApplocationContext上下文单例模式装配bean为例,深入探讨bean的生命周期: (1).生命周期图: (2).具体事例 ...
-
Java并发编程总结4——ConcurrentHashMap在jdk1.8中的改进
一.简单回顾ConcurrentHashMap在jdk1.7中的设计 先简单看下ConcurrentHashMap类在jdk1.7中的设计,其基本结构如图所示: 每一个segment都是一个HashE ...
-
5.4Python数据处理篇之Sympy系列(四)---微积分
目录 目录 前言 (一)求导数-diff() 1.一阶求导-diff() 2.多阶求导-diff() 3.求偏导数-diff() (二)求积分-integrate() (三)求极限-limit() ( ...