SQL开发知识：Oracle中的SUM用法介绍
Oracle中的SUM条件查询
1、按照区域编码分组查询区域编码、IPTV_NBR不为空的数量、ACC_NBR不为空的数量、所有用户数量
SELECT
AREA_CODE,
SUM (
CASE
WHEN IPTV_NBR IS NULL or IPTV_NBR = ” THEN
0
ELSE
1
END
),
SUM (
CASE
WHEN ACC_NBR IS NULL or ACC_NBR = ” THEN
0
ELSE
1
END
),
COUNT (*)
FROM
GAT_SQMS.GAT_SQMS_BAND_IPTV_VIEW
GROUP BY
AREA_CODE;
2、使用sum条件查询
查询IPTV_NBR不为空的数量
SUM (
CASE
WHEN IPTV_NBR IS NULL or IPTV_NBR = ” THEN
0
ELSE
1
END
)
SUM里边使用CASE WHEN 语句
当IPTV_NBR IS NULL 为0，ELSE 为1
oracle中sum字符串方法
CREATE OR REPLACE FUNCTION SumString(i_TableName IN VARCHAR2,
i_GroupColName IN VARCHAR2,
i_ResultColName IN VARCHAR2,
i_GroupColValue IN VARCHAR2,
i_Separator IN VARCHAR2)
RETURN VARCHAR2 IS
TYPE T_Cur IS REF CURSOR;
C_Cur T_Cur;
V_Sql VARCHAR2(2000);
V_Result VARCHAR2(2000);
V_Tmp VARCHAR2(200);
V_Cnt NUMBER := 0;
BEGIN
V_Result := ‘ ‘;
V_Sql := ‘SELECT ‘ || i_ResultColName || ‘ FROM ‘ || i_TableName ||
‘ WHERE ‘ || i_GroupColName || ‘ = ‘ || i_GroupColValue;
OPEN C_Cur FOR V_Sql;
LOOP
FETCH C_Cur
INTO V_Tmp;
EXIT WHEN C_Cur%NOTFOUND;
IF V_Cnt = 0 THEN
V_Result := V_Tmp;
ELSE
V_Result := V_Result || i_Separator || V_Tmp;
END IF;
V_Cnt := V_Cnt + 1;
END LOOP;
CLOSE C_Cur;
RETURN V_Result;
END SUMSTRING;
总结
Oracle中的SUM条件查询
1、按照区域编码分组查询区域编码、IPTV_NBR不为空的数量、ACC_NBR不为空的数量、所有用户数量
SELECT
AREA_CODE,
SUM (
CASE
WHEN IPTV_NBR IS NULL or IPTV_NBR = ” THEN
0
ELSE
1
END
),
SUM (
CASE
WHEN ACC_NBR IS NULL or ACC_NBR = ” THEN
0
ELSE
1
END
),
COUNT (*)
FROM
GAT_SQMS.GAT_SQMS_BAND_IPTV_VIEW
GROUP BY
AREA_CODE;
2、使用sum条件查询
查询IPTV_NBR不为空的数量
SUM (
CASE
WHEN IPTV_NBR IS NULL or IPTV_NBR = ” THEN
0
ELSE
1
END
)
SUM里边使用CASE WHEN 语句
当IPTV_NBR IS NULL 为0，ELSE 为1
oracle中sum字符串方法
CREATE OR REPLACE FUNCTION SumString(i_TableName IN VARCHAR2,
i_GroupColName IN VARCHAR2,
i_ResultColName IN VARCHAR2,
i_GroupColValue IN VARCHAR2,
i_Separator IN VARCHAR2)
RETURN VARCHAR2 IS
TYPE T_Cur IS REF CURSOR;
C_Cur T_Cur;
V_Sql VARCHAR2(2000);
V_Result VARCHAR2(2000);
V_Tmp VARCHAR2(200);
V_Cnt NUMBER := 0;
BEGIN
V_Result := ‘ ‘;
V_Sql := ‘SELECT ‘ || i_ResultColName || ‘ FROM ‘ || i_TableName ||
‘ WHERE ‘ || i_GroupColName || ‘ = ‘ || i_GroupColValue;
OPEN C_Cur FOR V_Sql;
LOOP
FETCH C_Cur
INTO V_Tmp;
EXIT WHEN C_Cur%NOTFOUND;
IF V_Cnt = 0 THEN
V_Result := V_Tmp;
ELSE
V_Result := V_Result || i_Separator || V_Tmp;
END IF;
V_Cnt := V_Cnt + 1;
END LOOP;
CLOSE C_Cur;
RETURN V_Result;
END SUMSTRING;
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。如果你想了解更多相关内容请查看下面相关链接
