我的表如下:table1begindate enddate mydo
2008-02-022 2008-02-029 其实就是一周的内容
2008-02-15 2008-02-21
tabl2 date mydo
2008-02-23 打篮球
2008-02-24 踢足球
2008-02-25 睡懒觉
..............................现在要用一条sql直接在Oracle数据库里 查询出我一周所做的事情:begindate enddate other
2008-02-022 2008-02-029 打篮球 踢足球 睡懒觉 其实就是一周的内容求教高手:如何才能实现啊!
2008-02-022 2008-02-029 其实就是一周的内容
2008-02-15 2008-02-21
tabl2 date mydo
2008-02-23 打篮球
2008-02-24 踢足球
2008-02-25 睡懒觉
..............................现在要用一条sql直接在Oracle数据库里 查询出我一周所做的事情:begindate enddate other
2008-02-022 2008-02-029 打篮球 踢足球 睡懒觉 其实就是一周的内容求教高手:如何才能实现啊!
declare
c_temp1 varchar2(350);
c_temp2 varchar2(50);
begin
for c_loop1 in (select begindate,enddate from table1) loop
c_temp1=null;
for c_loop2 in (select date,mydo from table2) loop
c_temp2=null;
select mydo into c_temp2 from table2 where c_loop2.date between c_loop1.begindate and
c_loop2.enddate;
c_temp1:=c_temp1||' '||c_temp2;
end loop;
update table1 set mydo=c_temp1 where begindate=c_loop1.begindate and
enddate=c_loop1.enddate;
end loop;
end;
执行存储过程后,再查询table2即可.请验证!
1 '2007-01-02' '2007-01-03'
1 '2007-01-05' '2007-01-06'
1 '2007-01-09' '2007-01-13'
2 '2007-02-02' '2007-02-03'
3 '2007-04-02' '2007-04-03' 表B: Employee_sn CreateDate
1 '2007-01-02'
1 '2007-01-05'
1 '2007-01-03'
3 '2007-04-02'
1 '2007-01-04'
1 '2007-01-02'
2 '2007-02-02' 要得到的结果:
Employee_sn StartDate EndDate Count
1 '2007-01-02' '2007-01-03' 3
1 '2007-01-05' '2007-01-06' 1
1 '2007-01-09' '2007-01-13' 0
2 '2007-02-02' '2007-02-03' 1
3 '2007-04-02' '2007-04-03' 1 注:根据表A中的时间段和员工编号到表B中去统计出对应的员工在该时间段的记录总数.
表A中的员工可能会有多个时间段,但时间段彼此不会重复.
这个差不多的 SQL 怎么写啊
ls这个简单多了,lz的要定义字符串连接的聚合函数,才能用sql实现select t1.a,t1.b,t1.c,count(t2.a) from t1
left outer join t2 on t1.b>=t2.d and t1.c<=t2.d and t1.a=t2.a
group by t1.a,t1.b,t1.c
select t.a1,
t.a2,
t.a3,
(select count(*)
from table2 b
where b.b1 = t.a1
and b.b2 between t.a2 and t.a3) a4
from table1 t
order by a1;
from emp_a a ,emp_b b
where b.createdate between a.startdate and a.enddate and a.employee_sn=b.employee_sn
group by a.employee_sn,a.startdate,a.enddate
这样写为什么0行不显示呢?
select a.startdate,a.enddaate,count(a.employee_sn) as sl
from d_1 a ,d_2 b
where a.employee_sn = b.employee_sn and b.createdate between a.startdate and a.enddaate
group by a.startdate,a.enddaate