我知道是要进行行列转换,但是写了半天,效果不对。
这个是效果图:下面是数据库中的值:下面是我的SQL:
select to_char(run_date, 'yyyy-mm-dd') as run_date,
max(case team_name when '甲值' then team_name else '' end) as jiazhi,
max(case team_name when '乙值' then team_name else '' end) as yizhi,
max(case team_name when '丙值' then team_name else '' end) as binzhi,
max(case team_name when '丁值' then team_name else '' end) as dinzhi,
max(case team_name when '戊值' then team_name else '' end) as wuzhi,
begin_time
from V_work_time t group by run_date,team_name,begin_time order by t.run_date desc,begin_time asc
下面是我的sql执行效果:同一天的甲乙丙丁戊不在同一行,还有后面的begin_time不知道如何处理,麻烦大家给点提示或者帮忙写一下,谢谢。
这个是效果图:下面是数据库中的值:下面是我的SQL:
select to_char(run_date, 'yyyy-mm-dd') as run_date,
max(case team_name when '甲值' then team_name else '' end) as jiazhi,
max(case team_name when '乙值' then team_name else '' end) as yizhi,
max(case team_name when '丙值' then team_name else '' end) as binzhi,
max(case team_name when '丁值' then team_name else '' end) as dinzhi,
max(case team_name when '戊值' then team_name else '' end) as wuzhi,
begin_time
from V_work_time t group by run_date,team_name,begin_time order by t.run_date desc,begin_time asc
下面是我的sql执行效果:同一天的甲乙丙丁戊不在同一行,还有后面的begin_time不知道如何处理,麻烦大家给点提示或者帮忙写一下,谢谢。
解决方案 »
- 请问oracle的全数据实现从10G到11G迁移。如何减少错误?
- set role 角色名;与alter user 用户 default role 角色名1;区别?
- 谁知道Orcale中给用户赋权限sql语句是?---急!!!!
- 下面我的oracle语句为什么报错?
- 数据累加,SQL怎么实现呢?
- 如何减少日志文件的数量?
- 请教一个insert速度问题
- 请教大家:我把12机器上数据库的一个大表数据(1000万),怎么快速倒到15机器的数据库里去,不用数据连接(dataconnection)倒!
- 高分求救!找叶子节点!
- [高分求助]oracle中的查询问题
- 请大家帮我看看这个存储过程错在了什么地方 谢谢
- 大数据量清理办法求助,3800W数据。
换个方式大概像这样
select SUM(run_date) '值班日期',SUM(jia) '甲值时间',SUM(yi) '乙值时间',SUM(bing) '丙值时间',SUM(ding) '丁值时间',SUM(wu) '戊值时间'
from (
select run_date,begin_time jia,0 yi,0 bing,0 ding,0 wu
from V_work_time where team_name='甲值'
union all
select run_date,0 jia,begin_time yi,0 bing,0 ding,0 wu
from V_work_time where team_name='乙值'
union all
select run_date,0 jia,0 yi,begin_time bing,0 ding,0 wu
from V_work_time where team_name='丙值'
union all
select run_date,0 jia,0 yi,0 bing,begin_time ding,0 wu
from V_work_time where team_name='丁值'
union all
select run_date,0 jia,0 yi,0 bing,0 ding,begin_time wu
from V_work_time where team_name='戊值'
);
值班时间为0的就是不值班的啥!?你说有0点值班的?呵呵呵呵
反正语法大概差不多,我主要是想知道写法。
if OBJECT_ID('teamtab') is not null
drop table teamtab
go
create table teamtab(
team_id int identity(1,1) primary key,
team_name nvarchar(20) not null --团队名称
)
go
insert into teamtab(team_name) values('甲')
insert into teamtab(team_name) values('乙')
insert into teamtab(team_name) values('丙')
insert into teamtab(team_name) values('丁')
insert into teamtab(team_name) values('戊')
insert into teamtab(team_name) values('己')if OBJECT_ID('worktimetab') is not null
drop table worktimetab
go
create table worktimetab(
workid int identity(1,1) primary key,
work_begin varchar(10) not null,
work_end varchar(10) not null
)
go
insert into worktimetab(work_begin,work_end) values('2:00','8:00')
insert into worktimetab(work_begin,work_end) values('8:00','14:00')
insert into worktimetab(work_begin,work_end) values('14:00','20:00')
insert into worktimetab(work_begin,work_end) values('20:00','2:00')if OBJECT_ID('runtab') is not null
drop table runtab
go
create table runtab(
run_date date not null,
time_id int not null,
team_id int not null
)
goinsert into runtab(run_date,time_id,team_id) values('2013-09-30',1,1)
insert into runtab(run_date,time_id,team_id) values('2013-09-30',2,2)
insert into runtab(run_date,time_id,team_id) values('2013-09-30',3,3)
insert into runtab(run_date,time_id,team_id) values('2013-09-30',4,5)
insert into runtab(run_date,time_id,team_id) values('2013-09-29',1,1)
insert into runtab(run_date,time_id,team_id) values('2013-09-29',2,3)
insert into runtab(run_date,time_id,team_id) values('2013-09-29',3,5)
insert into runtab(run_date,time_id,team_id) values('2013-09-29',4,4)
insert into runtab(run_date,time_id,team_id) values('2013-09-28',1,2)
insert into runtab(run_date,time_id,team_id) values('2013-09-28',2,4)
insert into runtab(run_date,time_id,team_id) values('2013-09-28',3,5)
insert into runtab(run_date,time_id,team_id) values('2013-09-28',4,6)
insert into runtab(run_date,time_id,team_id) values('2013-09-27',1,5)
insert into runtab(run_date,time_id,team_id) values('2013-09-27',2,2)
insert into runtab(run_date,time_id,team_id) values('2013-09-27',3,3)
insert into runtab(run_date,time_id,team_id) values('2013-09-27',4,1)
insert into runtab(run_date,time_id,team_id) values('2013-09-26',1,2)
insert into runtab(run_date,time_id,team_id) values('2013-09-26',2,4)
insert into runtab(run_date,time_id,team_id) values('2013-09-26',1,3)select t1.run_date,(case time_id when 1 then '一' when 2 then '二' when 3 then '三' when 4 then '四' end) as btime,t3.team_name from runtab t1 inner join worktimetab t2 on t1.time_id=t2.workid inner join teamtab t3 on t1.time_id=t3.team_id要实现的效果就是我一楼的效果图那样,
时间|甲|乙|丙|丁....(这里随teamtab的记录条数增加而增加),然后下面的记录就是时间和上班的那个组的名字。
插入时间的时候 也是自己转换的 小麻烦了点 下面是通过动态sql写出来的 有执行结果CREATE OR REPLACE PROCEDURE T_TEST
IS
V_SQL NVARCHAR2(2000);
CURSOR CURSOR_1 IS
SELECT team_name
FROM teamtab T;
BEGIN
V_SQL := 'SELECT run_date ';
FOR V_TB IN CURSOR_1
LOOP
V_SQL := V_SQL || ',' || 'MAX(DECODE(A.team_name,'''
|| V_TB.team_name || ''',DECODE(TIME_ID,1,''一'',2,''二'',3,''三'',4,''四''),NULL)) AS "'|| V_TB.team_name ||'"';
END LOOP;
V_SQL := V_SQL || ' FROM teamtab a,runtab b' ;
V_SQL := V_SQL || ' WHERE A.TEAM_ID = B.TEAM_ID';
V_SQL := V_SQL || ' GROUP BY RUN_DATE ORDER BY RUN_DATE';
DBMS_OUTPUT.PUT_LINE(V_SQL);
--EXECUTE IMMEDIATE V_SQL;
END;
RUN_DATE 甲 乙 丙 丁 戊 己
--------------------------------------------------------------------------
1 2013/9/26 一 一 二
2 2013/9/27 四 二 三 一
3 2013/9/28 一 二 三 四
4 2013/9/29 一 二 四 三
5 2013/9/30 一 二 三 四