将逗号分隔的列表拆分为临时表 [英] splitting comma separated list into a temp table

问题描述

我想将以下IN查询转换为内部联接查询:

I want to convert the following IN query into inner join query :

select country, name, rank from table person_details 
   where country in ('india','USA','australia') 
   and   name in ('tom', 'jill', 'jack') 
   and   rank in ('first', 'third', 'fifith');

我有两个问题:

1. 此表很大，因此将此IN查询更改为内部联接将加快处理速度.

1. this table is big so will changing this IN query into inner join will speed up things.

将这个逗号分隔的列表拆分为某些临时表中的列的最佳方法是什么.我看到了许多正则表达式示例，但它们看起来太复杂又太大了.

What will be best way to split this comma separated list to a column in some temp table. I have see many regex examples but they seem too complex and big.

我正在使用Oracle 11g DB.

I am using Oracle 11g DB.

表快照:

Id   name   country   rank
 1    tom    india     first
 2    jill   USA      second
 3    jack   aus       first

推荐答案

从表person_details中选择国家，名称，排名

select country, name, rank from table person_details

查询在语法上不正确.您不需要关键字TABLE.只要做:

The query is syntactically incorrect. you don't need the keyword TABLE. Just do:

select country, name, rank from person_details

严格来说，您的表未进行规范化.您不应将多个值存储在单个列中.迟早您会看到性能问题.重新设计表并将值存储在单独的列中永远不会太晚.

Strictly speaking, your table is not normalized. You should not store multiple values in a single column. Sooner or later you will see the performance issues. It is never too late to re-design your tables and store the values in separate columns.

话虽如此，有很多方法可以将以逗号分隔的字符串分成几行.这是在 CONNECT BY 子句中使用 REGEXP_SUBSTR 和 INSTR 的一种简单方法:

Having said that, there are many ways to split comma delimited string into rows. here is one simple way using REGEXP_SUBSTR and INSTR in CONNECT BY clause:

SQL> WITH DATA AS(
  2  select q'['india','USA','australia']' countries,
  3  q'['tom', 'jill', 'jack']' names,
  4  q'['first', 'third', 'fifth']' ranks
  5  from dual
  6  )
  7  SELECT regexp_substr(translate(countries,'''',' '), '[^,]+', 1, LEVEL) countries,
  8  trim(regexp_substr(translate(names,'''',' '), '[^,]+', 1, LEVEL)) names,
  9  trim(regexp_substr(translate(ranks,'''',' '), '[^,]+', 1, LEVEL)) ranks
 10  FROM DATA
 11  CONNECT BY instr(countries, ',', 1, LEVEL - 1) > 0
 12  /

COUNTRIES                 NAMES                 RANKS
------------------------- --------------------- -------------------------
 india                    tom                   first
 USA                      jill                  third
 australia                jack                  fifth

SQL>

我在我的文章 ORACLE分隔字符串操作.

这篇关于将逗号分隔的列表拆分为临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！