WITH t AS(
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

------解决方案--------------------
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
)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


------解决方案--------------------

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 NUM
FROM INFO A, INFO B 
GROUP