求一条SQL

某商品有四种类型A,B,C,D，每种商品有三种状态甲，乙，丙，如何用一条SQL查出每种商品每种状态的个数？
这种结果：
  甲 乙 丙
A 1 0 0
B 2 11 11
C 1 1 1
D 1 1 1
要求？oracle和mysql都要

SQL code

with T as 
(select 'a' a,'甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
union all
select 'b','甲' b, '乙' c, '丙' d from dual
union all
select 'd','甲' b, '乙' c, '丙' d from dual
union all
select 'd','甲' b, '乙' c, '丙' d from dual
union all
select 'd','甲' b, '乙' c, '丙' d from dual
union all
select 'c','甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
)

with T as 
(select 'a' a,'甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
union all
select 'b','甲' b, '乙' c, '丙' d from dual
union all
select 'd','甲' b, '乙' c, '丙' d from dual
union all
select 'd','甲' b, '乙' c, '丙' d from dual
union all
select 'd','甲' b, '乙' c, '丙' d from dual
union all
select 'c','甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
)
select a,count(b),count(c),count(d) from T group by a[/code]

[code=SQL]with T as 
(select 'a' a,'甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
union all
select 'b','甲' b, '乙' c, '丙' d from dual
union all
select 'd','甲' b, '乙' c, '丙' d from dual
union all
select 'd','甲' b, '乙' c, '丙' d from dual
union all
select 'd','甲' b, '乙' c, '丙' d from dual
union all
select 'c','甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
union all
select 'a','甲' b, '乙' c, '丙' d from dual
)
select a,count(b) 甲,count(c) 乙,count(d) 丙 from T group by a[

---result:
a 5 5 5
b 1 1 1
c 1 1 1
d 3 3 3

/code]

你的表结构是啥？

多给点字段啊

select 商品，商品类型，商品状态，count(商品)
from 商品表
group by 商品，商品类型，商品状态
order by 商品

不给出表结构，只要结果，让人怎么写SQL？

SQL code

SELECT X.商品,X.C, Y.C, Z.C
FROM
    (SELECT 商品,sum(数量) c where 状态 = '甲') X,
    (SELECT 商品,sum(数量) c where 状态 = '乙') Y,
    (SELECT 商品,sum(数量) c where 状态 = '丙') Z
WHERE X.商品 = Y.商品
  AND X.商品 = Z.商品 ;

select 
  商品类型,
  sum(decode(状态甲,1,0)) 状态甲,
  sum(decode(状态乙,1,0)) 状态乙,
  sum(decode(状态丙,1,0)) 状态丙
from 商品表
group by 商品类型;