字段1 字段2
A 00
A 01
B 00
B 01
C 00
C 02
D 00
D 02
E 01
E 02
上面是表的模拟数据,分别统计出字段2为(00,01)、(00,02)、(01,02)三种情况的数据,比如上表对应三种情况的正确结果应该是2、2、1,这个sql语句应该如何写,各位大虾帮帮忙
A 00
A 01
B 00
B 01
C 00
C 02
D 00
D 02
E 01
E 02
上面是表的模拟数据,分别统计出字段2为(00,01)、(00,02)、(01,02)三种情况的数据,比如上表对应三种情况的正确结果应该是2、2、1,这个sql语句应该如何写,各位大虾帮帮忙
解决方案 »
- 新建用户,赋只读权限
- oracel 11g 修改表结构的问题
- 如何恢复已经删除的数据库实例
- 用exp导出一个用户后,如何用imp在别的数据库里导入该用户?要不要先创建该用户?
- 求一条查询层次结构的ORCAL 查询语句~~~~~~
- 请问怎样add一个子分区
- ORACLE中,根据条件执行相应操作的语句如何写?
- 求一句sql语句?name字段中如果没有‘///’字符,就把name字段中的内容末尾,去掉空格,然后加上“///”
- oracle数据Oracle Net Configuration Assistant配置问题
- orcl解析xml获取节点属性的值
- 两张表之间取值的问题
- oracle中分页查询性能问题
group by 字段1
sum(decode(aa.b,'00,02',1,0)) "(00,02)",
sum(decode(aa.b,'01,02',1,0)) "(01,02)"
from
(
select a,WMSYS.WM_CONCAT(b) b
from table
group by a) aa
出来是一行
结果
(00,01) (00,02) (01,02)
2 2 1
你如果要分别统计,可以这样
select '(00,01)' name,count(1) num
from
(
select a,WMSYS.WM_CONCAT(b) b
from table
group by a) aa
where aa.b='00,01'
union all
select '(00,02)',count(1)
from
(
select a,WMSYS.WM_CONCAT(b) b
from table
group by a) aa
where aa.b='00,02'
union all
select '(01,02)',count(1)
from
(
select a,WMSYS.WM_CONCAT(b) b
from table
group by a) aa
where aa.b='01,02'这样出来的结果就是
name num
(00,01) 2
(00,02) 2
(01,02) 1
decode(tb.a1,1,'(00,01)',
2,'(00,02)',
3,'(01,02)')
FROM (
SELECT sum(a2) a1,sum(a2) a2
from x_temp
group by a1) tb
GROUP BY a1
SELECT COUNT(1),
NT.A2 || XT.A2 AA2
FROM (select MAX(t1.A2) A2,
T1.A1
from x_temp t1
GROUP BY T1.A1) XT,
(select MIN(t2.A2) A2,
T2.A1
from x_temp t2
GROUP BY T2.A1) NT
WHERE XT.A1 = NT.A1
GROUP BY NT.A2 || XT.A2
我感觉比较灵活,但是只适合字段1相同的只能有2个
order by 字段1,字段2再根据结果集合,连接成
col1 col2
a 00,01
b 00,01
...
形式,最后根据上个结果集
select col2,count(*) from ...
group by col2
select id, substr(max(sys_connect_by_path(nm,',')),2) As Type from
(SELECT id, Name As nm, MIN(name) over(PARTITION BY Id) minnm
,(row_number() over(ORDER BY id, name)) + (dense_rank() over(ORDER BY id)) no
FROM test)
start with nm=minnm
connect by no-1 = prior no
group by Id )
Group By Type
insert into att values('A','00') ;
insert into att values('A','01') ;
insert into att values('B','00') ;
insert into att values('B','01') ;
insert into att values('C','00') ;
insert into att values('C','02') ;
insert into att values('D','00') ;
insert into att values('D','02') ;
insert into att values('E','01') ;
insert into att values('E','02') ;
SELECT a.c2||','||b.c2 m,count(*) c FROM ATT a,att b where a.c1 = b.c1 and a.c2<b.c2
group by a.c2||','||b.c2;
drop table att;
/*
M C
--------------------- ----------
00,01 2
00,02 2
01,02 1
*/
b as (select 字段1,字段2 from 表名),
c as (select distinct a.字段1,a.字段2 as "22" ,b.字段2 as "33" ,a.字段2 |','|b.字段2 as "44",1 as "55" from a inner join b on(a.字段1=b.字段 1)),
select count("55") as “C”,"44" as "M"from c group by "44";
得出的结果会有 M C
00,01 2
00,02 2
01,02 1
01,00 2
02,00 2
00,01 1