我正在尝试运行以下查询,以便从 category_child 表和相应的 category 表中获取有序数据。
select * from category where id in (select child_id from category_child where category_id=1 order by sequence);

就好像是

select * from category where id in (2,3,4);


select * from category where id in (3,2,4);

给我同样的结果。
有什么办法可以按相同的顺序得到结果。

category 和 category_child 表是:
-- Table structure for table `category`
--

DROP TABLE IF EXISTS `category`;<br/>
/*!40101 SET @saved_cs_client     = @@character_set_client */;<br/>
/*!40101 SET character_set_client = utf8 */;<br/>
CREATE TABLE `category` (<br/>
  `id` int(11) NOT NULL AUTO_INCREMENT,<br/>
  `name` VARCHAR(50) NOT NULL,  <br/>
  `description` VARCHAR(250) NOT NULL,<br/>
  `image_url` VARCHAR(250),<br/>
  `created_on` timestamp NOT NULL DEFAULT '2014-11-06 00:00:00',<br/>
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  <br/>
  PRIMARY KEY (`id`)<br/>
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;<br/>
/*!40101 SET character_set_client = @saved_cs_client */;<br/>

--
-- Table structure for table `category_child`<br/>
--

DROP TABLE IF EXISTS `category_child`;<br/>
/*!40101 SET @saved_cs_client     = @@character_set_client */;<br/>
/*!40101 SET character_set_client = utf8 */;<br/>
CREATE TABLE `category_child` (<br/>
  `id` int(11) NOT NULL AUTO_INCREMENT,<br/>
  `category_id` int(11) NOT NULL,<br/>
  `child_id` int(11) NOT NULL,<br/>
  `child_type` VARCHAR(100) NOT NULL,<br/>
  `sequence` int(4) NOT NULL,<br/>
  `created_on` timestamp NOT NULL DEFAULT '2014-11-06 00:00:00',<br/>
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  <br/>
  PRIMARY KEY (`id`)<br/>
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;<br/>
/*!40101 SET character_set_client = @saved_cs_client */;<br/>

最佳答案

以下查询

select c.*
from category c, category_child cc
where cc.category_id=1 and c.id=cc.child_id
order by cc.sequence;

会起作用,只是在 Nir-Z 给出的答案中稍微改变条件并删除连接。

关于mysql - 如何在mysql中使用in关键字时获得有序结果集,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/27010555/

10-12 12:26
查看更多