文章id 栏目id 所属机构id
1 a 01
2 a 01_1
3 b 01_1_2
4 c 01
5 a 09
6 a 09_1
7 b 010_1
8 c 010
9 c 09_1_1
10 a 010_1_1
11 a 010_1
12 a 01
13 b 01_2
其中机构id 01_ 09_ 010_ 都为相应01、09、010机构子机构
请问如何得到以下统计结果
获得各个机构(包括子机构)在各个栏目下发布的文章总数
栏目 机构01 机构09 机构010
a 3 2 2
b 2 0 1
c 1 1 1
20 个解决方案
#1
SELECT 栏目ID,
COUNT(DECODE(SUBSTR(所属机构ID, 1, 3), '01_', 文章ID)) 机构01,
COUNT(DECODE(SUBSTR(所属机构ID, 1, 3), '09_', 文章ID)) 机构09,
COUNT(DECODE(SUBSTR(所属机构ID, 1, 3), '010', 文章ID)) 机构010
FROM TBL
#2
所属机构id 这个字段值都用-分开 不觉得麻烦了点么 插入要合并 查询还要分开 ..
#3
这不还是上次那个么?
with t as(
select '1' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '2' wen_id,'a' lan_id, '01_1' zuo_id from dual
union all
select '3' wen_id,'b' lan_id, '01_1_2' zuo_id from dual
union all
select '4' wen_id,'c' lan_id, '01' zuo_id from dual
union all
select '5' wen_id,'a' lan_id, '09' zuo_id from dual
union all
select '6' wen_id,'a' lan_id, '09_1' zuo_id from dual
union all
select '7' wen_id,'b' lan_id, '010_1' zuo_id from dual
union all
select '8' wen_id,'c' lan_id, '010' zuo_id from dual
union all
select '9' wen_id,'c' lan_id, '09_1_1' zuo_id from dual
union all
select '10' wen_id,'a' lan_id, '010_1_1' zuo_id from dual
union all
select '11' wen_id,'a' lan_id, '010_1' zuo_id from dual
union all
select '12' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '13' wen_id,'b' lan_id, '01_2' zuo_id from dual
)
select tt.lan_id,
MAX(nvl(decode(tt.org_id, '01', nm), 0)) 机构01,
MAX(nvl(decode(tt.org_id, '09', nm), 0)) 机构09,
MAX(nvl(decode(tt.org_id, '010', nm), 0)) 机构010
from (
select s.org_id, s.lan_id, count(s.wen_id) nm
from (select t.wen_id,
t.lan_id,
substr(t.zuo_id,
1,
decode(instr(t.zuo_id, '_', 1),
0,
length(t.zuo_id) + 1,
instr(t.zuo_id, '_') - 1)) org_id
from t) s
group by s.lan_id, s.org_id
order by s.org_id, s.lan_id) tt
group by tt.lan_id
order by tt.lan_id
#4
这和上次那个差不多啊
with t as(
select '1' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '2' wen_id,'a' lan_id, '01_1' zuo_id from dual
union all
select '3' wen_id,'b' lan_id, '01_1_2' zuo_id from dual
union all
select '4' wen_id,'c' lan_id, '01' zuo_id from dual
union all
select '5' wen_id,'a' lan_id, '09' zuo_id from dual
union all
select '6' wen_id,'a' lan_id, '09_1' zuo_id from dual
union all
select '7' wen_id,'b' lan_id, '010_1' zuo_id from dual
union all
select '8' wen_id,'c' lan_id, '010' zuo_id from dual
union all
select '9' wen_id,'c' lan_id, '09_1_1' zuo_id from dual
union all
select '10' wen_id,'a' lan_id, '010_1_1' zuo_id from dual
union all
select '11' wen_id,'a' lan_id, '010_1' zuo_id from dual
union all
select '12' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '13' wen_id,'b' lan_id, '01_2' zuo_id from dual
)
select tt.lan_id,
MAX(nvl(decode(tt.org_id, '01', nm), 0)) 机构01,
MAX(nvl(decode(tt.org_id, '09', nm), 0)) 机构09,
MAX(nvl(decode(tt.org_id, '010', nm), 0)) 机构010
from (
select s.org_id, s.lan_id, count(s.wen_id) nm
from (select t.wen_id,
t.lan_id,
substr(t.zuo_id,
1,
decode(instr(t.zuo_id, '_', 1),
0,
length(t.zuo_id) + 1,
instr(t.zuo_id, '_') - 1)) org_id
from t) s
group by s.lan_id, s.org_id
order by s.org_id, s.lan_id) tt
group by tt.lan_id
order by tt.lan_id
#5
和上次那个区别 只是 处理了一下ID
#6
已经测试通过:
表结构:
SQL> desc define
名称 是否为空? 类型
----------------------------------------- -------- --------------------
ID NUMBER(10)
TASKID VARCHAR2(10)
INFO VARCHAR2(20)
在插入你上述数据执行如下sql得到对应结果:
表结构:
SQL> desc define
名称 是否为空? 类型
----------------------------------------- -------- --------------------
ID NUMBER(10)
TASKID VARCHAR2(10)
INFO VARCHAR2(20)
在插入你上述数据执行如下sql得到对应结果:
select taskID,
sum(case when substr(info,1,2)='01' and substr(info,1,3)!='010' then 1 else 0 end),
sum(case when substr(info,1,2)='09' then 1 else 0 end),
sum(case when substr(info,1,3)='010' then 1 else 0 end)
from define group by taskid
/
#7
借一下数据
with t as
(select '1' wen_id, 'a' lan_id, '01' zuo_id
from dual
union all
select '2' wen_id, 'a' lan_id, '01_1' zuo_id
from dual
union all
select '3' wen_id, 'b' lan_id, '01_1_2' zuo_id
from dual
union all
select '4' wen_id, 'c' lan_id, '01' zuo_id
from dual
union all
select '5' wen_id, 'a' lan_id, '09' zuo_id
from dual
union all
select '6' wen_id, 'a' lan_id, '09_1' zuo_id
from dual
union all
select '7' wen_id, 'b' lan_id, '010_1' zuo_id
from dual
union all
select '8' wen_id, 'c' lan_id, '010' zuo_id
from dual
union all
select '9' wen_id, 'c' lan_id, '09_1_1' zuo_id
from dual
union all
select '10' wen_id, 'a' lan_id, '010_1_1' zuo_id
from dual
union all
select '11' wen_id, 'a' lan_id, '010_1' zuo_id
from dual
union all
select '12' wen_id, 'a' lan_id, '01' zuo_id
from dual
union all
select '13' wen_id, 'b' lan_id, '01_2' zuo_id from dual
)
SELECT *
FROM (SELECT lan_id, replace(substr(zuo_id, 1, 3), '_', '') as ss FROM t)
PIVOT(count(1) FOR ss IN('01' x01,'09' x09,'010' x010)) s
/
L X01 X09 X010
- ---------- ---------- ----------
a 3 2 2
b 2 0 1
c 1 1 1
#8
楼上 高明!
11g 这个新特性 确实不错
11g 这个新特性 确实不错
#9
这个也可以哦
#10
UP++
#11
上面都已经给出答案了,很标准了。
#12
杨老板的帖子
http://yangtingkun.itpub.net/post/468/392770
看看你就会了
#13
6楼的就可以了,1楼的最后再加个group by 栏目id 语句就好了。
#14
CREATE TABLE wenzhang(
wenzhangid VARCHAR2(4), --文章ID
lanmu VARCHAR2(10), --栏目ID
jigou VARCHAR2(10) --所属机构ID
);
SELECT P.LANMU AS "栏目",NVL(SUM("01"),0) AS "机构01",NVL(SUM("09"),0) AS "机构09",NVL(SUM("010"),0) AS "机构010"
FROM
(SELECT O.LANMU,
CASE WHEN O.JIGOU = '01' THEN '1' END "01",
CASE WHEN O.JIGOU = '09' THEN '1' END "09",
CASE WHEN O.JIGOU = '010' THEN '1' END "010"
FROM
(SELECT T.LANMU AS LANMU,
CASE
WHEN INSTR(T.JIGOU,'_') = 0 THEN SUBSTR(T.JIGOU,0,2)
WHEN INSTR(T.JIGOU,'_') = 3 THEN SUBSTR(T.JIGOU,0,2)
WHEN INSTR(T.JIGOU,'_') = 4 THEN SUBSTR(T.JIGOU,0,3)
END AS JIGOU
FROM WENZHANG T) O)P
GROUP BY P.LANMU
wenzhangid VARCHAR2(4), --文章ID
lanmu VARCHAR2(10), --栏目ID
jigou VARCHAR2(10) --所属机构ID
);
SELECT P.LANMU AS "栏目",NVL(SUM("01"),0) AS "机构01",NVL(SUM("09"),0) AS "机构09",NVL(SUM("010"),0) AS "机构010"
FROM
(SELECT O.LANMU,
CASE WHEN O.JIGOU = '01' THEN '1' END "01",
CASE WHEN O.JIGOU = '09' THEN '1' END "09",
CASE WHEN O.JIGOU = '010' THEN '1' END "010"
FROM
(SELECT T.LANMU AS LANMU,
CASE
WHEN INSTR(T.JIGOU,'_') = 0 THEN SUBSTR(T.JIGOU,0,2)
WHEN INSTR(T.JIGOU,'_') = 3 THEN SUBSTR(T.JIGOU,0,2)
WHEN INSTR(T.JIGOU,'_') = 4 THEN SUBSTR(T.JIGOU,0,3)
END AS JIGOU
FROM WENZHANG T) O)P
GROUP BY P.LANMU
#15
SELECT O.LANMU,NVL(SUM("01"),0),NVL(SUM("09"),0),NVL(SUM("010"),0)
FROM
(SELECT T.LANMU,
CASE WHEN SUBSTR(T.JIGOU,0,2) = '01' AND INSTR(T.JIGOU,'_')<4 THEN 1 END "01",
CASE WHEN SUBSTR(T.JIGOU,0,2) = '09' THEN 1 END "09",
CASE WHEN SUBSTR(T.JIGOU,0,3) ='010' THEN 1 END "010"
FROM WENZHANG T
) O
GROUP BY O.LANMU
;
#16
SELECT T.LANMU,
NVL(SUM(CASE WHEN SUBSTR(T.JIGOU,0,2) = '01' AND INSTR(T.JIGOU,'_')<4 THEN 1 END),0) "01",
NVL(SUM(CASE WHEN SUBSTR(T.JIGOU,0,2) = '09' THEN 1 END),0) "09",
NVL(SUM(CASE WHEN SUBSTR(T.JIGOU,0,3) ='010' THEN 1 END ),0) "010"
FROM WENZHANG T
GROUP BY T.LANMU;
#17
学到了!!!!!
#18
说点别的,不一定是楼主需求。
感觉楼主说的机构id 01_ 09_ 010_ 的几个数值,不是固定的吧,也就是说可能根据数据不一样,这些数值是变化的啊。如果具有通用性,感觉写个存储过程比较理想点啊,也就是说,数据中出现几个子机构,就输出这些子机构字段。
如果不是楼主需求,当我没说,呵呵。
感觉楼主说的机构id 01_ 09_ 010_ 的几个数值,不是固定的吧,也就是说可能根据数据不一样,这些数值是变化的啊。如果具有通用性,感觉写个存储过程比较理想点啊,也就是说,数据中出现几个子机构,就输出这些子机构字段。
如果不是楼主需求,当我没说,呵呵。
#19
SELECT * FROM TALBE where id in (repalce(所属机构id,'-',','))
这个简单点
这个简单点
#20
SELECT * FROM TALBE where id in (repalce(所属机构id,'-',','))//所属id 应该是一个变量,或者从程序当中取到的,这么说不知道了不了?
#21
#1
SELECT 栏目ID,
COUNT(DECODE(SUBSTR(所属机构ID, 1, 3), '01_', 文章ID)) 机构01,
COUNT(DECODE(SUBSTR(所属机构ID, 1, 3), '09_', 文章ID)) 机构09,
COUNT(DECODE(SUBSTR(所属机构ID, 1, 3), '010', 文章ID)) 机构010
FROM TBL
#2
所属机构id 这个字段值都用-分开 不觉得麻烦了点么 插入要合并 查询还要分开 ..
#3
这不还是上次那个么?
with t as(
select '1' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '2' wen_id,'a' lan_id, '01_1' zuo_id from dual
union all
select '3' wen_id,'b' lan_id, '01_1_2' zuo_id from dual
union all
select '4' wen_id,'c' lan_id, '01' zuo_id from dual
union all
select '5' wen_id,'a' lan_id, '09' zuo_id from dual
union all
select '6' wen_id,'a' lan_id, '09_1' zuo_id from dual
union all
select '7' wen_id,'b' lan_id, '010_1' zuo_id from dual
union all
select '8' wen_id,'c' lan_id, '010' zuo_id from dual
union all
select '9' wen_id,'c' lan_id, '09_1_1' zuo_id from dual
union all
select '10' wen_id,'a' lan_id, '010_1_1' zuo_id from dual
union all
select '11' wen_id,'a' lan_id, '010_1' zuo_id from dual
union all
select '12' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '13' wen_id,'b' lan_id, '01_2' zuo_id from dual
)
select tt.lan_id,
MAX(nvl(decode(tt.org_id, '01', nm), 0)) 机构01,
MAX(nvl(decode(tt.org_id, '09', nm), 0)) 机构09,
MAX(nvl(decode(tt.org_id, '010', nm), 0)) 机构010
from (
select s.org_id, s.lan_id, count(s.wen_id) nm
from (select t.wen_id,
t.lan_id,
substr(t.zuo_id,
1,
decode(instr(t.zuo_id, '_', 1),
0,
length(t.zuo_id) + 1,
instr(t.zuo_id, '_') - 1)) org_id
from t) s
group by s.lan_id, s.org_id
order by s.org_id, s.lan_id) tt
group by tt.lan_id
order by tt.lan_id
#4
这和上次那个差不多啊
with t as(
select '1' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '2' wen_id,'a' lan_id, '01_1' zuo_id from dual
union all
select '3' wen_id,'b' lan_id, '01_1_2' zuo_id from dual
union all
select '4' wen_id,'c' lan_id, '01' zuo_id from dual
union all
select '5' wen_id,'a' lan_id, '09' zuo_id from dual
union all
select '6' wen_id,'a' lan_id, '09_1' zuo_id from dual
union all
select '7' wen_id,'b' lan_id, '010_1' zuo_id from dual
union all
select '8' wen_id,'c' lan_id, '010' zuo_id from dual
union all
select '9' wen_id,'c' lan_id, '09_1_1' zuo_id from dual
union all
select '10' wen_id,'a' lan_id, '010_1_1' zuo_id from dual
union all
select '11' wen_id,'a' lan_id, '010_1' zuo_id from dual
union all
select '12' wen_id,'a' lan_id, '01' zuo_id from dual
union all
select '13' wen_id,'b' lan_id, '01_2' zuo_id from dual
)
select tt.lan_id,
MAX(nvl(decode(tt.org_id, '01', nm), 0)) 机构01,
MAX(nvl(decode(tt.org_id, '09', nm), 0)) 机构09,
MAX(nvl(decode(tt.org_id, '010', nm), 0)) 机构010
from (
select s.org_id, s.lan_id, count(s.wen_id) nm
from (select t.wen_id,
t.lan_id,
substr(t.zuo_id,
1,
decode(instr(t.zuo_id, '_', 1),
0,
length(t.zuo_id) + 1,
instr(t.zuo_id, '_') - 1)) org_id
from t) s
group by s.lan_id, s.org_id
order by s.org_id, s.lan_id) tt
group by tt.lan_id
order by tt.lan_id
#5
和上次那个区别 只是 处理了一下ID
#6
已经测试通过:
表结构:
SQL> desc define
名称 是否为空? 类型
----------------------------------------- -------- --------------------
ID NUMBER(10)
TASKID VARCHAR2(10)
INFO VARCHAR2(20)
在插入你上述数据执行如下sql得到对应结果:
表结构:
SQL> desc define
名称 是否为空? 类型
----------------------------------------- -------- --------------------
ID NUMBER(10)
TASKID VARCHAR2(10)
INFO VARCHAR2(20)
在插入你上述数据执行如下sql得到对应结果:
select taskID,
sum(case when substr(info,1,2)='01' and substr(info,1,3)!='010' then 1 else 0 end),
sum(case when substr(info,1,2)='09' then 1 else 0 end),
sum(case when substr(info,1,3)='010' then 1 else 0 end)
from define group by taskid
/
#7
借一下数据
with t as
(select '1' wen_id, 'a' lan_id, '01' zuo_id
from dual
union all
select '2' wen_id, 'a' lan_id, '01_1' zuo_id
from dual
union all
select '3' wen_id, 'b' lan_id, '01_1_2' zuo_id
from dual
union all
select '4' wen_id, 'c' lan_id, '01' zuo_id
from dual
union all
select '5' wen_id, 'a' lan_id, '09' zuo_id
from dual
union all
select '6' wen_id, 'a' lan_id, '09_1' zuo_id
from dual
union all
select '7' wen_id, 'b' lan_id, '010_1' zuo_id
from dual
union all
select '8' wen_id, 'c' lan_id, '010' zuo_id
from dual
union all
select '9' wen_id, 'c' lan_id, '09_1_1' zuo_id
from dual
union all
select '10' wen_id, 'a' lan_id, '010_1_1' zuo_id
from dual
union all
select '11' wen_id, 'a' lan_id, '010_1' zuo_id
from dual
union all
select '12' wen_id, 'a' lan_id, '01' zuo_id
from dual
union all
select '13' wen_id, 'b' lan_id, '01_2' zuo_id from dual
)
SELECT *
FROM (SELECT lan_id, replace(substr(zuo_id, 1, 3), '_', '') as ss FROM t)
PIVOT(count(1) FOR ss IN('01' x01,'09' x09,'010' x010)) s
/
L X01 X09 X010
- ---------- ---------- ----------
a 3 2 2
b 2 0 1
c 1 1 1
#8
楼上 高明!
11g 这个新特性 确实不错
11g 这个新特性 确实不错
#9
这个也可以哦
#10
UP++
#11
上面都已经给出答案了,很标准了。
#12
杨老板的帖子
http://yangtingkun.itpub.net/post/468/392770
看看你就会了
#13
6楼的就可以了,1楼的最后再加个group by 栏目id 语句就好了。
#14
CREATE TABLE wenzhang(
wenzhangid VARCHAR2(4), --文章ID
lanmu VARCHAR2(10), --栏目ID
jigou VARCHAR2(10) --所属机构ID
);
SELECT P.LANMU AS "栏目",NVL(SUM("01"),0) AS "机构01",NVL(SUM("09"),0) AS "机构09",NVL(SUM("010"),0) AS "机构010"
FROM
(SELECT O.LANMU,
CASE WHEN O.JIGOU = '01' THEN '1' END "01",
CASE WHEN O.JIGOU = '09' THEN '1' END "09",
CASE WHEN O.JIGOU = '010' THEN '1' END "010"
FROM
(SELECT T.LANMU AS LANMU,
CASE
WHEN INSTR(T.JIGOU,'_') = 0 THEN SUBSTR(T.JIGOU,0,2)
WHEN INSTR(T.JIGOU,'_') = 3 THEN SUBSTR(T.JIGOU,0,2)
WHEN INSTR(T.JIGOU,'_') = 4 THEN SUBSTR(T.JIGOU,0,3)
END AS JIGOU
FROM WENZHANG T) O)P
GROUP BY P.LANMU
wenzhangid VARCHAR2(4), --文章ID
lanmu VARCHAR2(10), --栏目ID
jigou VARCHAR2(10) --所属机构ID
);
SELECT P.LANMU AS "栏目",NVL(SUM("01"),0) AS "机构01",NVL(SUM("09"),0) AS "机构09",NVL(SUM("010"),0) AS "机构010"
FROM
(SELECT O.LANMU,
CASE WHEN O.JIGOU = '01' THEN '1' END "01",
CASE WHEN O.JIGOU = '09' THEN '1' END "09",
CASE WHEN O.JIGOU = '010' THEN '1' END "010"
FROM
(SELECT T.LANMU AS LANMU,
CASE
WHEN INSTR(T.JIGOU,'_') = 0 THEN SUBSTR(T.JIGOU,0,2)
WHEN INSTR(T.JIGOU,'_') = 3 THEN SUBSTR(T.JIGOU,0,2)
WHEN INSTR(T.JIGOU,'_') = 4 THEN SUBSTR(T.JIGOU,0,3)
END AS JIGOU
FROM WENZHANG T) O)P
GROUP BY P.LANMU
#15
SELECT O.LANMU,NVL(SUM("01"),0),NVL(SUM("09"),0),NVL(SUM("010"),0)
FROM
(SELECT T.LANMU,
CASE WHEN SUBSTR(T.JIGOU,0,2) = '01' AND INSTR(T.JIGOU,'_')<4 THEN 1 END "01",
CASE WHEN SUBSTR(T.JIGOU,0,2) = '09' THEN 1 END "09",
CASE WHEN SUBSTR(T.JIGOU,0,3) ='010' THEN 1 END "010"
FROM WENZHANG T
) O
GROUP BY O.LANMU
;
#16
SELECT T.LANMU,
NVL(SUM(CASE WHEN SUBSTR(T.JIGOU,0,2) = '01' AND INSTR(T.JIGOU,'_')<4 THEN 1 END),0) "01",
NVL(SUM(CASE WHEN SUBSTR(T.JIGOU,0,2) = '09' THEN 1 END),0) "09",
NVL(SUM(CASE WHEN SUBSTR(T.JIGOU,0,3) ='010' THEN 1 END ),0) "010"
FROM WENZHANG T
GROUP BY T.LANMU;
#17
学到了!!!!!
#18
说点别的,不一定是楼主需求。
感觉楼主说的机构id 01_ 09_ 010_ 的几个数值,不是固定的吧,也就是说可能根据数据不一样,这些数值是变化的啊。如果具有通用性,感觉写个存储过程比较理想点啊,也就是说,数据中出现几个子机构,就输出这些子机构字段。
如果不是楼主需求,当我没说,呵呵。
感觉楼主说的机构id 01_ 09_ 010_ 的几个数值,不是固定的吧,也就是说可能根据数据不一样,这些数值是变化的啊。如果具有通用性,感觉写个存储过程比较理想点啊,也就是说,数据中出现几个子机构,就输出这些子机构字段。
如果不是楼主需求,当我没说,呵呵。
#19
SELECT * FROM TALBE where id in (repalce(所属机构id,'-',','))
这个简单点
这个简单点
#20
SELECT * FROM TALBE where id in (repalce(所属机构id,'-',','))//所属id 应该是一个变量,或者从程序当中取到的,这么说不知道了不了?