MYQL Query Tree Table results: remarks, comments, comment reply

MYQL Query Tree Table results: Say, say the comments, comments reply

There are three table association tables:

User's speaking table (Ixt_customer_note)

Talk about the comment form (ixt_customer_note_comment)

Reply form for Comments (ixt_customer_note_reply)

To say that the table is saved by the user to say the message, the comment table is saved by the user to say the comments published, reply to the table is to save the comments and replies to the reply of the user

Request Query Three table returns the result as a tree structure, as follows:

Published say: 1003

Published say: 1002

Post a comment: comment1002_1

Post a comment: comment1002_2

Post reply: Reply_1002_1

Post reply: reply_1002_2

Post a comment: Comment1002_3

Published say: 1001

Post a comment: comment1001_1

Post a comment: comment1001_2

Published say: 1000

Post a comment: comment1000_1

Post reply: reply_1000_1

Post reply: reply_1000_2

1. Design three sheets and insert relevant data

Ixt_customer_note table structure:

Ixt_customer_note Table SQL statements: DROP TABLE IF EXISTS ' ixt_customer_note '; CREATE TABLE ' Ixt_customer_note ' ( ' ID ' varchar (not NULL COMMENT ' primary key uuid '), ' customerId ' varchar (not NULL COMMENT ' user ID '), ' Content ' varchar ($) Not NULL COMMENT ' to say something ', ' CreateUser ' varchar (+) DEFAULT NULL COMMENT ' creator ID ', ' CreateDate ' datetime DEFAULT NULL COMMENT ' creation time ', ' UpdateUser ' varchar (+) DEFAULT NULL COMMENT ' update Person ID ', ' Updatedate ' datetime DEFAULT NULL COMMENT ' Update Time ', ' Deleteflag ' bit (1) not NULL DEFAULT B ' 0 ' COMMENT ' Delete identity: 0 not deleted, 1 deleted ', PRIMARY KEY (' id ') ) Engine=innodb DEFAULT Charset=utf8; INSERT into ' ixt_customer_note ' VALUES (' n ', ' user1 ', ' n ', 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 table structure:

Ixt_customer_note_comment Table SQL statements: DROP TABLE IF EXISTS ' ixt_customer_note_comment '; CREATE TABLE ' ixt_customer_note_comment ' ( ' ID ' varchar (not NULL COMMENT ' primary key uuid '), ' customerId ' varchar (+) not NULL COMMENT ' comment User ID ', ' Dataid ' varchar (a) Not NULL COMMENT ' commented id ', ' Content ' varchar (+) not NULL COMMENT ' comments ', ' CreateUser ' varchar (+) DEFAULT NULL COMMENT ' creator ID ', ' CreateDate ' datetime DEFAULT NULL COMMENT ' update Person ID ', ' updateUser ' varchar DEFAULT NULL COMMENT ' Update Time ', ' Updatedate ' datetime DEFAULT NULL COMMENT ' Update Time ', ' Deleteflag ' bit (1) not NULL DEFAULT B ' 0 ' COMMENT ' Delete identity: 0 not deleted, 1 deleted ', PRIMARY KEY (' id ') ) Engine=innodb DEFAULT Charset=utf8; 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, n Ull, "); INSERT into ' ixt_customer_note_comment ' VALUES (' 3221 ', ' C1 ', ' + ', ' 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, n Ull, ");

Ixt_customer_note_reply table structure:

