表结构:
人员编码 月份 工时 天数
001 1 12 5
001 2 20 6
001 3 18 8
002 1 12 5
002 2 15 6
002 3 18 7如何通过sql语句查询出如下显示结果
人员编码 内容 1月 2月 3月 ......12月
001 工时 12 20 18
001 天数 5 6 8
002 工时 12 15 18
002 天数 5 6 7希望高手帮帮忙,万分感谢
人员编码 月份 工时 天数
001 1 12 5
001 2 20 6
001 3 18 8
002 1 12 5
002 2 15 6
002 3 18 7如何通过sql语句查询出如下显示结果
人员编码 内容 1月 2月 3月 ......12月
001 工时 12 20 18
001 天数 5 6 8
002 工时 12 15 18
002 天数 5 6 7希望高手帮帮忙,万分感谢
解决方案 »
- 在oracle中定义了包,也定义了主体,但在调用函数的时候有错
- Oracle的字面量与绑定变量
- 审核执行错误ORA-06502:PL/SQL:数字或值错误:字符串缓冲区太小
- oci Direct Path Loading 问题
- 谁能给我解释一下这个小程序是什么意思?
- 哪位XDJM有Developer 2000,或知道下载的地址,小弟不胜感激
- varchar & varchar2有什么区别?
- 数据库链接问题?菜鸟!
- oracle9i中关于在一个存储过程中调用另外一个存储过程
- delphi中访问oracle数据库,不安装oracle client,要怎么做?
- 联合主键删除的问题
- 请大家帮我解决一个问题,我到那个帖子给分!
from (select 人员编码,sum(decode(月份,1,工时,0) 1月,
sum(decode(月份,2,工时,0) 2月,
sum(decode(月份,3,工时,0) 3月,
sum(decode(月份,4,工时,0) 4月,
sum(decode(月份,5,工时,0) 5月,
sum(decode(月份,6,工时,0) 6月,
sum(decode(月份,7,工时,0) 7月,
sum(decode(月份,8,工时,0) 8月,
sum(decode(月份,9,工时,0) 9月,
sum(decode(月份,10,工时,0) 10月,
sum(decode(月份,11,工时,0) 11月,
sum(decode(月份,12,工时,0) 12月
from table
group by 人员编码
union all
select 人员编码,sum(decode(月份,1,天数,0) 1月,
sum(decode(月份,2,天数,0) 2月,
sum(decode(月份,3,天数,0) 3月,
sum(decode(月份,4,天数,0) 4月,
sum(decode(月份,5,天数,0) 5月,
sum(decode(月份,6,天数,0) 6月,
sum(decode(月份,7,天数,0) 7月,
sum(decode(月份,8,天数,0) 8月,
sum(decode(月份,9,天数,0) 9月,
sum(decode(月份,10,天数,0) 10月,
sum(decode(月份,11,天数,0) 11月,
sum(decode(月份,12,天数,0) 12月
from table
group by 人员编码)
order by 人员编码;
from (select 人员编码,'工时' 内容,sum(decode(月份,1,工时,0) 1月,
sum(decode(月份,2,工时,0) 2月,
sum(decode(月份,3,工时,0) 3月,
sum(decode(月份,4,工时,0) 4月,
sum(decode(月份,5,工时,0) 5月,
sum(decode(月份,6,工时,0) 6月,
sum(decode(月份,7,工时,0) 7月,
sum(decode(月份,8,工时,0) 8月,
sum(decode(月份,9,工时,0) 9月,
sum(decode(月份,10,工时,0) 10月,
sum(decode(月份,11,工时,0) 11月,
sum(decode(月份,12,工时,0) 12月
from table
group by 人员编码,'工时'
union all
select 人员编码,'天数',sum(decode(月份,1,天数,0) 1月,
sum(decode(月份,2,天数,0) 2月,
sum(decode(月份,3,天数,0) 3月,
sum(decode(月份,4,天数,0) 4月,
sum(decode(月份,5,天数,0) 5月,
sum(decode(月份,6,天数,0) 6月,
sum(decode(月份,7,天数,0) 7月,
sum(decode(月份,8,天数,0) 8月,
sum(decode(月份,9,天数,0) 9月,
sum(decode(月份,10,天数,0) 10月,
sum(decode(月份,11,天数,0) 11月,
sum(decode(月份,12,天数,0) 12月
from table
group by 人员编码,'天数')
order by 人员编码,内容;
目前我只想到这个了,等等看其他人是否有别的方法实现
,sum("2月") as "2月"
,sum("3月") as "3月" from
(select a.人员编码,a.内容,case when 月份='1' then 值 end as "1月",
case when 月份='2' then 值 end as "2月",
case when 月份='3' then 值 end as "3月" from
(select 人员编码,月份,'工时' as 内容,工时 as 值 from test.tmp
union
select 人员编码,月份,'天数' as 内容 ,天数 as 值 from test.tmp) a) b group by 人员编码,内容
decode(rn,1,'工时',2,'天数')内容
sum(decode(月份,1,decode(rn,1,工时,2天数),0) 1月,
sum(decode(月份,2,decode(rn,1,工时,2天数),0) 2月,
sum(decode(月份,3,decode(rn,1,工时,2天数),0) 3月,
sum(decode(月份,4,decode(rn,1,工时,2天数),0) 4月,
sum(decode(月份,5,decode(rn,1,工时,2天数),0) 5月,
sum(decode(月份,6,decode(rn,1,工时,2天数),0) 6月,
sum(decode(月份,7,decode(rn,1,工时,2天数),0) 7月,
sum(decode(月份,8,decode(rn,1,工时,2天数),0) 8月,
sum(decode(月份,9,decode(rn,1,工时,2天数),0) 9月,
sum(decode(月份,10,decode(rn,1,工时,2天数),0) 10月,
sum(decode(月份,11,decode(rn,1,工时,2天数),0) 11月,
sum(decode(月份,12,decode(rn,1,工时,2天数),0) 12月
from mytab as a,(select rownum as rn from dual connect by rownum<=2) b
group by 人员编码,rn
order by 人员编码,rn;
(
COL_NAME VARCHAR2(20),
COL_VALUE NUMBER(4)
)
;
CREATE OR REPLACE TYPE TYPE_TEST_1_VARR AS VARRAY(19) OF TYPE_TEST_1; SELECT * FROM TEST_1 FOR UPDATE;SELECT T.NO,
A.COL_NAME,
SUM(DECODE(T.MONTH_ID, 1, A.COL_VALUE)) "1月",
SUM(DECODE(T.MONTH_ID, 2, A.COL_VALUE)) "2月",
SUM(DECODE(T.MONTH_ID, 3, A.COL_VALUE)) "3月"
FROM TEST_1 T,
TABLE(TYPE_TEST_1_VARR(TYPE_TEST_1('工号', T.GS),
TYPE_TEST_1('天数', T.GS))) A
GROUP BY T.NO, A.COL_NAME
先定义一个对象,用来存放表中的列名和值,再定义一个数据类型为该对象。
数组赋值用:
TABLE(TYPE_TEST_1_VARR(TYPE_TEST_1('工号', T.GS),
TYPE_TEST_1('天数', T.GS),.....))--有多少列定义多少值
名称为值对应的列名称,值为T表的对应值然后再转日期为横表
CREATE TABLE TEST
(
P_CODE VARCHAR2(4),
MONTH INT,
HOUR INT,
DAYS INT
)
INSERT INTO TEST VALUES('001', 1 , 12, 5);
INSERT INTO TEST VALUES('001', 2 , 20, 6);
INSERT INTO TEST VALUES('001', 3 , 18, 8);
INSERT INTO TEST VALUES('001', 4 , 12, 5);
INSERT INTO TEST VALUES('001', 5 , 15, 6);
INSERT INTO TEST VALUES('001', 6 , 18, 7);
INSERT INTO TEST VALUES('001', 7 , 12, 5);
INSERT INTO TEST VALUES('001', 8 , 20, 6);
INSERT INTO TEST VALUES('001', 9 , 18, 8);
INSERT INTO TEST VALUES('001', 10, 12, 5);
INSERT INTO TEST VALUES('001', 11, 15, 6);
INSERT INTO TEST VALUES('001', 12, 18, 7);
INSERT INTO TEST VALUES('002', 1 , 12, 5);
INSERT INTO TEST VALUES('002', 2 , 20, 6);
INSERT INTO TEST VALUES('002', 3 , 18, 8);
INSERT INTO TEST VALUES('002', 4 , 12, 5);
INSERT INTO TEST VALUES('002', 5 , 15, 6);
INSERT INTO TEST VALUES('002', 6 , 18, 7);
INSERT INTO TEST VALUES('002', 7 , 12, 5);
INSERT INTO TEST VALUES('002', 8 , 20, 6);
INSERT INTO TEST VALUES('002', 9 , 18, 8);
INSERT INTO TEST VALUES('002', 10, 12, 5);
INSERT INTO TEST VALUES('002', 11, 15, 6);
INSERT INTO TEST VALUES('002', 12, 18, 7);
COMMIT;
SELECT *
FROM (SELECT P_CODE,
MONTH,
HOUR,
'工时' AS TYPE
FROM TEST) ---
PIVOT(MAX(HOUR) FOR MONTH IN (1 AS JAN, 2 AS FEB, 3 AS MAR, 4 AS APR, 5 AS MAY, 6 AS JUN, 7 AS JUL, 8 AS AUG, 9 AS SEP, 10 AS OCT, 11 AS NOV, 12 AS DEC))
UNION
SELECT *
FROM (SELECT P_CODE,
MONTH,
DAYS,
'天数'
FROM TEST) ---
PIVOT(MAX(DAYS) FOR MONTH IN (1 AS JAN, 2 AS FEB, 3 AS MAR, 4 AS APR, 5 AS MAY, 6 AS JUN, 7 AS JUL, 8 AS AUG, 9 AS SEP, 10 AS OCT, 11 AS NOV, 12 AS DEC))
P_CODE TYPE JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
001 工时 12 20 18 12 15 18 12 20 18 12 15 18
001 天数 5 6 8 5 6 7 5 6 8 5 6 7
002 工时 12 20 18 12 15 18 12 20 18 12 15 18
002 天数 5 6 8 5 6 7 5 6 8 5 6 7
是这行提示缺少右括号
select 人员编码,
decode(rn,1,'工时',2,'天数')内容
sum(decode(月份,1,decode(rn,1,工时,2天数),0)) 1月,
sum(decode(月份,2,decode(rn,1,工时,2天数),0)) 2月,
sum(decode(月份,3,decode(rn,1,工时,2天数),0)) 3月,
sum(decode(月份,4,decode(rn,1,工时,2天数),0)) 4月,
sum(decode(月份,5,decode(rn,1,工时,2天数),0)) 5月,
sum(decode(月份,6,decode(rn,1,工时,2天数),0)) 6月,
sum(decode(月份,7,decode(rn,1,工时,2天数),0)) 7月,
sum(decode(月份,8,decode(rn,1,工时,2天数),0)) 8月,
sum(decode(月份,9,decode(rn,1,工时,2天数),0)) 9月,
sum(decode(月份,10,decode(rn,1,工时,2天数),0)) 10月,
sum(decode(月份,11,decode(rn,1,工时,2天数),0)) 11月,
sum(decode(月份,12,decode(rn,1,工时,2天数),0)) 12月
from mytab as a,(select rownum as rn from dual connect by rownum<=2) b
group by 人员编码,rn
order by 人员编码,rn;
decode(rn,1,'工时',2,'天数') 内容
sum(decode(月份,1,decode(rn,1,工时,2天数),0)) 1月,
sum(decode(月份,2,decode(rn,1,工时,2天数),0)) 2月,
sum(decode(月份,3,decode(rn,1,工时,2天数),0)) 3月,
sum(decode(月份,4,decode(rn,1,工时,2天数),0)) 4月,
sum(decode(月份,5,decode(rn,1,工时,2天数),0)) 5月,
sum(decode(月份,6,decode(rn,1,工时,2天数),0)) 6月,
sum(decode(月份,7,decode(rn,1,工时,2天数),0)) 7月,
sum(decode(月份,8,decode(rn,1,工时,2天数),0)) 8月,
sum(decode(月份,9,decode(rn,1,工时,2天数),0)) 9月,
sum(decode(月份,10,decode(rn,1,工时,2天数),0)) 10月,
sum(decode(月份,11,decode(rn,1,工时,2天数),0)) 11月,
sum(decode(月份,12,decode(rn,1,工时,2天数),0)) 12月
from mytab a,(select rownum as rn from dual connect by rownum<=2) b
group by 人员编码,rn
order by 人员编码,rn;
decode(rn,1,'工时',2,'天数') 内容,
sum(decode(月份,1,decode(rn,1,工时,2天数),0)) 1月,
sum(decode(月份,2,decode(rn,1,工时,2天数),0)) 2月,
sum(decode(月份,3,decode(rn,1,工时,2天数),0)) 3月,
sum(decode(月份,4,decode(rn,1,工时,2天数),0)) 4月,
sum(decode(月份,5,decode(rn,1,工时,2天数),0)) 5月,
sum(decode(月份,6,decode(rn,1,工时,2天数),0)) 6月,
sum(decode(月份,7,decode(rn,1,工时,2天数),0)) 7月,
sum(decode(月份,8,decode(rn,1,工时,2天数),0)) 8月,
sum(decode(月份,9,decode(rn,1,工时,2天数),0)) 9月,
sum(decode(月份,10,decode(rn,1,工时,2天数),0)) 10月,
sum(decode(月份,11,decode(rn,1,工时,2天数),0)) 11月,
sum(decode(月份,12,decode(rn,1,工时,2天数),0)) 12月
from mytab a,(select rownum as rn from dual connect by rownum<=2) b
group by 人员编码,rn
order by 人员编码,rn;是这样的吧,你们能的能运行过去?
decode(rn,1,'工时',2,'天数') 内容,
sum(decode(月份,1,decode(rn,1,工时,2,天数),0)) 1月,
sum(decode(月份,2,decode(rn,1,工时,2,天数),0)) 2月,
sum(decode(月份,3,decode(rn,1,工时,2,天数),0)) 3月,
sum(decode(月份,4,decode(rn,1,工时,2,天数),0)) 4月,
sum(decode(月份,5,decode(rn,1,工时,2,天数),0)) 5月,
sum(decode(月份,6,decode(rn,1,工时,2,天数),0)) 6月,
sum(decode(月份,7,decode(rn,1,工时,2,天数),0)) 7月,
sum(decode(月份,8,decode(rn,1,工时,2,天数),0)) 8月,
sum(decode(月份,9,decode(rn,1,工时,2,天数),0)) 9月,
sum(decode(月份,10,decode(rn,1,工时,2,天数),0)) 10月,
sum(decode(月份,11,decode(rn,1,工时,2,天数),0)) 11月,
sum(decode(月份,12,decode(rn,1,工时,2,天数),0)) 12月
from mytab a,(select rownum as rn from dual connect by rownum<=2) b
group by 人员编码,rn
order by 人员编码,rn;
这行后面的括号是全角的,改成半角的
decode(rn,1,'工时',2,'天数')内容
sum(decode(月份,1,decode(rn,1,工时,2天数),0) 1月,
sum(decode(月份,2,decode(rn,1,工时,2天数),0) 2月,
sum(decode(月份,3,decode(rn,1,工时,2天数),0) 3月,
sum(decode(月份,4,decode(rn,1,工时,2天数),0) 4月,
sum(decode(月份,5,decode(rn,1,工时,2天数),0) 5月,
sum(decode(月份,6,decode(rn,1,工时,2天数),0) 6月,
sum(decode(月份,7,decode(rn,1,工时,2天数),0) 7月,
sum(decode(月份,8,decode(rn,1,工时,2天数),0) 8月,
sum(decode(月份,9,decode(rn,1,工时,2天数),0) 9月,
sum(decode(月份,10,decode(rn,1,工时,2天数),0) 10月,
sum(decode(月份,11,decode(rn,1,工时,2天数),0) 11月,
sum(decode(月份,12,decode(rn,1,工时,2天数),0) 12月
from mytab as a,(select rownum as rn from dual connect by rownum<=2) b
group by 人员编码,rn
order by 人员编码,rn;
decode(rn,1,'工时',2,'天数') 内容
sum(decode(月份,1,decode(rn,1,工时,2天数),0) 1月,
sum(decode(月份,2,decode(rn,1,工时,2天数),0) 2月,
sum(decode(月份,3,decode(rn,1,工时,2天数),0) 3月,
sum(decode(月份,4,decode(rn,1,工时,2天数),0) 4月,
sum(decode(月份,5,decode(rn,1,工时,2天数),0) 5月,
sum(decode(月份,6,decode(rn,1,工时,2天数),0) 6月,
sum(decode(月份,7,decode(rn,1,工时,2天数),0) 7月,
sum(decode(月份,8,decode(rn,1,工时,2天数),0) 8月,
sum(decode(月份,9,decode(rn,1,工时,2天数),0) 9月,
sum(decode(月份,10,decode(rn,1,工时,2天数),0) 10月,
sum(decode(月份,11,decode(rn,1,工时,2天数),0) 11月,
sum(decode(月份,12,decode(rn,1,工时,2天数),0) 12月
from mytab as a,(select rownum as rn from dual connect by rownum<=2) b
group by 人员编码,rn
order by 人员编码,rn;
select rownum as rn from dual connect by rownum<=2
此处的rownum为何只能<=2,当把rownum设为<=3的时候就提示“用户数据中的connect by 循环”
请问如何解决
select rownum as rn from dual connect by rownum <=3这句就报错“用户数据中的connect by 循环”
只要大于2就报
---------
1
2
3
oracle 9i就没问题
可以确定这种语法在oracle7中是不能执行的
from (select 人员编码,sum(decode(月份,1,工时,0) 1月,
sum(decode(月份,2,工时,0) 2月,
sum(decode(月份,3,工时,0) 3月,
sum(decode(月份,4,工时,0) 4月,
sum(decode(月份,5,工时,0) 5月,
sum(decode(月份,6,工时,0) 6月,
sum(decode(月份,7,工时,0) 7月,
sum(decode(月份,8,工时,0) 8月,
sum(decode(月份,9,工时,0) 9月,
sum(decode(月份,10,工时,0) 10月,
sum(decode(月份,11,工时,0) 11月,
sum(decode(月份,12,工时,0) 12月
from table
group by 人员编码
union all
select 人员编码,sum(decode(月份,1,天数,0) 1月,
sum(decode(月份,2,天数,0) 2月,
sum(decode(月份,3,天数,0) 3月,
sum(decode(月份,4,天数,0) 4月,
sum(decode(月份,5,天数,0) 5月,
sum(decode(月份,6,天数,0) 6月,
sum(decode(月份,7,天数,0) 7月,
sum(decode(月份,8,天数,0) 8月,
sum(decode(月份,9,天数,0) 9月,
sum(decode(月份,10,天数,0) 10月,
sum(decode(月份,11,天数,0) 11月,
sum(decode(月份,12,天数,0) 12月
from table
group by 人员编码)
order by 人员编码;