Table 1Id productNo status
1 101 Active
2 201 IsActive
3 101 Active
4 101 Active想通过productNo查询出 Active状态总和,isActive的状态总和。比如给定101 ,查询出如下:
productNo Current Future
101 3 1
1 101 Active
2 201 IsActive
3 101 Active
4 101 Active想通过productNo查询出 Active状态总和,isActive的状态总和。比如给定101 ,查询出如下:
productNo Current Future
101 3 1
解决方案 »
- OCI取100+行数据,存到map中
- 请教调用for rec。。loop循环后输出的格式问题,有实例!
- 百万级数据的表分离数据,都进来看看,给点意见。谢谢。
- 数据库同步问题,请大家提供解决方案,欢迎讨论
- 谁能告诉我,数据文件是怎么回事啊,怎么用啊。
- 大数据量更新的性能策略,请大家给个建议?高手请进。
- oracle 标识符过长,这个问题困扰我很长时间了,忘各位赐教,谢谢了。
- 在存储过程中,通过链路从另一个数据库中取一个大表到本数据库,……………(100)
- Oracle连接时的一个小问题。
- SQL PLUS老是说协议配适器错误
- 多表查询的sql语句写法问题,跪求大家帮助!!
- 紧急求助 一个sql看一下
Table 1Id productNo status
1 101 Active
2 201 IsActive
3 101 Active
4 101 Active
5 101 IsActive想通过productNo查询出 Active状态总和,isActive的状态总和。比如给定101 ,查询出如下:
productNo Current(Active) Future (IsActive)
101 3 1
create table tb(Id int , productNo varchar2(10),status varchar2(20)) ;
insert into tb
select '1','101','Active' from dual
union all
select '2','201','IsActive' from dual
union all
select '3','101','Active' from dual
union all
select '4','101','Active' from dual
union all
select '5','101','IsActive' from dual
union all
select '6','101','IsActive' from dual
union all
select '7','201','IsActive' from dual;select productNo, ( select count(*) from tb where trim(status)='Active') as Current_Active,
( select count(*) from tb where productNo='101' and trim(status)='IsActive') as Future_IsActive
from tb where productNo='101' group by productNo
测试结果如下:
productNo Current_Active Future_IsActive
101 3 2
2 sum(case when status='Active' then 1 else 0 end) as Current_Active,
3 sum(case when status='IsActive' then 1 else 0 end) as Current_IsActive
4 from (
5 select 1 as id,101 as productNo,'Active' as status from dual
6 union all
7 select 2 as id,201 as productNo,'IsActive' as status from dual
8 union all
9 select 3 as id,101 as productNo,'Active' as status from dual
10 union all
11 select 4 as id,101 as productNo,'Active' as status from dual
12 union all
13 select 5 as id,101 as productNo,'IsActive' as status from dual
14 )tt
15 where tt.productNo = 101
16 group by tt.productNo; PRODUCTNO CURRENT_ACTIVE CURRENT_ISACTIVE
---------- -------------- ----------------
101 3 1