我不知道你这几张表的具体关系,下面是"各种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
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