mysql subindex_mysql选择具有相同ID的行并保存它们的顺序？

您可以选择所需的唯一行，而不是按照您的要求进行操作。在前端代码中，将每个唯一行存储在一个key => value结构中，其中key是项ID，value是您需要的有关该项的任何数据。

一旦你有了，你可以使用前端逻辑以所需的顺序输出它们，包括重复。这将减少您尝试选择的冗余数据量。

例如，这不是可用的代码 - 所需的确切语法取决于您的脚本语言

-- setup a display order

displayOrder= [1,2,1,3];

-- select data from database, order doesn't matter here

SELECT id,date

FROM items

WHERE id IN (displayOrder);

-- cache the results in a key=> value array

arrCachedRows = {};

for (.... each db row returned ...) {

arrCachedRows[id] = date;

}

-- Now output in desired order

for (listIndex in displayOrder) {

-- Make sure the index is cached

if (listIndex exists in arrCachedRow) {

echo arrCachedRows[listIndex ];

}

}如果你发出警告，你必须坚持使用UNION

如果你反对上面的建议并且绝对必须按照那个顺序将它们放回到1个查询中，那么添加一个额外的行来强制执行行顺序。请参阅下面的查询，其中我使用变量@subIndex将递增值添加为subIndex。这反过来允许您按此重新排序，它将按请求的顺序。

SELECT

i.*

FROM (

SELECT @subIndex:=@subIndex+1 AS subIndex, id, date FROM items where id = 1

UNION

SELECT @subIndex:=@subIndex+1 AS subIndex, id, date FROM items where id = 2

UNION

SELECT @subIndex:=@subIndex+1 AS subIndex, id, date FROM items where id = 1

UNION

SELECT @subIndex:=@subIndex+1 AS subIndex, id, date FROM items where id = 3

) AS i,(SELECT @subIndex:=0) v

ORDER BY i.subIndex或稍微更清洁的版本，将项目选择保持到外部并隐藏子索引

SELECT

items.*

FROM items

-- initialise variable

INNER JOIN (SELECT @subIndex:=0) v

-- create a meta-table with the ids desired in the order desired

INNER JOIN (

SELECT @subIndex:=@subIndex+1 AS subIndex, 1 AS id

UNION

SELECT @subIndex:=@subIndex+1 AS subIndex, 2 AS id

UNION

SELECT @subIndex:=@subIndex+1 AS subIndex, 1 AS id

UNION

SELECT @subIndex:=@subIndex+1 AS subIndex, 3 AS id

) AS i

ON i.id = items.id

-- order by the subindex from i

ORDER BY i.`subIndex` ASC