SQL按IN子句中的元素排序 [英] SQL order by elements from IN clause

问题描述

我有一个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接受?

谢谢

解决方案

虽然您的逻辑和上述解决方案对于小规模生产是有益的，但是如果您谈论的项目超过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

此方法是

1. 不可知的数据库
2. 可扩展为任意数量的输入
3. 提供最佳性能

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

Oracle提供了全局临时表功能，该功能创建的功能仅允许记录在会话中，并在会话的提交/结束时自动清除等.您可以使用此功能并避免使用会话密钥，但是除非其他数据库产品支持类似功能，否则无法在其他数据库产品上复制此解决方案.

I have an ITEM table and I'd like to return results ordered by the same order informed in a IN clause. These IDs are informed by the user.

Today I have this:

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

This works but CASE has a limit of 65535 arguments which is giving me ORA-00939: too many arguments for function when I need more than that.

Is there an alternative solution for Oracle, without limits and preferably with good performance and accepted in other DBMS as well?

Thanks

解决方案

While your logic and above solutions are good for small scale, if you are talking about more than 65000 items, you need a solution which is scalable.

My suggestion is to split this task to 2 steps.

Step 1

Create a temporary table, This temporary table will have 3 columns minumum

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

Each time user orders such a query, insert data ,i.e item ids and their sequence no into this temporary table with some unique key to identify user session (possibly user id or session id). This trick is to avoid collision of item lists when multiple users simultaneously fire reports.

Step 2

Now fire your report query joining your main table, temp table with session_key. In the query order data based on your input order (stored in temp table already)

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

This method is

1. database agnostic
2. scalable with any number of inputs
3. Gives best possible performance

Note: To further improve query performance, create index on session_key, item_id in temp table also create index on item_id on ITEM table (if not exists already)

Edit: Oracle offers Global Temporary Table feature, which creates has features to allow records only with in session and auto clean up upon commit/end of session etc. You can make use of this feature and avoid session key, but this solution can not be replicated on other database products unless they support similar feature.

这篇关于SQL按IN子句中的元素排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！