时间戳

首页 > 解决方案 > Complex SQL involving LOOP and QUERY

sql - Complex SQL involving LOOP and QUERY

问题描述

I have a result of some query that has only one column and in this column I need pass row by row making a query of this row content and where on the end I need a table with all result of this queries that I made row by row

I'm using SQL Developer with connection on a Oracle DB

this reproduces my first select

SELECT DISTINCT REGEXP_SUBSTR ('5256,5257,5258,5259','[^,]+',1,LEVEL) NUM_PROD
FROM dual CONNECT BY REGEXP_SUBSTR ('5256,5257,5258,5259','[^,]+',1,LEVEL) IS NOT NULL

I need get for example this content 5256, and use in another query.

That will return a result like this:

NAME      | NUM_PROD | COLUMN1 | COLUMN2

Michael | 5256 | AA | BB

after the result of all this query need be like 

Michael | 5256 | AA | BB

Rachel  | 5257| AA | BB

Jony      | 5258| AA | CC

Thank you for helping.

EDIT:

Let me explain better how my structure works

create table tableA (cod_person, nam_person, num_atend, num_presc, columnX, ColumnY) as (
    select 100, 'Michael', 3300, 2215, 'XX', 'YY' from dual union all
    select 101, 'Rachel',  3301, 2351, 'XX', 'YY' from dual union all
    select 103, 'Jony',    3302, 2463, 'XX', 'YY' from dual union all
    select 104, 'Tony',    3303, 2235, 'XX', 'YY' from dual);


create table tableB (num_presc, num_seq, columnXX, ColumnYY) as (
    select 2215, 2332,'XX', 'YY' from dual union all
    select 2351, 2334,'XX', 'YY' from dual union all
    select 1531, 2345,'XX', 'YY' from dual union all
    select 3250, 2348,'XX', 'YY' from dual);


create table tableC (num_presc, num_prod, num_seq_mat, columnXXX, ColumnYYY) as (
    select 2215, 5256, 2332,'XX', 'YY' from dual union all
    select 1205, 5252, 2337,'XX', 'YY' from dual union all
    select 2351, 5258, 2334,'XX', 'YY' from dual union all
    select 3135, 5260, 2349,'XX', 'YY' from dual);

My query need be like 

SELECT
    aa.cod_person,
    aa.nam_person,
    aa.num_atend,
    num_prod
FROM (SELECT DISTINCT regexp_substr ('5256,5257,5258,5259','[^,]+',1,level) num_prod FROM dual CONNECT BY regexp_substr ('5256,5257,5258,5259','[^,]+',1,level) IS NOT NULL)
    left join aa using (tableA)
    left join bb using (tableB)
    left join cc using (tableC)
WHERE   bb.num_presc = cc.num_presc 
AND     bb.num_seq = cc.num_seq_mat
AND     cc.num_prod =  (HERE WHEN I AM QUERYING ONE BY ONE I PUT THE NUM_PROD BUT NOW I'M SEARCHING ALL IN ONE TIME, SO, I DONT KNOW IF NEEDED THIS ARGUMENT)
AND     aa.num_presc = bb.num_presc
group by aa.cod_person, aa.num_atend

Currently I do this query one by one and work with this code:

SELECT
    aa.cod_person,
    aa.nam_person,
    aa.num_atend,
    5256 num_prod
FROM    tableA aa,
        tableB bb,
        tableC cc
WHERE   bb.num_presc = cc.num_presc 
AND     bb.num_seq = cc.num_seq_mat
AND     cc.num_prod =  5256
AND     aa.num_presc = bb.num_presc
group by aa.cod_person, aa.num_atend

but I need all the results together

标签： sqloracle

解决方案

You don't need any loops here. Simply join (or left join) your query with other data, it may be table(s) or other query:

select n.num_prod, o.name, o.column1, o.column2
  from (your_complex_query) n
  join (some_other_query) o on o.some_id = n.num_prod

For instance let's say we have two other tables:

create table emp (num_prod, name, dept_no) as (
    select 5256, 'Michael', 'SL' from dual union all
    select 5257, 'Rachel',  'SL' from dual union all
    select 5258, 'Jony',    'IT' from dual union all
    select 5600, 'Tony',    'AC' from dual);

create table dep (dept_no, description) as (
    select 'AC', 'Accounting'    from dual union all
    select 'SL', 'Sales'         from dual union all
    select 'IT', 'IT Department' from dual);

So our query will be:

select num_prod, emp.name, dept_no, dep.description
  from (
    select distinct regexp_substr ('5256,5257,5258,5259','[^,]+',1,level) num_prod
      from dual 
      connect by regexp_substr ('5256,5257,5258,5259','[^,]+',1,level) is not null)
  left join emp using (num_prod)
  left join dep using (dept_no);

or:

select n.num_prod, d.name, d.dept_no, d.description
  from (
    select distinct regexp_substr ('5256,5257,5258,5259','[^,]+',1,level) num_prod
      from dual 
      connect by regexp_substr ('5256,5257,5258,5259','[^,]+',1,level) is not null) n
  left join (select * from emp join dep using (dept_no)) d on d.num_prod = n.num_prod;

Result:

NUM_PROD  NAME    DEPT_NO DESCRIPTION
--------- ------- ------- -------------
5257      Rachel  SL      Sales
5256      Michael SL      Sales
5258      Jony    IT      IT Department
5259

Edit:

I created sample tables as you made it in your edited question. The exact query you need is:

select distinct aa.cod_person, aa.nam_person, aa.num_atend, nps.num_prod
from (select distinct regexp_substr ('5256,5257,5258,5259','[^,]+',1,level) num_prod 
        from dual 
        connect by regexp_substr ('5256,5257,5258,5259','[^,]+',1,level) IS NOT NULL) nps
    left join tablec cc on cc.num_prod = nps.num_prod
    left join tableb bb on bb.num_presc = cc.num_presc and bb.num_seq = cc.num_seq_mat 
    left join tablea aa on aa.num_presc = bb.num_presc

Join tables in correct order, build proper join condition and use alias for your num_prod generator, I used nps in my query. The result is:

COD_PERSON NAM_PERSON  NUM_ATEND NUM_PROD
---------- ---------- ---------- -------------------
                                 5259
       100 Michael          3300 5256
       101 Rachel           3301 5258
                                 5257

推荐阅读