SELECT "ONE_DATE",
"BRG_ID",
"LAST_DT",
"NEW_OH",
"ZCXH",
"ZCXH_DESC",
"ZCXC",
"ZCBH",
"WQWJA_BEFORE",
"WQWJB_BEFORE",
"NQNJA_BEFORE",
"NQNJB_BEFORE",
"DYHNJ_BEFORE",
"HDQNJ_BEFORE",
"OIL_WEIGHT_NQA",
"OIL_WEIGHT_NQB",
"OIL_WEIGHT_WQ",
"DAY_ZCXH_COUNT",
"RN_DAYTIME_ZCXH",
"RN_DAYTOTAL_COUNT",
"RN_ALLTIME_ZCXH"
FROM (SELECT a.*,
ROW_NUMBER () OVER (PARTITION BY a.zcxh ORDER BY a.LAST_DT)
rn_alltime_zcxh
FROM ( SELECT TO_CHAR (
TO_DATE (LAST_DT, 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD')
one_date,
t.brg_id,
t.LAST_DT,
t.NEW_OH,
t.ZCXH,
t.ZCXH_DESC,
t.ZCXC,
t.ZCBH,
t.WQWJA_BEFORE,
t.WQWJB_BEFORE,
t.NQNJA_BEFORE,
t.NQNJB_BEFORE,
t.DYHNJ_BEFORE,
t.HDQNJ_BEFORE,
TO_CHAR (t.OIL_WEIGHT_NQA) OIL_WEIGHT_NQA,
TO_CHAR (t.OIL_WEIGHT_NQB) OIL_WEIGHT_NQB,
TO_CHAR (t.OIL_WEIGHT_WQ) OIL_WEIGHT_WQ,
--每天,每种轴承为一组的数量
COUNT (
*)
OVER (
PARTITION BY TO_CHAR (
TO_DATE (
LAST_DT,
'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD'),
zcxh)
AS day_zcxh_count,
--每天,每种轴承各自加序号
ROW_NUMBER ()
OVER (
PARTITION BY TO_CHAR (
TO_DATE (
LAST_DT,
'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD'),
zcxh
ORDER BY LAST_DT)
rn_daytime_zcxh,
--每天轴承总排序
(ROW_NUMBER ()
OVER (
PARTITION BY TO_CHAR (
TO_DATE (
LAST_DT,
'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD')
ORDER BY LAST_DT))
rn_dayTotal_count
FROM V_OH_JX_RECORD t
ORDER BY LAST_DT DESC) a
WHERE a.rn_daytime_zcxh <= TRUNC (a.day_zcxh_count / 4) * 4)
WHERE rn_alltime_zcxh <= 100
UNION ALL
SELECT "ONE_DATE",
"BRG_ID",
"LAST_DT",
"NEW_OH",
"ZCXH",
"ZCXH_DESC",
"ZCXC",
"ZCBH",
"WQWJA_BEFORE",
"WQWJB_BEFORE",
"NQNJA_BEFORE",
"NQNJB_BEFORE",
"DYHNJ_BEFORE",
"HDQNJ_BEFORE",
"OIL_WEIGHT_NQA",
"OIL_WEIGHT_NQB",
"OIL_WEIGHT_WQ",
"DAY_ZCXH_COUNT",
"RN_DAYTIME_ZCXH",
"RN_DAYTOTAL_COUNT",
"RN_ALLTIME_ZCXH"
FROM (SELECT a.*,
ROW_NUMBER () OVER (PARTITION BY a.zcxh ORDER BY a.LAST_DT)
rn_alltime_zcxh
FROM ( SELECT TO_CHAR (
TO_DATE (LAST_DT, 'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD')
one_date,
t.brg_id,
t.LAST_DT,
TO_CHAR ('NEW') AS NEW_OH,
t.ZCXH,
t.ZCXH_DESC,
t.ZCXC,
t.ZCBH,
t.WQWJ_SIZE1 WQWJA_BEFORE,
t.WQWJ_SIZE2 WQWJB_BEFORE,
t.NQNJ_SIZE1 NQNJA_BEFORE,
t.NQNJ_SIZE2 NQNJB_BEFORE,
t.DYHNJ_SIZE DYHNJ_BEFORE,
t.HDQNJ_SIZE HDQNJ_BEFORE,
TO_CHAR ('新品无注油量') OIL_WEIGHT_NQA,
TO_CHAR ('新品无注油量') OIL_WEIGHT_NQB,
TO_CHAR ('新品无注油量') OIL_WEIGHT_WQ,
--每天,每种轴承为一组的数量
COUNT (
*)
OVER (
PARTITION BY TO_CHAR (
TO_DATE (
LAST_DT,
'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD'),
zcxh)
AS day_zcxh_count,
--每天,每种轴承各自加序号
ROW_NUMBER ()
OVER (
PARTITION BY TO_CHAR (
TO_DATE (
LAST_DT,
'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD'),
zcxh
ORDER BY LAST_DT)
rn_daytime_zcxh,
--每天轴承总排序
(ROW_NUMBER ()
OVER (
PARTITION BY TO_CHAR (
TO_DATE (
LAST_DT,
'YYYY-MM-DD HH24:MI:SS'),
'YYYY-MM-DD')
ORDER BY LAST_DT))
rn_dayTotal_count
FROM V_R_TV_NEW_SIZE t
ORDER BY LAST_DT DESC) a
WHERE a.rn_daytime_zcxh <= TRUNC (a.day_zcxh_count / 4) * 4)
WHERE rn_alltime_zcxh <= 100;
oracle窗体函数
最新推荐文章于 2024-08-15 09:21:27 发布