SQL语句编写 麻烦帮忙写一下一条SQL语句

SQL语句编写 麻烦帮忙写一下一条SQL语句

select d.name 申请部门,
a.hysmc 会议室名称,
f.count_wchys "5层会议室",
f.count_sjcdehys "19层第二会议室",
f.count_sjcdshys "19层第三会议室",
f.count_esecdyhys "22层第一会议室",
f.count_esecdehys "22层第二会议室",
(f.count_wchys+f.count_sjcdehys+f.count_sjcdshys+f.count_esecdyhys+f.count_esecdehys) 总计,
(to_char(b.meetingdate, 'yyyy')) 年度
from oa2_Reservemeeting b,
misbkjt1018.eg_hysinfo a,
misbkjt1018.KQD530130342 e,
oa2_user c,
oa2_group d,
(select s.name,
count(u.wchys) count_wchys,
count(u.sjcdehys) count_sjcdehys,
count(u.sjcdshys) count_sjcdshys,
count(u.esecdyhys) count_esecdyhys,
count(u.esecdehys) count_esecdehys
from oa2_Reservemeeting r,
oa2_group s,
oa2_user t,
misbkjt1018.KQD530130342 u
where r.slid = u.slid(+)
and r.reserveuserid = t.id(+)
and t.groupid = s.id(+)
group by s.name) f
where a.slid = b.slid(+)
and b.slid = e.slid(+)
and b.reserveuserid = c.id(+)
and c.groupid = d.id(+)
and d.name = f.name

这样写试试。追问字数多了，只能用截图了


追答你的意思是，前面不需要根据申请部门进行分组统计，最后所有的信息都统计为一条记录。是这样的吗？追问恩，是的，会议室就是不显示出来的
然后语句查询的时候，根据每个部门预定过的会议室，进行统计，统计出来的样式就是上面的图片那个追答select
d.name 申请部门,
a.hysmc 会议室名称,
f.count_wchys "5层会议室",
f.count_sjcdehys "19层第二会议室",
f.count_sjcdshys "19层第三会议室",
f.count_esecdyhys "22层第一会议室",
f.count_esecdehys "22层第二会议室",
(f.count_wchys+f.count_sjcdehys+f.count_sjcdshys+f.count_esecdyhys+f.count_esecdehys) 总计,
(to_char(b.meetingdate, 'yyyy')) 年度
from oa2_Reservemeeting b,
misbkjt1018.eg_hysinfo a,
misbkjt1018.KQD530130342 e,
oa2_user c,
oa2_group d,
(select s.name,
q.hysmc,
sum(u.wchys) count_wchys,
sum(u.sjcdehys) count_sjcdehys,
sum(u.sjcdshys) count_sjcdshys,
sum(u.esecdyhys) count_esecdyhys,
sum(u.esecdehys) count_esecdehys
from misbkjt1018.eg_hysinfo q,
oa2_Reservemeeting r,
oa2_group s,
oa2_user t,
misbkjt1018.KQD530130342 u
where q.slid = r.slid(+)
and r.slid = u.slid(+)
and r.reserveuserid = t.id(+)
and t.groupid = s.id(+)
group by s.name, q.hysmc) f
where a.slid = b.slid(+)
and b.slid = e.slid(+)
and b.reserveuserid = c.id(+)
and c.groupid = d.id(+)
and d.name = f.name
and a.hysmc = f.hysmc

把会议室的名也加到分组中。来自：求助得到的回答

精妙方案：使用PIVOT()语法，进行行转列！ 

-- 假如你会动态SQL构建，无论你有多少会议室均可自动出来；

普通方案：使用CASE语句

--公共部分-----------------

SELECt GroupID -- 部门
    ,RoomID -- 会议室
    ,Count(*) AS ApplyNum -- 申请次数
FROM oa2_Reservemeeting
WHERe to_char(meetingDate, 'yyyy') = 2016 -- 若想按年统计，则放到GROUP BY及SELECT中
GROUP BY GroupID, RoomID

对此结果集合，进行行列转换处理。