我还是不明白怎么写· 你看下我写的对吗?select roomid,roomname case when To_char(starttime,'YYYY/MON/DD')=To_char(starttime,'YYYY/MON/DD') then count(b.RoomID) when substr(to_char(startname,'YYYY/MM/DD'),6,2)=substr(to_char(sysdate,'YYYY/MM/DD'),6,2) then count(b.Roomid) when substr(to_char(startname,'YYYY/mm/DD'),9,2)=substr(to_char(sysdate,'YYYY/MM/DD'),9,2) then count(b.Roomid) when To_char(sysdate,'yy')=To_char(startime,'YY') then count(b.Roomid) end from TCourtRoom a,TTRail b where a.Roomid=b.Roomid group by b.Roomid;
select roomid,roomname, sum(case when to_char(startname,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') then 1 else 0 end) 当天开会数量 , sum(case when to_char(startname,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1 else 0 end) 本月开会数量 , sum(case when to_char(startname,'YYYY')=to_char(sysdate,'YYYY') then 1 else 0 end) 本年开会数量 , sum(case when to_char(startname,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1 else 0 end) 本月开会数量 , sum(case when to_char(startname,'YYYYWW')=to_char(sysdate,'YYYYWW') then 1 else 0 end) 本月开会数量 from TCourtRoom a,TTRail b where a.Roomid=b.Roomid group by b.Roomid;
select roomid,roomname, sum(case when to_char(startname,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') then 1 else 0 end) 当天开会数量 , sum(case when to_char(startname,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1 else 0 end) 本月开会数量 , sum(case when to_char(startname,'YYYY')=to_char(sysdate,'YYYY') then 1 else 0 end) 本年开会数量 , sum(case when to_char(startname,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1 else 0 end) 本月开会数量 , sum(case when to_char(startname,'YYYYWW')=to_char(sysdate,'YYYYWW') then 1 else 0 end) 本月开会数量 from TCourtRoom a,TTRail b where a.Roomid=b.Roomid group by b.Roomid,,roomname;
startname应该换成starttime,被搂住误导了
starttime没标识符。通过b.starttime,标识符又无效!!!
b表不是有starttime这个字段吗?
select a.roomid,a.roomname, sum(case when to_char(b.starttime,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') then 1 else 0 end) 当天开会数量 , sum(case when to_char(b.starttime,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1 else 0 end) 本月开会数量 , sum(case when to_char(b.starttime,'YYYY')=to_char(sysdate,'YYYY') then 1 else 0 end) 本年开会数量 , sum(case when to_char(b.starttime,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1 else 0 end) 本月开会数量 , sum(case when to_char(b.starttime,'YYYYWW')=to_char(sysdate,'YYYYWW') then 1 else 0 end) 本月开会数量 from TCourtRoom a,TTRail b where a.Roomid=b.Roomid group by a.Roomid,a.roomname;
select a.roomid,a.roomname, sum(case when to_char(b.starttime,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') then 1 else 0 end) 当天开会数量 , sum(case when to_char(b.starttime,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1 else 0 end) 本月开会数量 , sum(case when to_char(b.starttime,'YYYY')=to_char(sysdate,'YYYY') then 1 else 0 end) 本年开会数量 , sum(case when to_char(b.starttime,'YYYYWW')=to_char(sysdate,'YYYYWW') then 1 else 0 end) 本周开会数量 from tcourtroom a,ttrial b where a.roomid=b.roomid group by b.roomid,a.roomname; 不是group by表达式·为什么》
换成group by a.Roomid,a.roomname
select aa.roomid, aa.roomname, count(case when sysdate >= bb.starttime and sysdate <= bb.endtime then 1 else null end) "当天开会数量", count(case when next_day(sysdate, 'mon') -7 <= bb.starttime and bb.starttime < next_day(sysdate, 'mon') -1 then 1 when tnext_day(sysdate, 'mon') -7 <= bb.endtime and bb.endtime < next_day(sysdate, 'mon') -1 then 1 else null end) "本周开会数量", count(case when trunc(sysdate, 'mm') <= bb.starttime and bb.starttime < add_months(trunc(sysdate, 'mm'), 1) then 1 when trunc(sysdate, 'mm') <= bb.endtime and bb.endtime < add_months(trunc(sysdate, 'mm'), 1) then 1 else null end) "当月开会数量", count(case trunc(sysdate, 'yyyy') <= bb.starttime and bb.starttime < add_months(trunc(sysdate, 'yyyy'), 12) then 1 when trunc(sysdate, 'yyyy') <= bb.endtime and bb.endtime < add_months(trunc(sysdate, 'yyyy'), 12) then 1 ) "当年开会数量" from aa left join bb on aa.roomid = bb.roomid group by aa.roomid, aa.roomname
你看下我写的对吗?select roomid,roomname
case when To_char(starttime,'YYYY/MON/DD')=To_char(starttime,'YYYY/MON/DD')
then count(b.RoomID) when substr(to_char(startname,'YYYY/MM/DD'),6,2)=substr(to_char(sysdate,'YYYY/MM/DD'),6,2)
then count(b.Roomid) when substr(to_char(startname,'YYYY/mm/DD'),9,2)=substr(to_char(sysdate,'YYYY/MM/DD'),9,2)
then count(b.Roomid) when To_char(sysdate,'yy')=To_char(startime,'YY')
then count(b.Roomid) end
from TCourtRoom a,TTRail b where a.Roomid=b.Roomid group by b.Roomid;
sum(case when to_char(startname,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') then 1
else 0
end) 当天开会数量 ,
sum(case when to_char(startname,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1
else 0
end) 本月开会数量 ,
sum(case when to_char(startname,'YYYY')=to_char(sysdate,'YYYY') then 1
else 0
end) 本年开会数量 ,
sum(case when to_char(startname,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1
else 0
end) 本月开会数量 ,
sum(case when to_char(startname,'YYYYWW')=to_char(sysdate,'YYYYWW') then 1
else 0
end) 本月开会数量
from TCourtRoom a,TTRail b where a.Roomid=b.Roomid group by b.Roomid;
sum(case when to_char(startname,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') then 1
else 0
end) 当天开会数量 ,
sum(case when to_char(startname,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1
else 0
end) 本月开会数量 ,
sum(case when to_char(startname,'YYYY')=to_char(sysdate,'YYYY') then 1
else 0
end) 本年开会数量 ,
sum(case when to_char(startname,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1
else 0
end) 本月开会数量 ,
sum(case when to_char(startname,'YYYYWW')=to_char(sysdate,'YYYYWW') then 1
else 0
end) 本月开会数量
from TCourtRoom a,TTRail b where a.Roomid=b.Roomid group by b.Roomid,,roomname;
sum(case when to_char(b.starttime,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') then 1
else 0
end) 当天开会数量 ,
sum(case when to_char(b.starttime,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1
else 0
end) 本月开会数量 ,
sum(case when to_char(b.starttime,'YYYY')=to_char(sysdate,'YYYY') then 1
else 0
end) 本年开会数量 ,
sum(case when to_char(b.starttime,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1
else 0
end) 本月开会数量 ,
sum(case when to_char(b.starttime,'YYYYWW')=to_char(sysdate,'YYYYWW') then 1
else 0
end) 本月开会数量
from TCourtRoom a,TTRail b where a.Roomid=b.Roomid group by a.Roomid,a.roomname;
sum(case when to_char(b.starttime,'YYYYMMDD')=to_char(sysdate,'YYYYMMDD') then 1
else 0
end) 当天开会数量 ,
sum(case when to_char(b.starttime,'YYYYMM')=to_char(sysdate,'YYYYMM') then 1
else 0
end) 本月开会数量 ,
sum(case when to_char(b.starttime,'YYYY')=to_char(sysdate,'YYYY') then 1
else 0
end) 本年开会数量 ,
sum(case when to_char(b.starttime,'YYYYWW')=to_char(sysdate,'YYYYWW') then 1
else 0
end) 本周开会数量
from tcourtroom a,ttrial b where a.roomid=b.roomid group by b.roomid,a.roomname;
不是group by表达式·为什么》
select aa.roomid, aa.roomname,
count(case when sysdate >= bb.starttime and sysdate <= bb.endtime
then 1
else null end) "当天开会数量",
count(case when next_day(sysdate, 'mon') -7 <= bb.starttime
and bb.starttime < next_day(sysdate, 'mon') -1
then 1
when tnext_day(sysdate, 'mon') -7 <= bb.endtime
and bb.endtime < next_day(sysdate, 'mon') -1
then 1
else null end) "本周开会数量",
count(case when trunc(sysdate, 'mm') <= bb.starttime
and bb.starttime < add_months(trunc(sysdate, 'mm'), 1)
then 1
when trunc(sysdate, 'mm') <= bb.endtime
and bb.endtime < add_months(trunc(sysdate, 'mm'), 1)
then 1
else null end) "当月开会数量",
count(case trunc(sysdate, 'yyyy') <= bb.starttime
and bb.starttime < add_months(trunc(sysdate, 'yyyy'), 12)
then 1
when trunc(sysdate, 'yyyy') <= bb.endtime
and bb.endtime < add_months(trunc(sysdate, 'yyyy'), 12)
then 1 ) "当年开会数量"
from aa left join bb on aa.roomid = bb.roomid
group by aa.roomid, aa.roomname
-至少2年的Oracle数据库管理的相关经验
-熟悉Oracle的备份恢复,数据迁移,RAC,数据库调优和PL/SQL编写
-熟悉Red Hat和Solaris操作系统及Shell脚本编写
-熟练的英语听说读写能力
-良好的沟通和表达能力
-在高强度和富有挑战的工作环境下,进行有效工作的能力
-富有创造力,工作积极主动,能主动地与项目组其他成员进行合作
-高度认同公司的企业文化:尊重员工,诚实和正直,追求品质和卓越,以客户为中心,恪守个人职责和倡导团队精神,求变创新,专注于技术,社会责任。 有意者请联系[email protected]