SELECT p1.user_id, p1.arpu, p2.FLD_LVL_DESC AS ARPU_LVL , p1.ROI, p3.FLD_LVL_DESC AS ROI_LVL FROM ck_tmp_test p1 ,TMP_CK_FLD_DESC p2, TMP_CK_FLD_DESC p3 WHERE (P2.FLD_NAME='ARPU' and P2.min_fld<arpu and arpu<=P2.max_fld) AND (P3.FLD_NAME='ROI' and P3.min_fld<ROI and ROI<=P3.max_fld) 上面是现在用的代码,不过这样的话,效率不是很好。因为用户表p1非常大,而且分档字段有几十个,TMP_CK_FLD_DESC表要重复几十遍来获取字段的分档区间。用户表p1,在user_id上是有索引的。
索引在FLD,MIN_FLD,MAX_FLD上面都已经建了。因为只是想得到结果,所以码表形式上可以改。但是如果把码表横过来,似乎也不太合适,因为这样的话字段数是不定的。@ccs02287 :现在没有求汇总,只是做一个映射。
SELECT
p1.user_id,
p1.arpu,
p2.FLD_LVL_DESC AS ARPU_LVL ,
p1.ROI,
p3.FLD_LVL_DESC AS ROI_LVL
FROM ck_tmp_test p1
,TMP_CK_FLD_DESC p2,
TMP_CK_FLD_DESC p3
WHERE (P2.FLD_NAME='ARPU' and P2.min_fld<arpu and arpu<=P2.max_fld)
AND (P3.FLD_NAME='ROI' and P3.min_fld<ROI and ROI<=P3.max_fld) 上面是现在用的代码,不过这样的话,效率不是很好。因为用户表p1非常大,而且分档字段有几十个,TMP_CK_FLD_DESC表要重复几十遍来获取字段的分档区间。用户表p1,在user_id上是有索引的。
能不能建function