select A.SORDER,MIN(KOTECED),MAX(KOTECED),MAX(A.JSTSTM)-MIN(A.JSTSTM) AS 时间 from factv2.SAGPLNFL A,FACTV2.SGPROGSS B Where B.WAREHUSFG=1(1为入库标志) AND A.SORDER=B.SORDER AND A.KOTECD>=2015 AND A.KOTECD<2027 GROUP BY A.SORDER UNION ALL --其它的类似,分别求出时间 select A.SORDER,MIN(KOTECED),MAX(KOTECED),MAX(A.JSTSTM)-MIN(A.JSTSTM) AS 时间 from factv2.SAGPLNFL A,FACTV2.SGPROGSS B Where B.WAREHUSFG=1(1为入库标志) AND A.SORDER=B.SORDER AND A.KOTECD>=2026 AND A.KOTECD<2037 GROUP BY A.SORDER UNION ALL .......
select A.SORDER,to_char(sum(A.JSTSTM-A.JSTSTM)*24,'9990.99') AS 时间 from factv2.SAGPLNFL A,FACTV2.SGPROGSS B Where B.WAREHUSFG=1(1为入库标志) AND A.SORDER=B.SORDER and a.KOTECD>=2015 and a.KOTECD<2026 group by sorder ORDER BY A.SORDERselect A.SORDER,to_char((max(A.JSTSTM)-min(A.JSTSTM))*24,'9990.99') AS 时间 from factv2.SAGPLNFL A,FACTV2.SGPROGSS B Where B.WAREHUSFG=1(1为入库标志) AND A.SORDER=B.SORDER and a.KOTECD>=2015 and a.KOTECD<2026 group by sorder ORDER BY A.SORDER
将每个工序 结束-开始 在 合计select t1.SORDER, 时间段1(2015~2026) , 时间段2(2026~2036) ,时间段3(2036~2075),时间段4 from (select A.SORDER,to_char(sum(A.JSTSTM-A.JSTSTM)*24,'9990.99')||'小时' AS 时间段1(2015~2026) from factv2.SAGPLNFL A,FACTV2.SGPROGSS B Where B.WAREHUSFG=1(1为入库标志) AND A.SORDER=B.SORDER and a.KOTECD>=2015 and a.KOTECD<2026 group by sorder ) t1 , (select A.SORDER,to_char(sum(A.JSTSTM-A.JSTSTM)*24,'9990.99')||'小时' AS 时间段2(2026~2036) from factv2.SAGPLNFL A,FACTV2.SGPROGSS B Where B.WAREHUSFG=1(1为入库标志) AND A.SORDER=B.SORDER and a.KOTECD>=2026 and a.KOTECD<2036 group by sorder ) t2 , (select A.SORDER,to_char(sum(A.JSTSTM-A.JSTSTM)*24,'9990.99')||'小时' AS 时间段3(2036~2075) from factv2.SAGPLNFL A,FACTV2.SGPROGSS B Where B.WAREHUSFG=1(1为入库标志) AND A.SORDER=B.SORDER and a.KOTECD>=2036 and a.KOTECD<2075 group by sorder ) t3 , (select A.SORDER,to_char(sum(A.JSTSTM-A.JSTSTM)*24,'9990.99')||'小时' AS 时间段4 from factv2.SAGPLNFL A,FACTV2.SGPROGSS B Where B.WAREHUSFG=1(1为入库标志) AND A.SORDER=B.SORDER and a.KOTECD>=2036 and a.KOTECD<2075 group by sorder ) t4 where t1.SORDER=t2.SORDER and t1.SORDER=t3.SORDER and t1.SORDER=t4.SORDER ORDER BY SORDER
SELECT A.SORDER, MIN(KOTECED), MAX(KOTECED), trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS 时间 FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B WHERE B.WAREHUSFG = 1 AND A.SORDER = B.SORDER AND A.KOTECD >= 2015 AND A.KOTECD <= 2026 GROUP BY A.SORDER UNION ALL SELECT A.SORDER, MIN(KOTECED), MAX(KOTECED), trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS 时间 FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B WHERE B.WAREHUSFG = 1 AND A.SORDER = B.SORDER AND A.KOTECD > 2026 AND A.KOTECD <= 2037 GROUP BY A.SORDER UNION ALL SELECT A.SORDER, MIN(KOTECED), MAX(KOTECED), trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS 时间 FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B WHERE B.WAREHUSFG = 1 AND A.SORDER = B.SORDER AND A.KOTECD > 2037 AND A.KOTECD < 2075 GROUP BY A.SORDER UNION ALL SELECT A.SORDER, MIN(KOTECED), MAX(KOTECED), trunc(A.WAREETM - A.JSTETM, 2) AS 时间 FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B WHERE B.WAREHUSFG = 1 AND A.SORDER = B.SORDER AND A.KOTECD = 2075 GROUP BY A.SORDER
--行列转换 SELECT SORDER, SUM(decode(sjd, '2015-2026', sj, 0)) '2015-2026', SUM(decode(sjd, '2026-2037', sj, 0)) '2026-2037', SUM(decode(sjd, '2037-2075', sj, 0)) '2037-2075', SUM(decode(sjd, '2075-', sj, 0)) '2075-', FROM (SELECT A.SORDER, '2015-2026' AS sjd, trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS sj FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B WHERE B.WAREHUSFG = 1 AND A.SORDER = B.SORDER AND A.KOTECD >= 2015 AND A.KOTECD <= 2026 GROUP BY A.SORDER UNION ALL SELECT A.SORDER, '2026-2037', trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS sj FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B WHERE B.WAREHUSFG = 1 AND A.SORDER = B.SORDER AND A.KOTECD > 2026 AND A.KOTECD <= 2037 GROUP BY A.SORDER UNION ALL SELECT A.SORDER, '2037-2075' sjd, trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS sj FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B WHERE B.WAREHUSFG = 1 AND A.SORDER = B.SORDER AND A.KOTECD > 2037 AND A.KOTECD < 2075 GROUP BY A.SORDER UNION ALL SELECT A.SORDER, '2075-', trunc(A.WAREETM - A.JSTETM, 2) AS sj FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B WHERE B.WAREHUSFG = 1 AND A.SORDER = B.SORDER AND A.KOTECD = 2075 GROUP BY A.SORDER)
楼上的语句在ORAClE中编译不能通过。
目前只有xys_777兄的 测试能通过,但是效率好像比较低,有没有更好的,优化的语句? 谢谢。
目前只有xys_777兄的 测试能通过,但是效率好像比较低,有没有更好的,优化的语句? 谢谢。
xys_777 ,高手到ORALCE版块了,以后多多指教楼主,你的数据比较多吧,索引有么?
这个表中有上百万条记录。你觉得针对这个语句,需要建立什么索引?(已经有主键SORDER)。
是这个意思吧? SELECT a.sorder, ROUND((MAX(DECODE(a.koteced,2026,a.jstetm))-MAX(DECODE(a.koteced,2015,a.jststm)))*24,2) "2015-2026", ROUND((MAX(DECODE(a.koteced,2036,a.jstetm))-MAX(DECODE(a.koteced,2026,a.jstetm)))*24,2) "2026-2036", ROUND((MAX(DECODE(a.koteced,2075,a.jstetm))-MAX(DECODE(a.koteced,2036,a.jstetm)))*24,2) "2036-2075", ROUND((MAX(b.WAREETM) -MAX(DECODE(a.koteced,2075,a.jstetm)))*24) "2075-入库" FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B WHERE b.warehusfg=1 AND a.sorder =b.sorder GROUP BY a.sorder ORDER BY sorder
select A.SORDER,MIN(KOTECED),MAX(KOTECED),MAX(A.JSTSTM)-MIN(A.JSTSTM) AS 时间
from factv2.SAGPLNFL A,FACTV2.SGPROGSS B
Where B.WAREHUSFG=1(1为入库标志)
AND A.SORDER=B.SORDER
AND A.KOTECD>=2015
AND A.KOTECD<2027
GROUP BY A.SORDER
UNION ALL --其它的类似,分别求出时间
select A.SORDER,MIN(KOTECED),MAX(KOTECED),MAX(A.JSTSTM)-MIN(A.JSTSTM) AS 时间
from factv2.SAGPLNFL A,FACTV2.SGPROGSS B
Where B.WAREHUSFG=1(1为入库标志)
AND A.SORDER=B.SORDER
AND A.KOTECD>=2026
AND A.KOTECD<2037
GROUP BY A.SORDER
UNION ALL .......
SORDER 时间段1(2015~2026) 时间段2(2026~2036)时间段3(2036~2075)时间段4
03103410 A小时 B小时 c小时 D小时
03103420 E小时 F小时 g小时 H小时
03103430 I小时 J小时 K小时 M小时
.......
请各位高手给予帮忙,先谢谢了。
select A.SORDER,to_char(sum(A.JSTSTM-A.JSTSTM)*24,'9990.99') AS 时间
from factv2.SAGPLNFL A,FACTV2.SGPROGSS B
Where B.WAREHUSFG=1(1为入库标志)
AND A.SORDER=B.SORDER
and a.KOTECD>=2015 and a.KOTECD<2026
group by sorder
ORDER BY A.SORDERselect A.SORDER,to_char((max(A.JSTSTM)-min(A.JSTSTM))*24,'9990.99') AS 时间
from factv2.SAGPLNFL A,FACTV2.SGPROGSS B
Where B.WAREHUSFG=1(1为入库标志)
AND A.SORDER=B.SORDER
and a.KOTECD>=2015 and a.KOTECD<2026
group by sorder
ORDER BY A.SORDER
from
(select A.SORDER,to_char(sum(A.JSTSTM-A.JSTSTM)*24,'9990.99')||'小时' AS 时间段1(2015~2026)
from factv2.SAGPLNFL A,FACTV2.SGPROGSS B
Where B.WAREHUSFG=1(1为入库标志)
AND A.SORDER=B.SORDER
and a.KOTECD>=2015 and a.KOTECD<2026
group by sorder
) t1 ,
(select A.SORDER,to_char(sum(A.JSTSTM-A.JSTSTM)*24,'9990.99')||'小时' AS 时间段2(2026~2036)
from factv2.SAGPLNFL A,FACTV2.SGPROGSS B
Where B.WAREHUSFG=1(1为入库标志)
AND A.SORDER=B.SORDER
and a.KOTECD>=2026 and a.KOTECD<2036
group by sorder
) t2 ,
(select A.SORDER,to_char(sum(A.JSTSTM-A.JSTSTM)*24,'9990.99')||'小时' AS 时间段3(2036~2075)
from factv2.SAGPLNFL A,FACTV2.SGPROGSS B
Where B.WAREHUSFG=1(1为入库标志)
AND A.SORDER=B.SORDER
and a.KOTECD>=2036 and a.KOTECD<2075
group by sorder
) t3 ,
(select A.SORDER,to_char(sum(A.JSTSTM-A.JSTSTM)*24,'9990.99')||'小时' AS 时间段4
from factv2.SAGPLNFL A,FACTV2.SGPROGSS B
Where B.WAREHUSFG=1(1为入库标志)
AND A.SORDER=B.SORDER
and a.KOTECD>=2036 and a.KOTECD<2075
group by sorder
) t4
where t1.SORDER=t2.SORDER
and t1.SORDER=t3.SORDER
and t1.SORDER=t4.SORDER
ORDER BY SORDER
类似:
统计结果按照以下格式显示:
SORDER 时间段1(2015~2026) 时间段2(2026~2036)时间段3(2036~2075)时间段4
03103410 A小时 B小时 c小时 D小时
03103420 E小时 F小时 g小时 H小时
03103430 I小时 J小时 K小时 M小时
.......
FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B
WHERE B.WAREHUSFG = 1 AND
A.SORDER = B.SORDER AND
A.KOTECD >= 2015 AND
A.KOTECD <= 2026
GROUP BY A.SORDER
UNION ALL
SELECT A.SORDER, MIN(KOTECED), MAX(KOTECED), trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS 时间
FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B
WHERE B.WAREHUSFG = 1 AND
A.SORDER = B.SORDER AND
A.KOTECD > 2026 AND
A.KOTECD <= 2037
GROUP BY A.SORDER
UNION ALL
SELECT A.SORDER, MIN(KOTECED), MAX(KOTECED), trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS 时间
FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B
WHERE B.WAREHUSFG = 1 AND
A.SORDER = B.SORDER AND
A.KOTECD > 2037 AND
A.KOTECD < 2075
GROUP BY A.SORDER
UNION ALL
SELECT A.SORDER, MIN(KOTECED), MAX(KOTECED), trunc(A.WAREETM - A.JSTETM, 2) AS 时间
FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B
WHERE B.WAREHUSFG = 1 AND
A.SORDER = B.SORDER AND
A.KOTECD = 2075
GROUP BY A.SORDER
SELECT SORDER,
SUM(decode(sjd, '2015-2026', sj, 0)) '2015-2026',
SUM(decode(sjd, '2026-2037', sj, 0)) '2026-2037',
SUM(decode(sjd, '2037-2075', sj, 0)) '2037-2075',
SUM(decode(sjd, '2075-', sj, 0)) '2075-',
FROM (SELECT A.SORDER, '2015-2026' AS sjd, trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS sj
FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B
WHERE B.WAREHUSFG = 1 AND
A.SORDER = B.SORDER AND
A.KOTECD >= 2015 AND
A.KOTECD <= 2026
GROUP BY A.SORDER
UNION ALL
SELECT A.SORDER, '2026-2037', trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS sj
FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B
WHERE B.WAREHUSFG = 1 AND
A.SORDER = B.SORDER AND
A.KOTECD > 2026 AND
A.KOTECD <= 2037
GROUP BY A.SORDER
UNION ALL
SELECT A.SORDER, '2037-2075' sjd, trunc(SUM(A.JSTETM - A.JSTSTM) * 24, 2) AS sj
FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B
WHERE B.WAREHUSFG = 1 AND
A.SORDER = B.SORDER AND
A.KOTECD > 2037 AND
A.KOTECD < 2075
GROUP BY A.SORDER
UNION ALL
SELECT A.SORDER, '2075-', trunc(A.WAREETM - A.JSTETM, 2) AS sj
FROM factv2.SAGPLNFL A, FACTV2.SGPROGSS B
WHERE B.WAREHUSFG = 1 AND
A.SORDER = B.SORDER AND
A.KOTECD = 2075
GROUP BY A.SORDER)
谢谢。
谢谢。
SELECT a.sorder,
ROUND((MAX(DECODE(a.koteced,2026,a.jstetm))-MAX(DECODE(a.koteced,2015,a.jststm)))*24,2) "2015-2026",
ROUND((MAX(DECODE(a.koteced,2036,a.jstetm))-MAX(DECODE(a.koteced,2026,a.jstetm)))*24,2) "2026-2036",
ROUND((MAX(DECODE(a.koteced,2075,a.jstetm))-MAX(DECODE(a.koteced,2036,a.jstetm)))*24,2) "2036-2075",
ROUND((MAX(b.WAREETM) -MAX(DECODE(a.koteced,2075,a.jstetm)))*24) "2075-入库"
FROM factv2.SAGPLNFL A,
FACTV2.SGPROGSS B
WHERE b.warehusfg=1
AND a.sorder =b.sorder
GROUP BY a.sorder
ORDER BY sorder