Using a temporary table, how to connect, such as indexing ah, true of the new, I don't understand, o great god taught,
CodePudding user response:
Posted statements, implementation plan, and the amount of data for each table, so that the great god to analysis, we can also follow to learn togetherCodePudding user response:
Thank the bosses, I will correct!CodePudding user response:
The code is as follows:Select aa. * tt. Result1 gp_result, tt, create_time gp_sj, tt. The create_user gp_r, tt. Xb_user, DECODE (tt) reserved7, 'A', 'morning', 'B', 'student', 'C', 'night') gp_bz, asr. * from
(select * from
(select * from
(select a1. *, TO_CHAR (a1) trans_time, 'mm)
| | 'month' mon,
TO_CHAR (a1) trans_time, 'yyyy/mm/dd) days,
TO_CHAR (a1) trans_time, 'hh24: mi: ss') times, b1. * from
(select a. arcode, a.c reate_time, a.c reate_user, a.r eserved5, (case when a.r eserved7='A' THEN 'morning' s a.r eserved7='B' THEN 'GSM' else 'night' end) as zubie,
A. d. escription_s, a.r esult1, ap. The description as description1 from
(select * from
(select * from
(select t1. *, code. Description_s from
(select t.b arcode, tc reate_time, tc reate_user, t.r eserved5, t.r eserved7, t.r esult,
(case when t.r esult=20 THEN 'unqualified'
The when t.r esult=30 THEN 'commuted qualified'
The when t.r esult=10 THEN 'entry qualified'
End) as result1,
ROW_NUMBER () OVER (PARTITION BY t.B ARCODE ORDER BY tc REATE_TIME DESC) ROW_NUM
The from t_mes_test t where tc ategory='50' and t.r esult & lt;> '30') t1 left join
(select code_name_s, description_s from AT_BAS_CODE @ mes_link) code
On code. Code_name_s=t1. Reserved5
The order by create_time desc) t2
Where t2. Create_time between to_date (' ${st} '| |' 00:30:00 ', '- dd yyyy - mm hh24: mi: ss') and to_date (' ${of} Ed '| |' 00:29:59 ', '- dd yyyy - mm hh24: mi: ss') + 1) a2 where a2. Row_num=1) a
Left the join
(select the description, user_name from app_user @ MES_LINK) ap
On a.c reate_user=ap. User_name
B1)
Left the join
(select a. arcode_s, Amy polumbo art_number_s, part DESCRIPTION, p.e quipment_name, trans_time, at1. Shift_name_s, ap, first_name
The from at_wm_stock @ MES_LINK a, PART @ MES_LINK PART, pm_eqp_production p, app_user @ MES_LINK ap, AT_PM_SHIFT_CHANGE @ MES_LINK at1
Where
- barcode_s='8 c3000847 and
Amy polumbo art_number_s=part. Part_number
And p.i d_key=a.f rom_id_key_s
And ap) user_name=at1) user_name_s
And substr (a. atch_no_s, 1, 20)=at1. Batch_no_s
And at1. Equipment_name_s like 'CCXBM %'
- and trans_time between to_date (' ${st} '| |' 00:00:00 ', '- dd yyyy - mm hh24: mi: ss') and to_date (' ${of} Ed '| |' 23:59:59 ', '- dd yyyy - mm hh24: mi: ss')
The order by trans_time desc) a1
On a1. Barcode_s=b1. Barcode
) p where p.e quipment_name is not null order by create_time desc) kz
LEFT the JOIN
(SELECT B.m aterial_spec_s,
P. attern_s,
Amy polumbo art_number
The FROM part @ MES_LINK A
LEFT the JOIN uda_part @ MES_LINK B
ON Amy polumbo ART_KEY=B.O BJECT_KEY
) pp ON kz. Part_number_s=pp. Part_number
Where 1=1 ${if (len (equip)==0, ""," the and equipment_name in (' "+ equip +") ")}
The ${the if (len (res)==0, ""," the and result1 in (' res "+ +") ")}) aa
LEFT the JOIN (select t.b arcode, tc reate_time, tc reate_user, app. The description xb_user, t.r eserved6, t.r eserved7, DECODE (t.r esult, '10', 'entry qualified', '20', 'unqualified', '30', 'to qualified) result1, CODE. DESCRIPTION_S,
ROW_NUMBER () OVER (PARTITION BY t.B ARCODE ORDER BY tc REATE_TIME DESC) ROW_NUM
The from t_mes_test t
LEFT the JOIN app_user @ mes_link app ON app. User_name=tc reate_user
Left the join
(select code_name_s, description_s from AT_BAS_CODE @ mes_link) code
On code. Code_name_s=t.r eserved5
Where tc ategory='50' AND tc reate_time between to_date (' ${st} '| |' 00:30:00 ', '- dd yyyy - mm hh24: mi: ss')
And to_date (' ${of} Ed '| |' 00:29:59 ', '- dd yyyy - mm hh24: mi: ss') + 1 and t.r esult='30'
The order by tc reate_time desc) tt ON tt. Barcode=aa. Barcode
Left the join (select t.b arcode_s, TO_CHAR (t.l ast_modified_time, 'yyyy - MM - dd hh24: mi: ss'), sc. Code_name_s, sc. REMARKS_s from at_wm_stock_record_code @ mes_link sc, at_wm_stock @ mes_link t, (select *
The FROM AT_WM_STOCK_OPER_RECORD @ mes_link
WHERE ATR_KEY IN
(SELECT MAX (ATR_KEY)
The FROM AT_WM_STOCK_OPER_RECORD @ mes_link
WHERE OPER_TYPE_S='81'
GROUP BY STOCK_64_64
)
) the sr where t.a tr_key=sr. Stock_64_64
AND sr. Atr_key=sc. Stock_record_64) asr on asr. Barcode_s=aa. The barcode
CodePudding user response:
More than three million data for each tableCodePudding user response:
The SQL can you ran out of the data? So much dblink, might as well make upstream encapsulate directly, you collect every day, how long will this run? Great god!CodePudding user response:
This is a query in one of the mes system? Can handle to get this to first dizzy!!!!!!!!!!!CodePudding user response: