相信自己!

  BlogJava :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  26 随笔 :: 3 文章 :: 13 评论 :: 0 Trackbacks
select t5.emp_name as sale_name,
       t9.emp_name as cs_name,
       t5.dept_name,
       t5.friend_id,
       t5.friend_name,
       t5.contract_number,
       t5.total_value,
       t5.so_id,
       t5.start_date,
       t5.end_date,
       t5.friend_phone,
       t5.num /*count(*)*/
  from (select t1.cust_id as friend_Id,
               t1.so_id,
               t1.emp_id,
               t2.emp_name,
               tf.friend_name,
               t3.dept_name,
               tf.friend_phone,
               t1.so_code as contract_number,
               t1.so_amount as TOTAL_VALUE,
               t1.so_delivery_date as START_DATE,
               t1.so_PAYMENT_DATE as END_DATE,
               t4.num
          from tc_sales_order_m t1,
               tc_employee t2,
               tc_department t3,
               tc_friend tf,
               (select tss.cust_id, tss.num
                  from (
                        select cust_id, count(*) as num
                          from tc_sales_order_m ts
                         where ts.owner_id = 1
                           and ts.create_time <
                               to_date('2008-09-27', 'YYYY-mm-dd')
                         group by cust_id) tss
                 where tss.num = 1
                 and tss.cust_id in
                       (select tm2.cust_id
                          from tc_sales_order_m tm2
                         where tm2.owner_id = 1
                           and tm2.create_time <=
                               to_date('2001-01-05', 'YYYY-mm-dd')
                           and tm2.create_time >=
                               to_date('2001-01-04', 'YYYY-mm-dd'))) t4
         where t1.owner_id = 1
           and t2.owner_id = 1
           and t3.owner_id = 1
           and tf.owner_id = 1
           and t1.create_time <= to_date('2001-01-05', 'YYYY-mm-dd')
           and t1.create_time >= to_date('2001-01-04', 'YYYY-mm-dd')
           and tf.friend_id = t1.cust_id
           and t1.emp_id = t2.emp_id
           and t2.dept_id = t3.dept_id
           and t1.cust_id(+) = t4.cust_id
           and t3.biz_dept_id = '110'
           and t1.cust_id = t4.cust_id
           and t3.DEPT_CODE not like '0112%'
           and t3.DEPT_CODE not like '0113%') t5,
       (select t4.emp_name, t2.friend_id
          from rel_friend_emp t2, tc_department t3, tc_employee t4
         where t2.emp_id = t4.emp_id
           and t3.dept_id = t4.dept_id
           and t2.owner_id = 1
           and t3.owner_id = 1
           and t4.owner_id = 1
           and t2.is_self = 1
           and t3.biz_dept_id = '130') t9
 where t5.friend_id = t9.friend_id(+)
posted on 2008-09-27 14:24 北极雪 阅读(147) 评论(0)  编辑  收藏 所属分类: 数据库

只有注册用户登录后才能发表评论。


网站导航: