17,377
社区成员
发帖
与我相关
我的任务
分享
select count((select id
from (select id from xjrw1 where xl = '仙剑一') a1
where a1.id = x.id)) xj1,
count((select id
from (select id from xjrw1 where xl = '仙剑二') a2
where a2.id = x.id)) xj2,
count((select id
from (select id from xjrw1 where xl = '仙剑三') a3
where a3.id = x.id)) xj3
--,round(xj1+xj2) t
from xjrw1 x
select max(decode(xl,'仙剑一',cnt,null))+max(decode(xl,'仙剑二',cnt,null)) as x12, --计算xj1+xj2
max(decode(x1,'仙剑二',cnt,null))+max(decode(x1,'仙剑三',cnt,null)) as x23, --计算xj2+xj3
max(decode(xl,'仙剑一',cnt,null))+max(decode(x1,'仙剑二',cnt,null))+max(decode(x1,'仙剑三',cnt,null)) as x123, --计算xj1+xj2+xj3
from(select x1,count(*) as cnt
from xjrw1
where xl in ('仙剑一','仙剑二','仙剑三')
group by x1
)
select id,x1,count(*)
from xjrw1
where xl in ('仙剑一','仙剑二','仙剑三')
group by id,x1