首頁猿問想問這樣做的原理是什么？

想問這樣做的原理是什么？

SQL Server

忽然笑 2023-04-16 21:17:26

例：select * from list ORDER BY INSTR('4,2,3,1',lid);以及比較常見的和 IN 配合使用：SELECT * FROM EVENT WHERE eventId IN(443,419,431,440,420,414,509) ORDER BY INSTR(',443,419,431,440,420,414,509,',CONCAT(',',eventId,','))

查看完整描述

2 回答

繁星coding

TA貢獻(xiàn)1797條經(jīng)驗(yàn) 獲得超4個贊

首先INSTR（str, substr）是返回substr在str中的位置，若不存在，則返回0。
select * from list ORDER BY INSTR('4,2,3,1',lid);
使用ORDER BY INSTR時，如果lid不存在'4,2,3,1'中，則返回0,order by默認(rèn)按遞增排序，所以lid為4,2,3,1的會放到最后。
SELECT * FROM EVENT WHERE eventId IN(443,419,431,440,420,414,509) ORDER BY INSTR(',443,419,431,440,420,414,509,',CONCAT(',',eventId,','))
mysql> select INSTR(',443,419,431,440,420,414,509,',CONCAT(',',443,','));
+------------------------------------------------------------+
| INSTR(',443,419,431,440,420,414,509,',CONCAT(',',443,',')) |
+------------------------------------------------------------+
| 1 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select INSTR(',443,419,431,440,420,414,509,',CONCAT(',',419,','));
+------------------------------------------------------------+
| INSTR(',443,419,431,440,420,414,509,',CONCAT(',',419,',')) |
+------------------------------------------------------------+
| 5 |
+------------------------------------------------------------+

反對回復(fù) 2023-04-20

開心每一天1111

TA貢獻(xiàn)1836條經(jīng)驗(yàn) 獲得超13個贊

order by只是一個子句，instr是一個內(nèi)部函數(shù)，用在你所列舉的例子中完全是作為order by子句的clause，語法上就是這么定義的，如果clause是表達(dá)式，先計(jì)算表達(dá)式，再根據(jù)表達(dá)式結(jié)果來執(zhí)行order by.

反對回復(fù) 2023-04-20