SQL按IN子句中的元素排序

小编典典

SQL按IN子句中的元素排序

sql

我有一个ITEM表，我想返回以IN子句中通知的相同顺序排序的结果。这些ID由用户通知。

今天我有这个：

SELECT * 
FROM ITEM
WHERE ITEM_ID IN (45,2,671,6)
ORDER BY CASE ITEM_ID 
  WHEN 45 THEN 0
  WHEN 2 THEN 1
  WHEN 671 THEN 2
  WHEN 6 THEN 3
END

这可以工作，但是CASE有65535个参数的限制，这ORA-00939: too many arguments for function在我需要更多参数时给了我。

是否有Oracle的替代解决方案，没有限制，最好具有良好的性能，并且也被其他DBMS接受？

谢谢

阅读 151

2021-04-15

共1个答案

小编典典

尽管您的逻辑和以上解决方案适合于小规模生产，但如果要谈论65000多个项目，则需要可扩展的解决方案。

我的建议是将此任务分为2个步骤。

步骤1

创建一个临时表，此临时表的最小值为3列

TEMP_ITEM_ORDER_TABLE (
  session_key varchar2(50),
  item_id number,
  item_report_order number
)

每次用户下达这样的查询时，都使用一些唯一的键（用于标识用户会话）（可能是用户ID或会话ID）将数据（即项目ID及其序列号）插入此临时表中。此技巧是为了避免多个用户同时触发报告时项目列表发生冲突。

第2步

现在，通过触发报表查询连接到主表，临时表session_key。在基于您的输入顺序的查询顺序数据中（已存储在临时表中）

SELECT 
  T1.* , T2.item_report_order
FROM ITEM T1, TEMP_ITEM_ORDER_TABLE T2
  WHERE T1.ITEM_ID = T2.ITEM_ID
  AND T2.session_key = :input_session_key
  ORDER BY t2.item_report_order

这个方法是

不可知数据库
具有任意数量的输入可扩展
提供最佳性能

注意：为了进一步提高查询性能，请在session_key上创建索引，在临时表中创建item_id，还在ITEM表上创建对item_id的索引（如果尚不存在）

编辑：Oracle提供了“全局临时表”功能，该功能创建具有以下功能：只允许在会话中使用记录，并在会话的提交/结束时自动清除等。您可以使用此功能并避免使用会话密钥，但是此解决方案不能除非它们支持类似功能，否则可以在其他数据库产品上复制。

2021-04-15