MySQL ORDER BY FIELD可以从内部联接表中获取第二个参数吗？

Question

I got one table which's rows are item_id and tag_id. Every item_id is unique auto implement. item may have several tag_id.

I'm trying to get data sorted like this. When user visit page I catch item_id, go to table, get all tag_ids by item_id and try to return other item_ids which's have same tag_id.

Here is the problem when it returns items_id it is random, first i need to get items by first tag and then other. Here is the SQL query.

    SELECT DISTINCT `item_id`, `rel`.`tag_id`
    FROM `itv_content_tags_rel` AS `rel`
    INNER JOIN ( SELECT `tag_id`
                 FROM `itv_content_tags_rel`
                 WHERE `item_id` = 65736 
                ) 
    AS kk ON kk.`tag_id` = `rel`.`tag_id`
    ORDER BY FIELD(`rel`.`tag_id`, 'rel.tag_id')

The ORDER BY FIELD row not working, [Her is table i got with whis query.][1]

When i change
ORDER BY FIELD(rel.tag_id, 'rel.tag_id')
By

ORDER BY FIELD(`rel`.`tag_id`, '3788') DESC

everything works perfectly [I got sorted table][2]

Is here any solution ?

Edit :

Here is the table i got after Tim's edit. Tim's query Is it possible to get sorted with item_id too like this. First sorted with tag_id then time_id

Answer 1

The reason adding FIELD appears to have fixed the problem is that it puts the 3788 ID first, followed by everything else, which coincidentally just happens to be one other ID. If you want to sort on the tag_id column numerically, then you can just use ORDER BY and cast this column to something numeric:

SELECT DISTINCT item_id, rel.tag_id
FROM itv_content_tags_rel AS rel
INNER JOIN
(
    SELECT tag_id
    FROM itv_content_tags_rel
    WHERE item_id = 65736 
) AS kk
    ON kk.tag_id = rel.tag_id
ORDER BY CAST(rel.tag_id AS UNSIGNED)

Note that if you plan for the tag_id to only always contain numbers, maybe consider switching the type of this column to a numeric type so you can avoid casts like this in your queries.

Update: If you also want to sort using the item_id then just add another level to the ORDER BY, i.e. something like this:

ORDER BY CAST(rel.tag_id AS UNSIGNED),
         CAST(rel.item_id AS UNSIGNED)