某商品有四种类型A,B,C,D,每种商品有三种状态甲,乙,丙,如何用一条SQL查出每种商品每种状态的个数?
这种结果:
甲 乙 丙
A 1 0 0
B 2 11 11
C 1 1 1
D 1 1 1
要求?oracle和mysql都要
这种结果:
甲 乙 丙
A 1 0 0
B 2 11 11
C 1 1 1
D 1 1 1
要求?oracle和mysql都要
解决方案 »
- pl sql(9i):如何判斷某個欄位中的內容有多少行及每行的長度是多少?
- linux下,配置oracle的远程连接..在线等.
- 关于oracle英文字符集乱码的问题,查询显示乱码的问题。
- 还是不清楚为什么会是这种情况
- 关于oracle备份
- Oracle 高级复制更换IP后复制关系失败!(问题已详细描述,急!!!在线等待)
- 如何完全删除一个库(不是简单的把库从目录树中删除),包括硬盘上的库文件,还有服务中的项目也要删除。
- Net Configuration Assistant 测试不能成功通过
- 请教一个简单的SQL语句
- 绝对送分,异常简单!
- 数据仓库中的merge into 和 delete + insert into
- 单行注释REM与--组合的问题
(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]
(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]
from 商品表
group by 商品,商品类型,商品状态
order by 商品
FROM
(SELECT 商品,sum(数量) c where 状态 = '甲') X,
(SELECT 商品,sum(数量) c where 状态 = '乙') Y,
(SELECT 商品,sum(数量) c where 状态 = '丙') Z
WHERE X.商品 = Y.商品
AND X.商品 = Z.商品 ;
商品类型,
sum(decode(状态甲,1,0)) 状态甲,
sum(decode(状态乙,1,0)) 状态乙,
sum(decode(状态丙,1,0)) 状态丙
from 商品表
group by 商品类型;