table1:
oid owner num status
1 0002 100 1
1 0003 200 2
2 0002 400 3
2 0003 400 1
1 0002 500 1
1 0003 600 1
2 0002 700 3希望显示的结果:
oid owner Sum_num 状态
1 0002 600 完成
1 0003 800 完成中
2 0002 1100 初始
2 0003 400 完成sum_num为相同oid与owner列的num之和;
S_status:若全为 1 则是完成;
若有一个 2 则是完成中
若全为 3 则是初始
我的SQL语句,未能将状态全部实现:
select distinct a.oid,a.owner,
(select sum(num) from table1 b where b.oid=a.oid and b.owner=a.owner) Sum_num,
case
when EXECSTATUS='1' then '完成'
-- when ExecStatus='3' then '初始'
else '完成中' end as 状态
from table1 a这样的结果却是根据状态的不同,而分多行显示?
oid owner num status
1 0002 100 1
1 0003 200 2
2 0002 400 3
2 0003 400 1
1 0002 500 1
1 0003 600 1
2 0002 700 3希望显示的结果:
oid owner Sum_num 状态
1 0002 600 完成
1 0003 800 完成中
2 0002 1100 初始
2 0003 400 完成sum_num为相同oid与owner列的num之和;
S_status:若全为 1 则是完成;
若有一个 2 则是完成中
若全为 3 则是初始
我的SQL语句,未能将状态全部实现:
select distinct a.oid,a.owner,
(select sum(num) from table1 b where b.oid=a.oid and b.owner=a.owner) Sum_num,
case
when EXECSTATUS='1' then '完成'
-- when ExecStatus='3' then '初始'
else '完成中' end as 状态
from table1 a这样的结果却是根据状态的不同,而分多行显示?
select
...
decode(status,1,'完成',2,'完成中','初始')
...
group by oid,owner,status
when b.status='1' then '初始'
when b.status='2' then '完成中'
else '完成' end Status from (
select S_id,s_owner,sum(s_num) as s_num from Study
group by s_id,s_owner ) a, (
select a.s_id,a.s_owner,a.status from
(select distinct S_id,s_owner,Status
from Study ) a,
(select S_id,s_owner,count(*) as num from (
select distinct S_id,s_owner,Status
from Study )
group by S_id,s_owner ) b where a.s_id=b.s_id
and a.s_owner=b.s_owner and b.num=1
union
select S_id,s_owner,'2' as Status from
(select S_id,s_owner,count(*) as num from (
select distinct S_id,s_owner,Status
from Study )
group by S_id,s_owner ) where num>1
) b
where a.s_id=b.s_id and a.s_owner=b.s_owner你自己再优化下。