17,086
社区成员
发帖
与我相关
我的任务
分享
select b.sdate ,a.* from
(select to_char(to_date('2011-12', 'YYYY-MM') + rownum - 1, 'YYYY-MM-DD') as sdate from dual
connect by rownum <= to_number(to_char(last_day(to_date('2011-12', 'YYYY-MM')), 'dd'))) b
left jion b.sdate=a.日期
create table T12 as
select '安全环境监督部' d , '574' n, '杨业' m , '2011/12/01' r, '07:58' q from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/01', '17:31' from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/02', '07:53' from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/02', '07:55' from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/02', '19:47' from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/04', '08:07' from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/04', '17:22' from dual;
select d ,n ,m ,r ,
replace(wm_concat(q),',',' '),
count(q)
from t12 group by d ,n ,m ,r
with tb as
(select '安全环境监督部' d , '574' n, '杨业' m , '2011/12/01' r, '07:58' q from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/01', '17:31' from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/02', '07:53' from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/02', '07:55' from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/02', '19:47' from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/04', '08:07' from dual union all
select '安全环境监督部', '574', '杨业', '2011/12/04', '17:22' from dual )
select '安全环境监督部', '574', '杨业' ,t1.r,t2.jl,nvl(t2.rn,0)from
(select (to_date((select min(r)from tb),'YYYY/MM/DD')+rownum-1) r
from dual connect by rownum<=(select max(to_date(r,'YYYY/MM/DD'))-min(to_date(r,'YYYY/MM/DD'))from tb)+1) t1
,(select '安全环境监督部', '574', '杨业' ,r ,replace(wm_concat(q),',',' ') jl,count(q) rn
from tb group by r )t2
where t1.r=t2.r(+)
order by t1.r
select * from
(select 部门 ,工号 ,姓名 ,日期 ,
replace(wm_concat(时间),',',' '),
count(时间) rn
from tb group by 部门 ,工号 ,姓名 ,日期 )
where rn>=2
SELECT 部门, 工号, 姓名, 日期, MIN(时间), COUNT(1)
FROM TAB
GROUP BY 部门, 工号, 姓名, 日期
select 部门 ,工号 ,姓名 ,日期 ,
replace(wm_concat(时间),',',' '),
count(时间)
from tb group by 部门 ,工号 ,姓名 ,日期