ixt_customer_note_reply Table SQL statements: DROP TABLE IF EXISTS ' ixt_customer_note_reply '; CREATE TABLE ' ixt_customer_note_reply ' ( ' ID ' varchar (not NULL COMMENT ' primary key uuid '), ' customerId ' varchar (not NULL COMMENT ' reply to User ID '), ' commentdataid ' varchar DEFAULT NULL COMMENT ' commented id ' replied, ' Parentreplydataid ' varchar () DEFAULT NULL COMMENT ' The ID of the reply replied to ', ' Content ' varchar (+) not NULL COMMENT ' reply contents ', ' CreateUser ' varchar (+) DEFAULT NULL COMMENT ' creator ID ', ' CreateDate ' datetime DEFAULT NULL COMMENT ' update Person ID ', ' updateUser ' varchar DEFAULT NULL COMMENT ' Update Time ', ' Updatedate ' datetime DEFAULT NULL COMMENT ' Update Time ', ' Deleteflag ' bit (1) not NULL DEFAULT B ' 0 ' COMMENT ' Delete identity: 0 not deleted, 1 deleted ', PRIMARY KEY (' id ') ) Engine=innodb DEFAULT Charset=utf8; INSERT into ' ixt_customer_note_reply ' VALUES (' 1212 ', ' v1 ', ' 3221 ', null, ' reply_1000_1 ', null, ' 2015-10-12 21:28:44 ', nul L, NULL, '); INSERT into ' ixt_customer_note_reply ' VALUES (' 3121 ', ' v2 ', ' 3221 ', ' 1212 ', ' reply_1000_2 ', null, ' 2015-10-13 21:28:49 ', n ull, 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 ', nul L, NULL, ');

2. Find out the data of three tables respectively:

2.1, inquires the user to say the table reverse display

Select CreateDate, id as Dataid, customerId, concat (' Publish said: ', content) as content from Ixt_customer_note order by CreateDate Desc

2.2, the query said comments positive sequence display

Select Nc.createdate, Nc.dataid, Nc.customerid, concat (' Comment: ', nc.content) as content from Ixt_customer_note_comment NC Left JOIN Ixt_customer_note N in nc.dataid=n.id order by n.createdate DESC, nc.createdate ASC;

2.3. The reply of the comment that the query says is positive sequence display

Select Nr.createdate, Nc.dataid, Nr.customerid, concat (' Post reply: ', 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. With these three table data, how do you show them as a table and eventually get a tree structure?

If you want to get a tree display, consider whether you can combine the results returned by three tables into a single table, because their results are all the data we need after they are merged together, but the effect of the final display has to be adjusted.

OK, first of all, consider the comments of the merged user, and the tree structure, then we should use the Union keyword, and set. Observe that after merging the result set, should first according to say the publication time in reverse, then according to say the comment of the publication time is positive order, so write SQL execution:

The approximate statement is: SELECT * FROM (the result set of the result set Union comment) as T order by says. CreateDate Desc, comments. createdate ASC;

SELECT * FROM ((select CreateDate as CreateDate1, "" "as CreateDate2, id as Dataid, customerId, Concat (' published", content) as C Ontent from Ixt_customer_note ORDER BY createdate DESC) union (select N.createdate as CreateDate1, nc.createdate as Create Date2, Nc.dataid, Nc.customerid, concat (' Comment: ', 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, create DATE2 ASC;

4, the above merge result set is the result that we want, OK, then merge the reply result set. The result set after merging should be said to be in reverse chronological order, and then according to the published time of the review, and then press the reply to the publication time. To distinguish which table each record is, we add a field type in the result set that represents the type of record: T1 is to say, T2 is a comment, T3 is a reply.

SQL statement: SELECT * FROM (the result set of the union comment for the resulting set of results) as T order by says. CreateDate Desc, comments. createdate asc, reply. CreateDate A Sc

SELECT * FROM ((select CreateDate as CreateDate1, "" as CreateDate2, "" As CreateDate3, "T1" as type, id as Dataid, custome RIDs, concat (' post ', content) as content from Ixt_customer_note ORDER BY createdate Desc) union (select n.createdate a S createDate1, nc.createdate as CreateDate2, "" as CreateDate3, "T2" as type, Nc.dataid, Nc.customerid, concat ('   &N BSP; Comment: ', 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 Createdat E2, nr.createdate as CreateDate3, "T3" as type, Nc.dataid, Nr.customerid, concat ('         Post reply: ', nr.co ntent) as content from Ixt_customer_note_reply nr LEFT join Ixt_customer_note_comment NC on Nr.commentdataid=nc.id left Jo In ixt_customer_note N to nc.dataid=n.id ORDER by n.createdate Desc, nc.createdate ASC, NR.CREATEDATE ASC) as T Orde R by createDate1 Desc, CREATEDATE2 ASC, createDate3 ASC;

5, the above results set is what we want, but CreateDate should eventually only one, you can continue to improve, the createdate merged into a column, said the display createDate1, comments show CreateDate2, reply shows createDate3.

The improved statements are as follows:

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,cu Stomerid, id as Dataid, concat (' Publish said: ', 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 ('     Comment: ', 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.crea Tedate as CreateDate2, nr.createdate as CreateDate3, "T3" as type, Nr.customerid, Nc.dataid, concat ('         Post reply: ', nr.content) as content from Ixt_customer_note_reply nr LEFT join Ixt_customer_note_comment NC on nr.co Mmentdataid=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 CreateD Ate1 DESC, createDate2 ASC, createDate3 ASC;

MYQL Query Tree Table results: remarks, comments, comment reply