表为storage
列为
Index Product Status
数据是
1 P1 A
2 P1 B
3 P1 C
4 P2 A
5 P2 C
. . .
. . .
N PM A现在要求用一条语句查询这个库存表中每种产品的总数及其处在每个状态的数量
即要求每条记录集的是以下格式
Product Sum(*) Sum(Status_A) Sum(Status_B) Sum(Status_C)
如 P1 100 60 1 39
PM 2 1 1 0注:产品Product的种类是无限的,状态status有限可固定。
列为
Index Product Status
数据是
1 P1 A
2 P1 B
3 P1 C
4 P2 A
5 P2 C
. . .
. . .
N PM A现在要求用一条语句查询这个库存表中每种产品的总数及其处在每个状态的数量
即要求每条记录集的是以下格式
Product Sum(*) Sum(Status_A) Sum(Status_B) Sum(Status_C)
如 P1 100 60 1 39
PM 2 1 1 0注:产品Product的种类是无限的,状态status有限可固定。
解决方案 »
- alter system checkpoint 没工作么?
- Oracle 10g安装,升级
- Oracle物化视图数据同步
- 用oracle做个定时任务 在月末更改数据表名时如有插入数据,这时是否安全
- oracle三种关联都有啥区别啊?
- 58减去(当前系统时间-人员出身日期)<=4 个月,请问如何用oracel语言实现呢??
- @SPCREATE,建立STATSPACK的问题(随便问问)
- 如何查询哪些表把某一个表的任何列当作了外键?
- 关于默认值问题!十万火急,在线等待!
- oracle中交叉表怎么样来查询?
- 求一oracle查询sql
- 菜鸟请教各位下,pl/sql 等工具连接Oracle数据库时,如何返回结果
product,
sum(case when status='A' then 1 end),
sum(case when status='B' then 1 end),
sum(case when status='C' then 1 end)
from storage
group by product
with storage as
(select 1 IND,'P1' Product,'A' Status from dual
union all
select 2,'p1','B' from dual
union all
select 3,'p1','C' from dual
union all
select 4,'p2','A' from dual
union all
select 5,'p2','B' from dual
)
select PRODUCT,
SUM(DECODE(STATUS, 'A', 1, 0)) A,
SUM(DECODE(STATUS, 'B', 1, 0)) B,
SUM(DECODE(STATUS, 'C', 1, 0)) C
from storage
GROUP BY PRODUCTPRODUCT A B C
P1 1 0 0
p1 0 1 1
p2 1 1 0
--定义表,字段时最好不要用oracle保留字
with stor as(
select 1 indx,'p1' product,'A' status from dual union all
select 2,'p1','B' from dual union all
select 3,'p1','C' from dual union all
select 4,'p2','A' from dual union all
select 5,'p2','C' from dual union all
select 20,'p1','A' from dual union all
select 11,'p2','B' from dual union all
select 10,'p3','D' from dual)
select product,
sum(indx) sum_indx,
sum(case status when 'A' then indx else 0 end) sum_status_a,
sum(case status when 'B' then indx else 0 end) sum_status_b,
sum(case status when 'C' then indx else 0 end) sum_status_c,
sum(case status when 'D' then indx else 0 end) sum_status_d
from stor
group by product;
PRODUCT SUM_INDX SUM_STATUS_A SUM_STATUS_B SUM_STATUS_C SUM_STATUS_D
------- ---------- ------------ ------------ ------------ ------------
p1 26 21 2 3 0
p2 20 4 11 5 0
p3 10 0 0 0 10
select product,
count(status) "总数",
sum(decode(status,'A',1,0)) "A状态数",
sum(decode(status,'B',1,0)) "B状态数",
sum(decode(status,'C',1,0)) "C状态数",
sum(decode(status,'D',1,0)) "D状态数"
from storage
group by product;
select product Product,count(status) sum*,sum(decode(status,'A',1,0)) sumA,
sum(decode(status,'B',1,0)) sumB,sum(decode(status,'C',1,0)) sumC
from storage group by product
一般固定条件的行转列都用 decode 或 case when 实现
楼主再看下这个,都是行转列的问题分固定和不固定的转换
http://wenku.baidu.com/view/88aebabbfd0a79563c1e7268.html
select product, A, B, C, (A + B + C) sumall
from select product,
sum(case
when status = 'A' then
1
end) A,
sum(case
when status = 'B' then
1
end) B,
sum(case
when status = 'C' then
1
end) C
from storage
group by product)SQL二
select product,
sum(A) A,
sum(B) B,
sum(C) C,
(sum(A) + sum(B) + sum(C)) sumall,
from (select product 0 A, 0 B, count(Index) C
from storage
where Status = 'C'
group by product
union all
select product 0 A, count(Index) B, 0 C
from storage
where Status = 'B'
group by product
union all
select product count(Index) A, 0 B, 0 C
from storage
where Status = 'A'
group by product) t
group t.product
(SELECT 1 IND, 'p1' PRODUCT, 'a' STATUS
FROM DUAL
UNION ALL
SELECT 2, 'p1', 'b'
FROM DUAL
UNION ALL
SELECT 3, 'p1', 'c' FROM DUAL)
SELECT PRODUCT,
SUM(DECODE(STATUS, '1', 1, 1)) A,
SUM(DECODE(STATUS, 'a', 1, 0)) A,
SUM(DECODE(STATUS, 'b', 1, 0)) B,
SUM(DECODE(STATUS, 'c', 1, 0)) C
FROM STORAGE
GROUP BY PRODUCT;