时间戳

首页 > 解决方案 > oracle 从联合选择中选择计数

sql - oracle 从联合选择中选择计数

问题描述

只是我需要获得总评论。所以我的查询需要选择和计数。这是我的sql。我对total_comments有疑问

SELECT customid, 
       dt, 
       customer_name, 
       e_mail, 
       phone, 
       TYPE, 
       message, 
       crm_status_id,
       total_comments
FROM   (SELECT f.ROWID                                AS customid, 
               f.dt                                   AS d, 
               To_char(f.dt, 'YYYY-MM-DD HH24:MI:SS') AS dt, 
               f.first_name 
               || ' ' 
               || f.middle_name 
               || ' ' 
               || f.last_name                         AS customer_name, 
               f.e_mail, 
               f.phone, 
               Decode(f.feedback_type, 'CALLBACK', 'CALLBACK', 
                                       'FEEDBACK')    AS TYPE, 
               f.feedback_message                     AS message, 
               f.crm_status_id                        AS crm_status_id,
               (SELECT COUNT(*) FROM CRM_FEEDBACK_COMMENTS WHERE CRM_FEEDBACK_COMMENTS.FEEDBACK_ROW_ID = f.customid) as total_comments
        FROM   crm_feedback f 
        UNION ALL 
        SELECT g.ROWID                                       AS customid, 
               create_date                                   AS d, 
               To_char(create_date, 'YYYY-MM-DD HH24:MI:SS') AS dt, 
               c.customer_name1                              AS customer_name, 
               p.email, 
               p.phone, 
               'MOBILSHOBE FEEDBACK'                         AS TYPE, 
               g.message_body                                AS message, 
               g.crm_status_id                               AS crm_status_id,
               (SELECT COUNT(*) FROM CRM_FEEDBACK_COMMENTS WHERE FEEDBACK_COMMENTS.FEEDBACK_ROW_ID = g.customid) as total_comments             
        FROM   feedback g 
               inner join sttm_customer@fcc_dblink c 
                       ON c.customer_no = g.customer_no 
               inner join pg2_customer@fcc_dblink p 
                       ON p.cif = g.customer_no)

我收到此错误：[Err] ORA-00904: "F"."CUSTOMID": invalid identifier

标签： sqloracleunion

解决方案

您的表CRM_FEEDBACK_COMMENTS用作CRM_FEEDBACK.rowid外键。这是一个糟糕的设计，有几个原因。

  1. 它不是真正的外键，因为我们无法构建引用父 ROWID 的实际约束。
  2. ROWID 不是保证值：行可以移动（尽管很少），在这种情况下您的子记录是孤立的。
  3. 正如您所发现的那样，编写连接是一件令人头疼的事情。

ORA-00904: "F"."CUSTOMID": 标识符无效

您收到此错误消息是因为我们无法将列别名传递给标量游标。要解决您最初的问题，您需要CRM_FEEDBACK.rowid将CRM_FEEDBACK_COMMENTS.FEEDBACK_ROW_ID. 您可以使用内联视图（即 FROM 子句中的子查询）来执行此操作：

   SELECT g.ROWID                                       AS customid, 
           create_date                                   AS d, 
           To_char(create_date, 'YYYY-MM-DD HH24:MI:SS') AS dt, 
           c.customer_name1                              AS customer_name, 
           p.email, 
           p.phone, 
           'MOBILSHOBE FEEDBACK'                         AS TYPE, 
           g.message_body                                AS message, 
           g.crm_status_id                               AS crm_status_id,
    FROM   feedback g 
           left outer join 
               (SELECT FEEDBACK_ROW_ID, COUNT(*) as total_comments  
                FROM CRM_FEEDBACK_COMMENTS 
                group by FEEDBACK_ROW_ID) comm
                on chartorowid(comm.FEEDBACK_ROW_ID) = g.ROWID       
           inner join sttm_customer@fcc_dblink c 
                   ON c.customer_no = g.customer_no 
           inner join pg2_customer@fcc_dblink p 
                   ON p.cif = g.customer_no)

推荐阅读