大家看看这样写有没错啊？

Question

SELECT a.f_city_code,f.service_kind,f.gather_id,a.f_item_id,f.name,f.period_type,b.end_value
FROM bf_indb_user_product_info_t a,
     pm_call_favour_group_t     c,
     pm_call_favour_group_set_t d,
     bs_gsm_call_favour_t       e,
     bs_period_id_t             f,
     (SELECT SUM(TO_NUMBER(NVL(end_value,'0'))) end_value,service_kind,period_type,kind
      FROM  BS_INDB_PERIOD_FAVOUR_T
      WHERE PERIOD_INDEX<=(SELECT MAX(PERIOD_INDEX)
        FROM   BS_INDB_PERIOD_FAVOUR_T
        WHERE((period_index<>next_index AND next_index<>0) OR next_index=0))
        GROUP BY service_kind,period_type,kind) b
WHERE a.f_city_code='760'
   AND a.f_favour_group_id=c.f_group_id
   AND a.f_service_kind=c.f_service_kind
   AND c.f_city_code=a.f_city_code
   AND c.f_city_code = d.f_city_code
   AND c.f_city_code = e.city_code
   AND c.f_service_kind = d.f_service_kind
   AND c.f_service_kind = f.service_kind
   AND c.f_call_favour_group = d.f_call_favour_group
   AND d.f_call_favour = e.kind
   AND f.kind = e.airtime_discount
   AND e.airtime_discount > 1
   AND f.gather_id > 0
   AND f.kind=b.kind
   AND f.period_type=b.period_type
   AND f.service_kind=b.service_kind

Answer 1

SELECT a.f_city_code,f.service_kind,f.gather_id,a.f_item_id,f.name,f.period_type,b.end_value
FROM bf_indb_user_product_info_t a,
     pm_call_favour_group_t     c,
     pm_call_favour_group_set_t d,
     bs_gsm_call_favour_t       e,
     bs_period_id_t             f,
     (SELECT SUM(TO_NUMBER(NVL(end_value,'0'))) end_value,service_kind,period_type,kind
      FROM  BS_INDB_PERIOD_FAVOUR_T
      WHERE PERIOD_INDEX<=(SELECT MAX(PERIOD_INDEX)
        FROM   BS_INDB_PERIOD_FAVOUR_T
        WHERE((period_index<>next_index AND next_index<>0) OR next_index=0))
        GROUP BY service_kind,period_type,kind) b
WHERE a.f_city_code='760'
   AND a.f_favour_group_id=c.f_group_id
   AND a.f_service_kind=c.f_service_kind
   AND c.f_city_code=a.f_city_code
   AND c.f_city_code = d.f_city_code
   AND c.f_city_code = e.city_code
   AND c.f_service_kind = d.f_service_kind
   AND c.f_service_kind = f.service_kind
   AND c.f_call_favour_group = d.f_call_favour_group
   AND d.f_call_favour = e.kind
   AND f.kind = e.airtime_discount
   AND e.airtime_discount > 1
   AND f.gather_id > 0
   AND f.kind=b.kind
   AND f.period_type=b.period_type
   AND f.service_kind=b.service_kind

Answer 2

执行就死机，执行的时间要好久哦？还有CPU和内存的情况？能看到不啊？估计是数据量大了，检查是否锁表了。

Answer 3

大哥....你嵌套了三个SQL能不慢吗...你到网上去找一点SQL优化资料看一下..把SQL优化一下.就可以哒..一般是数据量比较大的表面放得前面.过滤数据最多也要放得前面.能不用嵌套尽量不用.

Answer 4

过滤数据最多也要放得后面.能不用嵌套尽量不用.SQL运行一般是从后到前..你首先过滤掉一大部分数据再去关联表那样会快些.