产品表t_prdct包含字段
客户经理 集团 产品类型
C1 G1 A
C1 G1 B
C1 G1 C
C1 G2 A
C1 G3 B一个客户经理对应多个集团,一个集团有多个产品类型
现在要根据产品类型汇总客户经理下集团数结果字段
客户经理 包含A类产品的集团个数 不包含A类且包含B类产品的集团个数 不包含A或B类产品的集团个数
C1 2 1 0 实际分类还有许多组合
现在只是列举简单的
能怎样用尽量简单的语句实现
客户经理 集团 产品类型
C1 G1 A
C1 G1 B
C1 G1 C
C1 G2 A
C1 G3 B一个客户经理对应多个集团,一个集团有多个产品类型
现在要根据产品类型汇总客户经理下集团数结果字段
客户经理 包含A类产品的集团个数 不包含A类且包含B类产品的集团个数 不包含A或B类产品的集团个数
C1 2 1 0 实际分类还有许多组合
现在只是列举简单的
能怎样用尽量简单的语句实现
select 客户经理,集团,count(*) from tableName where 产品类型='A' group by 客户经理,集团
2.不包含A类且包含B类产品的集团个数
select 客户经理,集团,count(*) from tableName where 产品类型<>'A' and 产品类型='B' group by 客户经理,集团
3.不包含A或B类产品的集团个数
select 客户经理,集团,count(*) from tableName where 产品类型<>'A' or 产品类型<>'B' group by 客户经理,集团-------综合起来
select a.客户经理,a.cn,b.cn,c.cn from
(select 客户经理,集团,count(*) cn from tableName where 产品类型='A' group by 客户经理,集团) a,
(select 客户经理,集团,count(*) cn from tableName where 产品类型<>'A' and 产品类型='B' group by 客户经理,集团) b,
(select 客户经理,集团,count(*) cn from tableName where 产品类型<>'A' or 产品类型<>'B' group by 客户经理,集团) c
where a.客户经理=b.客户经理 and b.客户经理=c.客户经理
1.包含A类产品的集团个数
select 客户经理,count(*) cn from (select 客户经理,集团,count(*) from tableName where 产品类型='A' group by 客户经理,集团)
2.不包含A类且包含B类产品的集团个数
select 客户经理,count(*) cn from (select 客户经理,集团,count(*) from tableName where 产品类型<>'A' and 产品类型='B' group by 客户经理,集团)
3.不包含A或B类产品的集团个数
select 客户经理,count(*) cn from (select 客户经理,集团,count(*) from tableName where 产品类型<>'A' or 产品类型<>'B' group by 客户经理,集团)-------综合起来
select a.客户经理,a.cn,b.cn,c.cn from
(select 客户经理,count(*) cn from (select 客户经理,集团,count(*) from tableName where 产品类型='A' group by 客户经理,集团)) a,
(select 客户经理,count(*) cn from (select 客户经理,集团,count(*) from tableName where 产品类型<>'A' and 产品类型='B' group by 客户经理,集团)) b,
(select 客户经理,count(*) cn from (select 客户经理,集团,count(*) from tableName where 产品类型<>'A' or 产品类型<>'B' group by 客户经理,集团) c
where a.客户经理=b.客户经理 and b.客户经理=c.客户经理
create table temp
(manager varchar2(10), --经理
combine varchar2(10), --集团
productType varchar2(10)); --产品
--所有的客户经理
select distinct manager from temp;
--包含A类产品的集团个数
select manager, count(distinct combine)
from temp
where productType = 'A'
group by manager;
--不包含A类且包含B类产品的集团个数
select a.manager, count(distinct combine)
from temp a
where producttype = 'B'
and 'A' not in (select b.producttype
from temp b
where a.manager = b.manager
and a.combine = b.combine)
group by manager;
--不包含A或B类产品的集团个数
select a.manager, count(distinct combine)
from temp a
where producttype not in ('A', 'B')
and ('A' not in (select b.producttype
from temp b
where a.manager = b.manager
and a.combine = b.combine) or
'B' not in (select b.producttype
from temp b
where a.manager = b.manager
and a.combine = b.combine))
group by manager;--汇总的SQL语言
select T1.manager,
nvl(T2.comcount, 0),
nvl(T3.comcount, 0),
nvl(T4.comcount, 0)
from (select distinct manager from temp) T1,
(select manager, count(distinct combine) comcount
from temp
where productType = 'A'
group by manager) T2,
(select a.manager, count(distinct combine) comcount
from temp a
where producttype = 'B'
and 'A' not in (select b.producttype
from temp b
where a.manager = b.manager
and a.combine = b.combine)
group by manager) T3,
(select a.manager, count(distinct combine) comcount
from temp a
where producttype not in ('A', 'B')
and ('A' not in (select b.producttype
from temp b
where a.manager = b.manager
and a.combine = b.combine) and
'B' not in (select b.producttype
from temp b
where a.manager = b.manager
and a.combine = b.combine))
group by manager) T4
where T1.manager = T2.manager(+)
and T1.manager = T3.manager(+)
and T1.manager = T4.manager(+);--测试用的数据
C1 G1 A
C1 G1 B
C1 G1 C
C1 G1 D
C1 G1 E
C1 G2 B
C1 G2 C
C1 G3 E
C1 G3 F
C1 G4 A
C1 G5 A
C1 G5 B
C1 G5 C
C1 G6 C
C1 G6 D
C1 G6 B
C1 G6 D
C1 G7 E--执行结果
C1 3 2 2
with tb as
(select 'C1' KHJL, 'G1' JT, 'A' CP from dual union all
select 'C1', 'G1', 'B' from dual union all
select 'C1', 'G1', 'C' from dual union all
select 'C1', 'G2', 'A' from dual union all
select 'C1', 'G3', 'B' from dual)
select KHJL,
sum(decode(TJX, 1, 1, 0)) 条件1,
sum(decode(TJX, 1, 2, 0)) 条件2,
sum(decode(TJX, 0, 1, 0)) 其它
from (select tb.*,
case
when CP = 'A' then 1 -- 条件1
when CP = 'B' then 2 -- 条件2
else 0
end tjx
from tb)
group by KHJL;对于不同的条件,使用时修改case when 子句的内容就可以了
(select 'C1' mgr, 'G1' company, 'A' ptype from dual union all
select 'C1', 'G1', 'B' from dual union all
select 'C1', 'G1', 'C' from dual union all
select 'C1', 'G2', 'A' from dual union all
select 'C1', 'G3', 'B' from dual),
tb2 as(
SELECT MGR,COMPANY,ptype
FROM TB B
WHERE NOT EXISTS(SELECT 1 FROM TB A
WHERE A.mgr=B.mgr AND A.company=B.company AND A.PTYPE='A')),
tb3 as
(SELECT MGR,COMPANY,ptype
FROM TB B
WHERE NOT EXISTS(SELECT 1 FROM TB A
WHERE A.mgr=B.mgr AND A.company=B.company AND A.PTYPE='A' OR A.PTYPE='B'))
select a.mgr,sum(decode(a.ptype,'A',1,0)) "含A类产品的集团个数",
count(b.ptype) "不含A类且含B类产品的集团个数",
NVL(count(c.ptype),0) "不含A或B类产品的集团个数"
from tb a,tb2 b,tb3 c
WHERE a.MGR=b.MGR(+) and a.company=b.company(+)
AND a.MGR=c.MGR(+) and a.company=c.company(+)
GROUP BY a.MGR
SQL> with tb as
2 (select 'C1' mgr, 'G1' company, 'A' ptype from dual union all
3 select 'C1', 'G1', 'B' from dual union all
4 select 'C1', 'G1', 'C' from dual union all
5 select 'C1', 'G2', 'A' from dual union all
6 select 'C1', 'G3', 'B' from dual),
7 tb2 as(
8 SELECT MGR,COMPANY,ptype
9 FROM TB B
10 WHERE NOT EXISTS(SELECT 1 FROM TB A
11 WHERE A.mgr=B.mgr AND A.company=B.company AND A.PTYPE='A')),
12 tb3 as
13 (SELECT MGR,COMPANY,ptype
14 FROM TB B
15 WHERE NOT EXISTS(SELECT 1 FROM TB A
16 WHERE A.mgr=B.mgr AND A.company=B.company AND A.PTYPE='A' OR A.PTYPE='B'))
17 select a.mgr,sum(decode(a.ptype,'A',1,0)) "含A类产品的集团个数",
18 count(b.ptype) "不含A类且含B类产品的集团个数",
19 NVL(count(c.ptype),0) "不含A或B类产品的集团个数"
20 from tb a,tb2 b,tb3 c
21 WHERE a.MGR=b.MGR(+) and a.company=b.company(+)
22 AND a.MGR=c.MGR(+) and a.company=c.company(+)
23 GROUP BY a.MGR
24 /
MGR 含A类产品的集团个数 不含A类且含B类产品的集团个数 不含A或B类产品的集团个数
--- ------------------- ---------------------------- ------------------------
C1 2 1 0
SQL>
但从结果看,许多简单的语句都不是想要的结果
能得出结果的又比较复杂最后还是觉得用这个方式来解决
是从ITPUB上提问得到解答
SELECT 客户经理
,COUNT(CASE WHEN CNT_A>0 THEN 1 END)
,COUNT(CASE WHEN CNT_A=0 AND CNT_B>0 THEN 1 END)
,COUNT(CASE WHEN CNT_A=0 AND CNT_B=0 THEN 1 END)
FROM (
SELECT 客户经理, 集团
,COUNT(CASE WHEN 产品类型='A' THEN 1 END) CNT_A
,COUNT(CASE WHEN 产品类型='B' THEN 1 END) CNT_B
FROM t_prdct
GROUP BY 客户经理, 集团
)
GROUP BY 客户经理