一条记录中包含多个count代码,PLSQL代码库,德仔网

代码语言

代码分类

【PLSQL】一条记录中包含多个count

作者:amandag / 发布于2017/2/3/ 1100

SELECT 
COUNT(CASE 
WHEN A.CHK_TYPE IN ('1') AND A.TASK_END_STS NOT IN ('4') 
THEN 'frUFCount' 
END) frUFCount,
COUNT(CASE 
WHEN A.CHK_TYPE IN ('1') and A.TASK_END_STS IN ('4') 
THEN 'frcount' 
END) frFcount,
COUNT(CASE 
WHEN A.CHK_TYPE IN ('2') AND A.TASK_END_STS NOT IN ('4') 
THEN 'reUFCount'
END) reUFCount,
COUNT(CASE 
WHEN A.CHK_TYPE IN ('2') and A.TASK_END_STS IN ('4') 
THEN 'reFCount' 
END) reFCount,
COUNT(CASE 
WHEN A.CHK_TYPE IN ('3') AND A.TASK_END_STS NOT IN ('4') 
THEN 'ocUFCount' 
END) ocUFCount,
COUNT(CASE 
WHEN A.CHK_TYPE IN ('3') and A.TASK_END_STS IN ('4') 
THEN 'ocFCount' 
END) ocFCount,
T.displayname 
FROM AFT1_TASK A, tbl_org_user T
where A.mang_no = T.Username
AND A.mang_br_no = '200001'
group by displayname

下面添加翻页功能：
SELECT * 
FROM (SELECT m.*, rownum as rnum
        FROM (SELECT COUNT(CASE
   WHEN A.CHK_TYPE IN ('1') AND
A.TASK_END_STS NOT IN ('4') THEN
'frUFCount'
 END) frUFCount,
   COUNT(CASE
   WHEN A.CHK_TYPE IN ('1') and A.TASK_END_STS IN ('4') THEN
'frcount'
 END) frFcount,
   COUNT(CASE
   WHEN A.CHK_TYPE IN ('2') AND
A.TASK_END_STS NOT IN ('4') THEN
'reUFCount'
 END) reUFCount,
   COUNT(CASE
   WHEN A.CHK_TYPE IN ('2') and A.TASK_END_STS IN ('4') THEN
'reFCount'
 END) reFCount,
   COUNT(CASE
   WHEN A.CHK_TYPE IN ('3') AND
A.TASK_END_STS NOT IN ('4') THEN
'ocUFCount'
 END) ocUFCount,
   COUNT(CASE
   WHEN A.CHK_TYPE IN ('3') and A.TASK_END_STS IN ('4') THEN
'ocFCount'
 END) ocFCount,
displayname
                FROM AFT1_TASK A, tbl_org_user T
                where A.mang_no = T.Username
                AND A.mang_br_no = '200001'
                group by displayname) m
        WHERE rownum < '11')
WHERE rnum >= 1

试试其它关键字

同语言下

可能有用的

amandag贡献的其它代码(12)