需要帮助来存储三列中的值 [英] Need help to store value from three column

问题描述

SELECT DISTINCT 
                    clt.id,
                    clt.client_p_email,
                    clt.client_s_email,
                    cus.customer_mail
from  client clt,
         customers cus
where clt.id=cus.id
--My Record is comming bellow 
ID          client_p_email                             client_s_email                                         customer_mail
-----       ----------------------------      ----------------------------                          ---------------------------
703         [e-mail]                          [e-mail]                                            [e-mail].                                    
623         [e-mail]                   [e-mail]                                           [e-mail]                                      
965         [e-mail]               [e-mail]                                  [e-mail]                                        
270         [e-mail]                   [e-mail]                                 [e-mail]                                         
719         [e-mail]                  [e-mail]                                          [e-mail]     

我想要什么?我想在一栏中显示所有电子邮件.但是如果您注意到 703 id 必须存储三次，而 623 只能存储两次，因为两封电子邮件是相同的.并且 965 将存储一次，因为所有内容都相同.请建议我如何创建一个匿名块来存储我想要的值
请帮忙

what i want? i want to display all email in one column. but if you notice 703 id has to store three time and 623 has to store only two time becasue two email are same. and 965 will store one time because all are same . please suggest me how can i create an anonymous block that will store value i want
Please help

推荐答案

假设你想在表中插入数据，例如:

Say you want to insert data in a table like:

create table allEmailTable  (id number, mail varchar2(100))

假设您已经有了给出该结果的查询，您可能需要:

Assuming you already have your query that gives that result, you may need:

insert into allEmailTable(id, mail)
with yourQuery(ID, client_p_email, client_s_email, customer_mail) as (
  select 703        , '[e-mail]'           ,'[e-mail]'   , '[e-mail]' from dual union all                                   
  select 623        , '[e-mail]'         ,'[e-mail]'   ,  '[e-mail]' from dual union all                                      
  select 965        , '[e-mail]'      ,'[e-mail]',  '[e-mail]' from dual union all                                        
  select 270        , '[e-mail]'      ,'[e-mail]',  '[e-mail]' from dual union all                                         
  select 719        , '[e-mail]'        ,'[e-mail]'   ,  '[e-mail]' from dual
)
select distinct ID, mail
from (
      select id, client_p_email as mail from yourQuery UNION
      select id, client_s_email         from yourQuery UNION
      select id, customer_mail          from yourQuery 
     )

结果:

SQL> select * from allEmailTable;

        ID MAIL
---------- --------------------
       270 [e-mail]
       270 [e-mail]
       270 [e-mail]
       623 [e-mail]
       623 [e-mail]
       703 [e-mail]
       703 [e-mail]
       703 [e-mail]
       719 [e-mail]
       719 [e-mail]
       719 [e-mail]
       965 [e-mail]

12 rows selected.

您的查询将是:

insert into allEmailTable(id, mail)
with yourQuery(ID, client_p_email, client_s_email, customer_mail) as (
  SELECT DISTINCT 
                    clt.id,
                    clt.client_p_email,
                    clt.client_s_email,
                    cus.customer_mail
  from  client clt,
         customers cus
where clt.id=cus.id
)
select distinct ID, mail
from (
      select id, client_p_email as mail from yourQuery UNION
      select id, client_s_email         from yourQuery UNION
      select id, customer_mail          from yourQuery 
     )

这篇关于需要帮助来存储三列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！