如何在MySQL中实现多样化排序？附查询示例与需求

Let's walk through various sorting approaches you can apply to your query, starting with your base query and original output:

Base Query & Original Results #

Your initial query:

SELECT put_id, ut_id, cm_tx, da, un FROM TA;

Original output:

1. Sort by ut_id (Ascending/Descending) #

To order results by the unique comment ID (ut_id), add an ORDER BY clause. Ascending is the default, but you can explicitly specify it, or use DESC for descending:

Ascending (matches original order):

SELECT put_id, ut_id, cm_tx, da, un FROM TA ORDER BY ut_id ASC;

Result is identical to your original output.

Descending:

SELECT put_id, ut_id, cm_tx, da, un FROM TA ORDER BY ut_id DESC;

Result:

2. Group by Post (put_id) & Sort by Comment Time (da) #

If you want to cluster all comments under the same post and order them by when they were made (using the Unix timestamp da), use this query:

SELECT put_id, ut_id, cm_tx, da, un FROM TA ORDER BY put_id ASC, da ASC;

Result (comments per post ordered oldest to newest):

To reverse the comment order (newest first per post), swap da ASC for da DESC:

SELECT put_id, ut_id, cm_tx, da, un FROM TA ORDER BY put_id ASC, da DESC;

Result:

3. Group by User (un) & Sort by Comment Time #

To see all comments from a single user grouped together, ordered by when they posted:

SELECT put_id, ut_id, cm_tx, da, un FROM TA ORDER BY un ASC, da ASC;

Result:

4. Sort by Comment Length (cm_tx character count) #

If you want to order comments by how long they are, use LENGTH(cm_tx) in the ORDER BY clause:

SELECT put_id, ut_id, cm_tx, da, un FROM TA ORDER BY LENGTH(cm_tx) ASC;

Result (shortest comments first):

For longest comments first, use DESC:

SELECT put_id, ut_id, cm_tx, da, un FROM TA ORDER BY LENGTH(cm_tx) DESC;

内容的提问来源于stack exchange，提问作者Hamed M