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

问题描述：

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

我有一个表，其中的行是item_id和tag_id。每个item_id都是唯一的自动工具。 item可能有几个tag_id。 p>

我正在尝试将数据排序为这样。当用户访问页面时，我抓住item_id，转到表格，按item_id获取所有tag_ids，并尝试返回其他具有相同tag_id的item_id。 p>

这是问题，当它返回items_id时它是随机的，首先我需要通过第一个标签获取项目，然后是其他标签。这是SQL查询。 p>

  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'  ）
  code>  pre> 
 
  ORDER BY FIELD行无法正常工作，[她的表格是我查询过的。] [1]  p> 
 
 
当我更改
 ORDER BY FIELD（ code> rel 。 code> tag_id ，'rel.tag_id'） code> 
 
By  
 
 
  ORDER BY FIELD（`rel``tag_id`，'3788'）DESC 
  code>  pre> 
 
 一切正常 [我有分类表] [2]  p> 
 
 
这里有解决方案吗？ p> 
 
 
编辑： p> 
 
 
这里 是蒂姆编辑后得到的表格。  
  Tim的查询 
是否可以使用item_id对此进行排序。  n 首先使用tag_id排序，然后按time_id排序  p> 
  div>

答

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)

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

相关推荐