Parent-child relationships list sort by creation time-CodePudding

For a data paging, so need to find out the list first data,
Data in the table are as follows: the id, the parent id, creation date,
Requirement: below the parent for the child, at the same level in reverse chronological order between sorting,

The statement is as follows:
The SELECT Anderson d, Amy polumbo ARENT_ID, a.C REATE_DATE FROM XXX a
The ORDER BY CASE WHEN Amy polumbo ARENT_ID=0 THEN Anderson D ELSE Amy polumbo ARENT_ID END DESC, Amy polumbo ARENT_ID, a.C REATE_DATE DESC;

To the id data for the 2, 1 to id to 5 above, can you tell me how to modify? Thank you
PS: we use a mysql

CodePudding user response:

 SELECT 
A. * 
The FROM 
Text a, - the main table 
Text b - the parent table 
WHERE 
- the parent node associated with the parent node, child nodes associated with corresponding to the parent node 
CASE Amy polumbo arent_id WHEN 0 THEN Anderson, d=b.i d ELSE Amy polumbo arent_id=b.i d END 
The ORDER BY 
Sort - reverse chronological order according to the parent node 
The biggest reate_date DESC, 
-- with the parent node, sorted by reverse chronological order, the parent node row first, so the time for the 2999 
CASE Amy polumbo arent_id WHEN 0 THEN '2999-12-31 23:59:59 ELSE a.c reate_date END DESC

CodePudding user response:

reference 1st floor lyd258963 response:

 SELECT 
A. * 
The FROM 
Text a, - the main table 
Text b - the parent table 
WHERE 
- the parent node associated with the parent node, child nodes associated with corresponding to the parent node 
CASE Amy polumbo arent_id WHEN 0 THEN Anderson, d=b.i d ELSE Amy polumbo arent_id=b.i d END 
The ORDER BY 
Sort - reverse chronological order according to the parent node 
The biggest reate_date DESC, 
-- with the parent node, sorted by reverse chronological order, the parent node row first, so the time for the 2999 
CASE Amy polumbo arent_id WHEN 0 THEN '2999-12-31 23:59:59 ELSE a.c reate_date END DESC

I put the order by changed to order by biggest reate_date DESC, Amy polumbo ARENT_ID, a.C reate_date DESC

Can be implemented, thanks.

CodePudding user response:

SELECT
Anderson, d,
Amy polumbo ARENT_ID,
A.C REATE_DATE
The FROM
XXX a
The ORDER BY Amy polumbo ARENT_ID,
Anderson, D,
A.C REATE_DATE DESC;