天啊,这么多join,眼都看花了
解决方案 »
- SQL问题
- 怎样后台删除SAP中的一张生产订单?
- postgreql下为什么不能启动数据库LOG: database system is ready to accept connections
- 高手们,邹老大,帮忙啊!!!救命啊!!!
- 关于死锁后如何知道是那个表造成的
- 请教:foxpro2.5的数据库是否可以导入到sql2000中来?
- 不通过试探连接数据库的方法如何判断本地计算机已经安装SQL Server?
- 急求解决删除数据库的日志的问题
- 问一个SQL执行语句
- 关于时间格式的转换?
- 如何获得主键的名字
- select distinct top 10 percent qy_name,qy_add from biao order by newid()随机取记录,distinct无效,怎么解决?顶者有分!
sum(case when c.hid=21 then c.average else 0 end) as h01
sum(case when c.hid=22 then c.average else 0 end) as h02,
sum(case when c.hid=23 then c.average else 0 end) as h03,
sum(case when c.hid=24 then c.average else 0 end) as h04,
sum(case when c.hid=1 then c.average else 0 end) as h05,
sum(case when c.hid=2 then c.average else 0 end) as h06,
sum(case when c.hid=3 then c.average else 0 end) as h07,
sum(case when c.hid=4 then c.average else 0 end) as h08,
sum(case when c.hid=5 then c.average else 0 end) as h09,
sum(case when c.hid=6 then c.average else 0 end) as h10,
sum(case when c.hid=7 then c.average else 0 end) as h11,
sum(case when c.hid=8 then c.average else 0 end) as h12,
sum(case when c.hid=9 then c.average else 0 end) as h13,
sum(case when c.hid=10 then c.average else 0 end) as h14,
sum(case when c.hid=11 then c.average else 0 end) as h15,
sum(case when c.hid=12 then c.average else 0 end) as h16,
sum(case when c.hid=13 then c.average else 0 end) as h17,
sum(case when c.hid=14 then c.average else 0 end) as h18,
sum(case when c.hid=15 then c.average else 0 end) as h19,
sum(case when c.hid=16 then c.average else 0 end) as h20,
sum(case when c.hid=17 then c.average else 0 end) as h21,
sum(case when c.hid=18 then c.average else 0 end) as h22,
sum(case when c.hid=19 then c.average else 0 end) as h23,
sum(case when c.hid=20 then c.average else 0 end) as h24,
c.scrq
from
(select shb_id from shb_table) a
left join
(select shb_id ,scrq ,average ,hid from v_hour) c
on
a.shb_id=c.shb_id and a.scrq=c.scrq
group by
a.shb_id,c.scrq
z.shb_id,
y.scrq
CASE WHEN hid=21 THEN average END AS h01
CASE WHEN hid=22 THEN average END AS h02from shb_table AS z
LEFT JOIN v_hour AS y ON z.shb_id=y.shb_id
sum(case when c.hid=21 then c.average else 0 end) as h01
sum(case when c.hid=22 then c.average else 0 end) as h02,
sum(case when c.hid=23 then c.average else 0 end) as h03,
sum(case when c.hid=24 then c.average else 0 end) as h04,
sum(case when c.hid=1 then c.average else 0 end) as h05,
sum(case when c.hid=2 then c.average else 0 end) as h06,
sum(case when c.hid=3 then c.average else 0 end) as h07,
sum(case when c.hid=4 then c.average else 0 end) as h08,
sum(case when c.hid=5 then c.average else 0 end) as h09,
sum(case when c.hid=6 then c.average else 0 end) as h10,
sum(case when c.hid=7 then c.average else 0 end) as h11,
sum(case when c.hid=8 then c.average else 0 end) as h12,
sum(case when c.hid=9 then c.average else 0 end) as h13,
sum(case when c.hid=10 then c.average else 0 end) as h14,
sum(case when c.hid=11 then c.average else 0 end) as h15,
sum(case when c.hid=12 then c.average else 0 end) as h16,
sum(case when c.hid=13 then c.average else 0 end) as h17,
sum(case when c.hid=14 then c.average else 0 end) as h18,
sum(case when c.hid=15 then c.average else 0 end) as h19,
sum(case when c.hid=16 then c.average else 0 end) as h20,
sum(case when c.hid=17 then c.average else 0 end) as h21,
sum(case when c.hid=18 then c.average else 0 end) as h22,
sum(case when c.hid=19 then c.average else 0 end) as h23,
sum(case when c.hid=20 then c.average else 0 end) as h24,
c.scrq
from
(select shb_id from shb_table) a
left join
v_hour c
on
a.shb_id = c.shb_id and a.scrq = c.scrq
group by
a.shb_id,c.scrq
sum(case when c.hid=21 then c.average else 0 end) as h01
sum(case when c.hid=22 then c.average else 0 end) as h02,
sum(case when c.hid=23 then c.average else 0 end) as h03,
sum(case when c.hid=24 then c.average else 0 end) as h04,
sum(case when c.hid=1 then c.average else 0 end) as h05,
sum(case when c.hid=2 then c.average else 0 end) as h06,
sum(case when c.hid=3 then c.average else 0 end) as h07,
sum(case when c.hid=4 then c.average else 0 end) as h08,
sum(case when c.hid=5 then c.average else 0 end) as h09,
sum(case when c.hid=6 then c.average else 0 end) as h10,
sum(case when c.hid=7 then c.average else 0 end) as h11,
sum(case when c.hid=8 then c.average else 0 end) as h12,
sum(case when c.hid=9 then c.average else 0 end) as h13,
sum(case when c.hid=10 then c.average else 0 end) as h14,
sum(case when c.hid=11 then c.average else 0 end) as h15,
sum(case when c.hid=12 then c.average else 0 end) as h16,
sum(case when c.hid=13 then c.average else 0 end) as h17,
sum(case when c.hid=14 then c.average else 0 end) as h18,
sum(case when c.hid=15 then c.average else 0 end) as h19,
sum(case when c.hid=16 then c.average else 0 end) as h20,
sum(case when c.hid=17 then c.average else 0 end) as h21,
sum(case when c.hid=18 then c.average else 0 end) as h22,
sum(case when c.hid=19 then c.average else 0 end) as h23,
sum(case when c.hid=20 then c.average else 0 end) as h24,
c.scrq
from
(select shb_id from shb_table) a
left join
(select shb_id ,scrq ,average ,hid from v_hour) c
on
a.shb_id=c.shb_id
group by
a.shb_id,c.scrq
sum(case when c.hid=21 then c.average else 0 end) as h01
sum(case when c.hid=22 then c.average else 0 end) as h02,
sum(case when c.hid=23 then c.average else 0 end) as h03,
sum(case when c.hid=24 then c.average else 0 end) as h04,
sum(case when c.hid=1 then c.average else 0 end) as h05,
sum(case when c.hid=2 then c.average else 0 end) as h06,
sum(case when c.hid=3 then c.average else 0 end) as h07,
sum(case when c.hid=4 then c.average else 0 end) as h08,
sum(case when c.hid=5 then c.average else 0 end) as h09,
sum(case when c.hid=6 then c.average else 0 end) as h10,
sum(case when c.hid=7 then c.average else 0 end) as h11,
sum(case when c.hid=8 then c.average else 0 end) as h12,
sum(case when c.hid=9 then c.average else 0 end) as h13,
sum(case when c.hid=10 then c.average else 0 end) as h14,
sum(case when c.hid=11 then c.average else 0 end) as h15,
sum(case when c.hid=12 then c.average else 0 end) as h16,
sum(case when c.hid=13 then c.average else 0 end) as h17,
sum(case when c.hid=14 then c.average else 0 end) as h18,
sum(case when c.hid=15 then c.average else 0 end) as h19,
sum(case when c.hid=16 then c.average else 0 end) as h20,
sum(case when c.hid=17 then c.average else 0 end) as h21,
sum(case when c.hid=18 then c.average else 0 end) as h22,
sum(case when c.hid=19 then c.average else 0 end) as h23,
sum(case when c.hid=20 then c.average else 0 end) as h24,
c.scrq
from
(select shb_id from shb_table) a
left join
v_hour c
on
a.shb_id=c.shb_id
group by
a.shb_id,c.scrq