我不知道你这几张表的具体关系,下面是"各种type的user_id总数"的取法
如何插入自己想办法吧select count(b.user_id) over(partition by b.type order by b.type) from a,b
where a.valid='表a的valid值' and b.group_id = a.group_id
如何插入自己想办法吧select count(b.user_id) over(partition by b.type order by b.type) from a,b
where a.valid='表a的valid值' and b.group_id = a.group_id
解决方案 »
- 高分,高结贴率,Oracle job日志或run detail如何删除,在线等
- 如何从多条记录中任取一条记录
- 有关工作日的问题
- 报错 ORA-00937: 非单组分组函数
- 除了dbms_metadata还有别的方法获取对象的创建脚本吗?
- 求助:asp+orcal,在录入数据的时候提示"违反唯一性约束条件",但是能正常录入的问题。
- 十万火急,关于oracle的介质文件的修复,请大侠给予援手啊!!
- 错误(ERROR - ORA-12154: TNS:could not resolve the connect identifier specified)
- 初学者,登陆sqlplus时,主机字符串填什么的,
- Oracle安装问题!
- 请教一个SQL统计语句
- 类似递归的问题
sum(decode(b.type,2,1)) as type2_count,
sum(decode(b.type,3,1)) as type3_count
from a,b where a.group_id=b.group_id and a.valid=1)
where group_id in (select group_id from a where valid=true)
group by type这个只能选出B表中符合插入条件的记录,不过记录可能是多条,插入C表的方法,再想想啊
select group_id, sum(type1_count), sum(type2_count), sum(type3_count) from
(
select b.group_id,
decode(b.type,1,count(1),0) type1_count,
decode(b.type,2,count(1),0) type2_count,
decode(b.type,3,count(1),0) type3_count
from a,b
where a.group_id=b.group_id and a.valid=zhi
group by b.group_id, b.type
)
group by group_id
看看行不
table a( group_id, valid )
table b( user_id, group_id, type, a, b, c )
table c( group_id, a_count, b_count, c_count )
根据表b的group_id, type进行分组统计user_id的数量,列a,b,c为用于判断的列,将列a为空的结果存进c.a_count,列b为空的结果存进c.b_count,列c为空的结果存进c.c_count
select group_id, sum(type1_count), sum(type2_count), sum(type3_count) from
(
select b.group_id,
decode(b.a,null,count(1),0) type1_count,
decode(b.b,null,count(1),0) type2_count,
decode(b.c,null,count(1),0) type3_count
from a,b
where a.group_id=b.group_id and a.valid=zhi
group by b.group_id,a,b,c
)
group by group_id
看看行不
SELECT a.group_id, SUM(DECODE(b.a,null,1,0)),
SUM(DECODE(b.b,null,1,0)), SUM(DECODE(b.c,null,1,0))
FROM a,b
WHERE a.group_id=b.group_id
AND a.valid=zhi
GROUP BY b.group_id