可以做t恤的网站/seo网络营销外包公司
XLJ
oracle提供了非常多的函数运算，有的时候使用这些函数可以大大减少sql语句的复杂度，并且减少表的扫描，
下面可以看一个利用decode函数进行语句优化的案列：
SELECT 1 AS CLIENT_SEQ,
1 AS ZQ,
NVL(A.OBJ_AREA, '000000') AS AREA_CODE,
(SELECT AA.AREA_NAME
FROM T_SYM_AREA AA
WHERE AA.AREA_CODE = NVL(A.OBJ_AREA, '000000')) AS AREA_NAME,
COUNT(DISTINCT A.OBJ_CODE) AS CLIENT_NUM,
'周期' AS PERIOD,
'客户端新增用户数' AS CLIENT_NAME
FROM T_XXX_USER_ATTR A
WHERE A.OBJ_TYPE = 0
AND A.OBJ_DATE = :B1
GROUP BY A.OBJ_AREA
UNION
SELECT 3 AS CLIENT_SEQ,
1 AS ZQ,
NVL(A.OBJ_AREA, '000000') AS AREA_CODE,
(SELECT AA.AREA_NAME
FROM T_XXX_AREA AA
WHERE AA.AREA_CODE = NVL(A.OBJ_AREA, '000000')) AS AREA_NAME,
SUM(A.OBJ_VAL1) AS CLIENT_NUM,
'周期' AS PERIOD,
'客户端登录次数' AS CLIENT_NAME
FROM T_XXX_USER_ATTR A
WHERE A.OBJ_TYPE = 1
AND A.OBJ_DATE = :B1
GROUP BY A.OBJ_AREA
对表访问了两次 我们自己来观察，完全可以用decode函数来处理，这样可以减少表的访问次数，来看看优化后的语句
SELECT decode(OBJ_TYPE, 0, 1, 3) AS CLIENT_SEQ,
1 AS ZQ,
NVL(A.OBJ_AREA, '000000') AS AREA_CODE,
(SELECT AA.AREA_NAME
FROM T_XXX_AREA AA
WHERE AA.AREA_CODE = NVL(A.OBJ_AREA, '000000')) AS AREA_NAME,
decode(OBJ_TYPE, 0, COUNT(DISTINCT A.OBJ_CODE), sum(A.OBJ_VAL1)) AS CLIENT_NUM,
'周期' AS PERIOD,
decode(OBJ_TYPE, 0, '客户端新增用户数', '客户端登录次数') AS CLIENT_NAME
FROM mepf_ods.T_XXX_USER_ATTR A
WHERE A.OBJ_TYPE in (0, 1)
AND A.OBJ_DATE = :B1
GROUP BY OBJ_TYPE, A.OBJ_AREA --这个地方很关键 不然不等价哦