关闭

一条记录中包含多个count

标签: sql
386人阅读 评论(0) 收藏 举报
分类:
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
注意select后面的*号,如果有特殊需求将*改成具体的字段。
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:100336次
    • 积分:1875
    • 等级:
    • 排名:千里之外
    • 原创:89篇
    • 转载:32篇
    • 译文:0篇
    • 评论:10条
    文章分类
    最新评论