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
------解决方案--------------------
- SQL code
WITH t AS(SELECT 1001 id,'A' brand FROM dualUNION ALL SELECT 1002,'A' FROM dualUNION ALL SELECT 1002,'B' FROM dualUNION ALL SELECT 1002,'B' FROM dualUNION ALL SELECT 1002,'B' FROM dualUNION ALL SELECT 1002,'C' FROM dualUNION ALL SELECT 1003,'A' FROM dualUNION ALL SELECT 1003,'B' FROM dualUNION ALL SELECT 1003,'C' FROM dualUNION ALL SELECT 1004,'A' FROM dualUNION ALL SELECT 1004,'A' FROM dualUNION ALL SELECT 1004,'B' FROM dualUNION ALL SELECT 1005,'A' FROM dualUNION 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 11001 B 01001 C 01002 A 11002 B 31002 C 11003 A 11003 B 11003 C 11004 A 21004 B 11004 C 01005 A 21005 B 01005 C 0
------解决方案--------------------
- SQL code
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)t11right join(select t2.id ,t3.brandfrom( select distinct id from info )t2, ( select distinct brand from info )t3) t23on 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
------解决方案--------------------
- SQL code
/**创建测试用表**/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 NUMFROM INFO A, INFO B GROUP BY A.ID,B.BRANDORDER BY 1, 2