group by c1,c2 c2的所有值都在结果中展示

显示全部楼层 · 2014-5-4 10:26:20

本帖最后由圆桌布于 2015-7-2 11:23 编辑
表数据如下：
ID CITY
CUST_LEVEL
---------- ----------------------------- --------------------
1 北京
A
2 北京
B
3 北京
C
4 西安
A
5 西安
C
6 西安
D
7 北京
B
8 西安
D
9 西安
D

期望查询结果：
CITY
CUST_LEVEL
TOTAL
-------------------------------- -------------------- ----------
北京
A
1
北京
B
2
北京
C
1
北京
D
0
西安
A
1
西安
C
1
西安
D
3
西安
B
0
表创建脚本：
create table crm(id number primary key,city varchar2(50),cust_level varchar2(20));
insert into crm(id,city,cust_level) values(1,'北京','A');
insert into crm(id,city,cust_level) values(2,'北京','B');
insert into crm(id,city,cust_level) values(3,'北京','C');
insert into crm(id,city,cust_level) values(4,'西安','A');
insert into crm(id,city,cust_level) values(5,'西安','C');
insert into crm(id,city,cust_level) values(6,'西安','D');
insert into crm(id,city,cust_level) values(7,'北京','B');
insert into crm(id,city,cust_level) values(8,'西安','D');
insert into crm(id,city,cust_level) values(9,'西安','D');
使用group by 写法：
select city, cust_level, count(id) total
from crm
group by city, cust_level
order by city;
查询结果如下：
CITY
CUST_LEVEL
TOTAL
-------------------------------------------------- -------------------- ----------
北京
A
1
北京
B
2
北京
C
1
西安
A
1
西安
C
1
西安
D
3

希望CUST_LEVEL的每一个值A、B、C、D都在结果中展示，某CITY没有对应的CUST_LEVEL，TOTOL为0

千问 · 2014-5-4 10:26:20

先把cust_level的唯一值做一个中间结果，然后用这个中间结果与表中的数据做外连接，连接条件为cust_level(来自中间结果）=cust_level。对外连接的结果做group by city cust_level(来自中间结果）

千问 · 2014-5-4 10:26:20

bfc99 发表于 2015-7-2 11:25
先把cust_level的唯一值做一个中间结果，然后用这个中间结果与表中的数据做外连接，连接条件为cust_level( ...
select *
from (select distinct cust_level from crm) a
left join crm
on crm.cust_level = a.cust_level;
结果如下：
CUST_LEVEL
ID CITY
CUST_LEVEL
-------------------- ---------- -------------------------------------------------- --------------------
A
1 北京
A
B
2 北京
B
C
3 北京
C
A
4 西安
A
C
5 西安
C
D
6 西安
D
B
7 北京
B
D
8 西安
D
D
9 西安
D
外连接之后数据没有增多

千问 · 2014-5-4 10:26:20

LFET OUT PARTITION JOIN

千问 · 2014-5-4 10:26:20

SQL> select * from crm;
ID CITY CUST_LEVEL
---------- ---------- ----------
1 北京 A
2 北京 B
3 北京 C
4 西安 A
5 西安 C
6 西安 D
7 北京 B
8 西安 D
9 西安 D
9 rows selected
SQL>
SQL> selectt1.city,
2
t1.cust_level,
3
nvl(t2.total, 0) total
4from
5(select a.city,
6
b.cust_level
7 from (select distinct city from crm) a,
8(select distinct cust_level from crm) b) t1,
9(select city, cust_level, count(*) total from crm group by city, cust_level) t2
10where t1.city = t2.city(+)
11and t1.cust_level = t2.cust_level(+)
12order by 1, 2;
CITY CUST_LEVELTOTAL
---------- ---------- ----------
北京 A
1
北京 B
2
北京 C
1
北京 D
0
西安 A
1
西安 B
0
西安 C
1
西安 D
3
8 rows selected
SQL>

千问 · 2014-5-4 10:26:20

with t as (

select 1 id,'北京'name,'A'cnt from dual union all
select 2 id,'北京'name,'B'cnt from dual union all
select 3 id,'北京'name,'C'cnt from dual union all
select 4 id,'西安'name,'A'cnt from dual union all
select 5 id,'西安'name,'C'cnt from dual union all
select 6 id,'西安'name,'D'cnt from dual union all
select 7 id,'北京'name,'B'cnt from dual union all
SELECT 8 id,'西安'name,'D'cnt from dual union all
select 9 id,'西安'name,'D'cnt from dual /*union all*/)
SELECT t.name,t1.cnt,COUNT(t.cnt)
FROM T PARTITION BY(NAME)

RIGHT JOIN (SELECT DISTINCT CNT FROM T) T1

ON T.CNT = T1.CNT

GROUP BY t.name,t1.cnt

order BY NAME

千问 · 2014-5-4 10:26:20

圆桌布发表于 2015-7-2 11:32
select *
from (select distinct cust_level from crm) a
left join crm
考虑欠周,应该是先构建一个CITY和CUST_LEVEL的全组合。以下为修改后的代码。
select c.city, c.cust_level, sum(nvl2(crm.id, 1, 0))
from (select city, cust_level

from (select distinct city from crm) a,

(select distinct cust_level from crm) b) c
left join crm
on c.city = crm.city
and c.cust_level = crm.cust_level
group by c.city, c.cust_level
order by c.city, c.cust_level;

千问 · 2014-5-4 10:26:20

oracle_cj 发表于 2015-7-2 11:53
with t as (
...
这个好。

千问 · 2014-5-4 10:26:20

oracle_cj 发表于 2015-7-2 11:53
with t as (
...
学习！！

千问 · 2014-5-4 10:26:20

谢谢各位！