id | brand |
+-------------+-------+|
| 1001 | A |
| 1002 | A |
| 1002 | B |
| 1002 | B |
| 1002 | B |
| 1002 | C |
| 1003 | A |
| 1003 | B |
| 1003 | C |
| 1004 | A |
| 1004 | A |
| 1004 | B |
| 1005 | A |
| 1005 | A |
假设我有如上的数据,表的名字假设为info吧,我想得到如下的的输出,
就是多出来的num字段,是对id和brand组合的计数统计,关键问题是,比如1001和B的组合是没有的,但是也要给出一个0,来表示没有这个组合,其实如果是直接忽略掉次数为0的这种情况是比较简单的,但是一定要把为0的情况也列出来。我实在想不出来了,求助大家帮帮忙,谢谢了 id | brand |num
+-------------+-------+-------
| 1001 | A |2
| 1001 | B |0
| 1001 | C |0
| 1002 | A |1
| 1002 | B |3
| 1002 | C |1
| 1003 | A |1
| 1003 | B |1
| 1003 | C |1
| 1004 | A |2
| 1004 | B |1
| 1004 | C |0
| 1005 | A |2
| 1005 | B |0
| 1005 | C |0
+-------------+-------+|
| 1001 | A |
| 1002 | A |
| 1002 | B |
| 1002 | B |
| 1002 | B |
| 1002 | C |
| 1003 | A |
| 1003 | B |
| 1003 | C |
| 1004 | A |
| 1004 | A |
| 1004 | B |
| 1005 | A |
| 1005 | A |
假设我有如上的数据,表的名字假设为info吧,我想得到如下的的输出,
就是多出来的num字段,是对id和brand组合的计数统计,关键问题是,比如1001和B的组合是没有的,但是也要给出一个0,来表示没有这个组合,其实如果是直接忽略掉次数为0的这种情况是比较简单的,但是一定要把为0的情况也列出来。我实在想不出来了,求助大家帮帮忙,谢谢了 id | brand |num
+-------------+-------+-------
| 1001 | A |2
| 1001 | B |0
| 1001 | C |0
| 1002 | A |1
| 1002 | B |3
| 1002 | C |1
| 1003 | A |1
| 1003 | B |1
| 1003 | C |1
| 1004 | A |2
| 1004 | B |1
| 1004 | C |0
| 1005 | A |2
| 1005 | B |0
| 1005 | C |0
解决方案 »
- 使用OLEDB,报“ORA-12154,TNS无法处理服务名”
- 怎么连接结果
- windows7 适合装什么版本的oracle
- 新手问题
- 用SEHLL删除ORACLE用户!
- 一次insert500万条记录,是一次提交快,还是分几次提交快
- sql*plus 无法登陆,提示错误说,ORA-12560:TNS:协议适配器错误,谢谢了!
- Oracle 8.1.7 在 Redhat 7.3 安装的问题。
- 急问!
- 请问:类似于select id,isnull(name,'test') from table where....在oracle 中怎么写?
- 如何导入.dmp文件到数据库中?
- 急啊,请教oracle 的定时job
SELECT 1001 id,'A' brand FROM dual
UNION ALL
SELECT 1002,'A' FROM dual
UNION ALL
SELECT 1002,'B' FROM dual
UNION ALL
SELECT 1002,'B' FROM dual
UNION ALL
SELECT 1002,'B' FROM dual
UNION ALL
SELECT 1002,'C' FROM dual
UNION ALL
SELECT 1003,'A' FROM dual
UNION ALL
SELECT 1003,'B' FROM dual
UNION ALL
SELECT 1003,'C' FROM dual
UNION ALL
SELECT 1004,'A' FROM dual
UNION ALL
SELECT 1004,'A' FROM dual
UNION ALL
SELECT 1004,'B' FROM dual
UNION ALL
SELECT 1005,'A' FROM dual
UNION ALL
SELECT 1005,'A' FROM dual
)
SELECT * FROM
(
SELECT id,brand,0 num FROM
(SELECT DISTINCT id FROM t) t1, (SELECT DISTINCT brand FROM t) t2
WHERE (id,brand) NOT IN (SELECT id,brand FROM t)
UNION ALL
SELECT DISTINCT id,brand,Count(*) over (PARTITION BY id,brand)num FROM T
)ORDER BY id,brand;output:
1001 A 1
1001 B 0
1001 C 0
1002 A 1
1002 B 3
1002 C 1
1003 A 1
1003 B 1
1003 C 1
1004 A 2
1004 B 1
1004 C 0
1005 A 2
1005 B 0
1005 C 0
select t23.id ,t23.brand,nvl(t1num,0)
from
(select t1.id ,t1.brand,count(1) t1num from
info t1
group by t1.id ,t1.brand
)t11
right join
(
select t2.id ,t3.brand
from
( select distinct id
from
info
)t2,
(
select distinct brand
from
info
)t3
) t23
on t11.id =t23.id and t11.brand=t23.brand
SELECT c.id, c.brand, nvl(d.num, 0) num
FROM (SELECT a.id, b.brand
FROM info a, (SELECT distinct brand FROM info) b
order by a.id) c
left join (select id, brand, count(1) num from info group by id, brand) d
on c.id = d.id
and c.brand = d.brand
order by c.id,c.brand
/**创建测试用表**/
CREATE TABLE info(
ID VARCHAR2(10),
brand VARCHAR2(2)
);
/**插入测试数据**/
insert into info values('1001','A');
insert into info values('1002','A');
insert into info values('1002','B');
insert into info values('1002','B');
insert into info values('1002','B');
insert into info values('1002','C');
insert into info values('1003','A');
insert into info values('1003','B');
insert into info values('1003','C');
insert into info values('1004','A');
insert into info values('1004','A');
insert into info values('1004','B');
insert into info values('1005','A');
insert into info values('1005','A');
/**对笛卡尔积的结果求根**/
SELECT A.ID,
B.BRAND,
SQRT(SUM(CASE WHEN A.ID=B.ID AND A.BRAND=B.BRAND THEN 1 ELSE 0 END)) AS NUM
FROM INFO A, INFO B
GROUP BY A.ID,B.BRAND
ORDER BY 1, 2
1001 A 1
1001 B 0
1001 C 0
1002 A 1
1002 B 3
1002 C 1
1003 A 1
1003 B 1
1003 C 1
1004 A 2
1004 B 1
1004 C 0
1005 A 2
1005 B 0
1005 C 0