求助SQL大神,拜托啦各位(Oracle),在线等,急!!!!

表decla_regularInfo,其中一些字段,
id ,
declarCycle 年度,
declarYear 报告年度
DeclarType 申报类型
dataStatus 完成状态 (1待提交、2待审核、3审核通过、4待修改);
怎么一条SQL实现这样形式
图片说明
解释一下:其中
审核通过就是 完成状态 3,
已申报人数就是 完成状态 2 3 4

2

5个回答

select t2.declarCycle as 年度,
t2.declarYear as 报告年度,
t2.DeclarType as 申报类型,
count(1) as 应申报人数,
(select count(1)
from decla_regularInfo t1
where t1.declarCycle = t2.declarCycle
and t1.DeclarType = t2.DeclarType
and t1.declaryear = t2.declaryear
and t1.dataStatus = '1') as 审核通过,
(select count(1)
from decla_regularInfo t3
where t3.declarCycle = t2.declarCycle
and t3.DeclarType = t2.DeclarType
and t3.declaryear = t2.declaryear
and t3.dataStatus in ('2', '3', '4')) as 已申报人数
from decla_regularInfo t2
group by t2.declaryear, t2.declarCycle, t2.DeclarType
order by declarCycle, DeclarType;

0
qq_36457807
饺子没有子 CASE WHEN 就能解决,非要那么多子查询
12 个月之前 回复
qq_24044859
qq_24044859 回复jackleeonlyone: 题主,我的回答错了吗?和我不是一样吗?你看一下回答的时间好不好啊,很心凉,怕写错,还建表测试。。。。
大约一年之前 回复
jackleeonlyone
jackleeonlyone dataStatus =3 为审核通过,不过这个没关系,学到了,牛逼
大约一年之前 回复

select
id,
declarYear,
declarCycle,d
eclarType,
count(datastatus) as '应申报人数',
(select count(datastatus) from declar_regularInfo where datastatus in (2,3,4)) as '已申报人数',
(select count(datastatus) from declar_regularInfo where datastatus = 3 ) as '审核通过'
from
declar_regularInfo
group by id,declarYear,declarCycle,declarType;

2
jackleeonlyone
jackleeonlyone 这样统计数量并非是对的,select * from decla_regularInfo where declarYear =2018 and declarCycle =3 and declarType =1 and datastatus in (2,3,4) 就是只有一条,而这样写就是五条数据
大约一年之前 回复
jackleeonlyone
jackleeonlyone 回复yangkkai_123: 这样写出来有点问题
大约一年之前 回复
qq_42178355
qq_42178355 group by 的字段,必须是同前面查询的一样
大约一年之前 回复
yangkkai_123
yangkkai_123 回复yangkkai_123: count写到外面
大约一年之前 回复
yangkkai_123
yangkkai_123 写的不好见谅
大约一年之前 回复

图片说明,结构就是这样,应该不是正确的

0
weixin_37850517
weixin_37850517 嗯,这就是sql表,还要设置字符类型
大约一年之前 回复

不知道是不是这个意思
select t2.declarCycle as 年度,
t2.declarYear as 报告年度,
t2.DeclarType as 申报类型,
count(1) as 应申报人数,
(select count(1)
from decla_regularInfo t1
where t1.declarCycle = t2.declarCycle
and t1.DeclarType = t2.DeclarType
and t1.declaryear = t2.declaryear
and t1.dataStatus = '1') as 审核通过,
(select count(1)
from decla_regularInfo t3
where t3.declarCycle = t2.declarCycle
and t3.DeclarType = t2.DeclarType
and t3.declaryear = t2.declaryear
and t3.dataStatus in ('2', '3', '4')) as 已申报人数
from decla_regularInfo t2
group by t2.declaryear, t2.declarCycle, t2.DeclarType
order by declarCycle, DeclarType;

0

select
id,
declarYear,
declarCycle,d
eclarType,
count(datastatus) as '应申报人数',
(select count(datastatus) from declar_regularInfo where datastatus in (2,3,4)) as '已申报人数',
(select count(datastatus) from declar_regularInfo where datastatus = 3 ) as '审核通过'
from
declar_regularInfo
group by id,declarYear,declarCycle,declarType;

0
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!