一个表A中含有如下数据:
id bname bs
-----------------------------------------
1 零件1 1.1
2 零件2 1.1
3 零件3 1.2
4 零件4 1.3
5 零件5 1.4
6 零件6 2.1
7 零件7 2.2
8 零件8 2.1
.......问题:
现在要统计bs为1.1、1.2加1.3的零件数量,其它的不分组统计,只列单项,如何写这个sql
解决方案 »
- SQL语句优化
- oracle报“not a single-group group function”
- 如何查看是否安装了Oracle label security模块
- oracle的问题
- Oracle怎么配置监听,监听配了以后怎样链接到数据库服务上面??
- oracle多个实例,如何共存
- Oracle 函数大全(字符串函数,数学函数,日期函数,逻辑运算函数,其他函数)
- EXPDP能否导出某个用户的数据?
- 绝对够经典的SQL语句,在线求助!
- 怎么下这样的sql,select sum(qty) from table1 where name='lile' group by name 当没有lile这条纪录时自动返回 sum(qty) =0 ,
- pl/sql developer无法敲入中文
- ORACLE存储过程中,可以动态创建表么?(表名跟当前时间有关)
count(1) 数量
from(select id,bname,decode(bs,1.3,1.2,bs) bs from A)
group by bs
union all
select bs , 1 数量 from a where bs not in (1.1,1.2,1.3)
order by bs
union all
select bs , 1 数量 from a where bs not in (1.1,1.2,1.3)
order by bs
--创建表格
create table test_t
(
id int,
bname varchar2(20),
bs varchar2(10)
);--插入数据
insert into test_t
select 1,'零件1','1.1' from dual
union all
select 2,'零件2','1.1' from dual
union all
select 3,'零件3','1.2' from dual
union all
select 4,'零件4','1.3' from dual
union all
select 5,'零件5','1.4' from dual
union all
select 6,'零件6','2.1' from dual
union all
select 7,'零件7','2.2' from dual
union all
select 8,'零件8','2.1' from dual
union all
select 9,'零件9','1.3' from dual
union all
select 11,'零件10','1.2' from dual;
commit;--执行语句
select '零件'||REPLACE(max(bname),',','/') 零件名,
count(1) 数量
from
(
select
id,
wm_concat(bname) over (partition by bs order by bname) bname, --为了用于正确的排序
decode(bs,'1.2','1.3',bs) bs
from
(select id,to_number(regexp_replace(bname,'[^0-9]+','')) bname,decode(bs,'1.2','1.3',bs) bs from test_t) a
) a
group by bs
order by bs
--查询结果零件名 数量
-------------------------------
零件1/2 2
零件3/4/9/10 4
零件5 1
零件6/8 2
零件7 1