原文：https://www.iteye.com/blog/mxskymx-2249326

表结构

用户的说说表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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=utf8mb4;


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, '');

说说的评论表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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=utf8mb4;

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, '');

评论的回复表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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=utf8mb4;

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, '');

查出三张表的数据

查询用户说说表倒序显示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
	createDate,
	id AS dataId,
	customerId,
	concat( '发表说说:', content ) AS content 
FROM
	ixt_customer_note 
ORDER BY
	createDate DESC;
	
+---------------------+--------+------------+---------------+
| createDate          | dataId | customerId | content       |
+---------------------+--------+------------+---------------+
| 2015-10-15 21:18:12 | 1003   | user1      | 发表说说:1003 |
| 2015-10-14 22:05:04 | 1002   | user1      | 发表说说:1002 |
| 2015-10-06 21:18:19 | 1001   | user1      | 发表说说:1001 |
| 2015-10-01 21:18:24 | 1000   | user1      | 发表说说:1000 |
+---------------------+--------+------------+---------------+

查询说说的评论正序显示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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;
	
+---------------------+--------+------------+------------------------+
| createDate          | dataId | customerId | content                |
+---------------------+--------+------------+------------------------+
| 2015-10-14 21:21:59 | 1002   | b1         | 发表评论:comment1002_1 |
| 2015-10-15 21:22:25 | 1002   | b2         | 发表评论:comment1002_2 |
| 2015-10-15 21:22:48 | 1002   | b3         | 发表评论:comment1002_3 |
| 2015-10-12 21:21:22 | 1001   | a1         | 发表评论:comment1001_1 |
| 2015-10-12 22:21:11 | 1001   | a2         | 发表评论:comment1001_2 |
| 2015-10-02 21:23:19 | 1000   | c1         | 发表评论:comment1000_1 |
+---------------------+--------+------------+------------------------+

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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;
	
+---------------------+--------+------------+-----------------------+
| createDate          | dataId | customerId | content               |
+---------------------+--------+------------+-----------------------+
| 2015-10-14 21:28:54 | 1002   | v3         | 发表回复:reply_1002_1 |
| 2015-10-15 21:28:57 | 1002   | v4         | 发表回复:reply_1002_2 |
| 2015-10-12 21:28:44 | 1000   | v1         | 发表回复:reply_1000_1 |
| 2015-10-13 21:28:49 | 1000   | v2         | 发表回复:reply_1000_2 |
+---------------------+--------+------------+-----------------------+

联合三张表查询

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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;

+---------------------+---------------------+--------+------------+----------------------------+
| createDate1         | createDate2         | dataId | customerId | content                    |
+---------------------+---------------------+--------+------------+----------------------------+
| 2015-10-15 21:18:12 |                     | 1003   | user1      | 发表说说:1003              |
| 2015-10-14 22:05:04 |                     | 1002   | user1      | 发表说说:1002              |
| 2015-10-14 22:05:04 | 2015-10-14 21:21:59 | 1002   | b1         |     发表评论:comment1002_1 |
| 2015-10-14 22:05:04 | 2015-10-15 21:22:25 | 1002   | b2         |     发表评论:comment1002_2 |
| 2015-10-14 22:05:04 | 2015-10-15 21:22:48 | 1002   | b3         |     发表评论:comment1002_3 |
| 2015-10-06 21:18:19 |                     | 1001   | user1      | 发表说说:1001              |
| 2015-10-06 21:18:19 | 2015-10-12 21:21:22 | 1001   | a1         |     发表评论:comment1001_1 |
| 2015-10-06 21:18:19 | 2015-10-12 22:21:11 | 1001   | a2         |     发表评论:comment1001_2 |
| 2015-10-01 21:18:24 |                     | 1000   | user1      | 发表说说:1000              |
| 2015-10-01 21:18:24 | 2015-10-02 21:23:19 | 1000   | c1         |     发表评论:comment1000_1 |
+---------------------+---------------------+--------+------------+----------------------------+

查询结果排序

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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;

优化查询结果集

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
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;

+---------------------+------+--------+------------+-------------------------------+
| createDate          | type | dataId | customerId | content                       |
+---------------------+------+--------+------------+-------------------------------+
| 2015-10-15 21:18:12 | t1   | 1003   | user1      | 发表说说:1003                 |
| 2015-10-14 22:05:04 | t1   | 1002   | user1      | 发表说说:1002                 |
| 2015-10-14 21:21:59 | t2   | 1002   | b1         |     发表评论:comment1002_1    |
| 2015-10-15 21:22:25 | t2   | 1002   | b2         |     发表评论:comment1002_2    |
| 2015-10-14 21:28:54 | t3   | 1002   | v3         |         发表回复:reply_1002_1 |
| 2015-10-15 21:28:57 | t3   | 1002   | v4         |         发表回复:reply_1002_2 |
| 2015-10-15 21:22:48 | t2   | 1002   | b3         |     发表评论:comment1002_3    |
| 2015-10-06 21:18:19 | t1   | 1001   | user1      | 发表说说:1001                 |
| 2015-10-12 21:21:22 | t2   | 1001   | a1         |     发表评论:comment1001_1    |
| 2015-10-12 22:21:11 | t2   | 1001   | a2         |     发表评论:comment1001_2    |
| 2015-10-01 21:18:24 | t1   | 1000   | user1      | 发表说说:1000                 |
| 2015-10-02 21:23:19 | t2   | 1000   | c1         |     发表评论:comment1000_1    |
| 2015-10-12 21:28:44 | t3   | 1000   | v1         |         发表回复:reply_1000_1 |
| 2015-10-13 21:28:49 | t3   | 1000   | v2         |         发表回复:reply_1000_2 |
+---------------------+------+--------+------------+-------------------------------+

说说id查询评论及回复列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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;

+---------------------+------+--------+------------+---------------------------+
| createDate          | type | dataId | customerId | content                   |
+---------------------+------+--------+------------+---------------------------+
| 2015-10-02 21:23:19 | t2   | 1000   | c1         | 发表评论:comment1000_1    |
| 2015-10-12 21:28:44 | t3   | 1000   | v1         |     发表回复:reply_1000_1 |
| 2015-10-13 21:28:49 | t3   | 1000   | v2         |     发表回复:reply_1000_2 |
+---------------------+------+--------+------------+---------------------------+