Results 1 to 9 of 9
Thread: Âúïðîñ çà SQL çàÿâêà
Hybrid View
-
12th September 2018 14:13 #1Registered User
Join Date: Mar:2018
Location: Áúëãàðèÿ
Posts: 93
Âúïðîñ çà SQL çàÿâêà
Èìàì åäèí ñòðàíåí êàçóñ çà SQL. Ïðè ìåí çàÿâêàòà íå âúðâè, âñúùíîñò âúðâè, íî íå èçêàðâà íèêàêâè ðåçóëòàòè, à ïðè êîëåãàòà ñè âúðâè áåç ïðîáëåì, èçêàðâà ìó äàííèòå. Íàìèðàòå ëè íÿêàêâà ëîãè÷åñêà ãðåøêà â çàÿâêàòà? Íÿêàêâè ñúâåòè?
Code:select t.barcode, t.shift_date, t.start_date, t.shift, t.attribute_2 model, bench, t.attribute_4 color, tff.name kilnFF, m.name kiln, decode(t.prod_qty, 1, 'GOOD', decode(t.prod_def_qty, 1, 'EP_GR', decode(t.rework_qty, 1, 'REW', 'LOSS'))) status, td.defect_id defect, td.defect_location_id defect_location, sum(t.prod_qty + t.prod_def_qty + t.rework_qty + t.loss_qty) pcs, (select mold_position from s_trm_cast_plan s where mold_position in (1, 2, 3 ,4) and s.barcode=t.barcode) from (select ttsm.* from s_tr_tracking ttsm, (select tracking_num, max(sequence_num) sequence_num from s_tr_tracking where start_date between '2018-04-02' and '2018-04-09' and process_id = 21 and attribute_2 = any ('T2812') group by tracking_num) tts where ttsm.tracking_num = tts.tracking_num and ttsm.sequence_num = tts.sequence_num) t, (select tm.tracking_num, tm.sequence_num, mr.name from s_tr_tracking_machine tm, s_trl_machine_resource mr where tm.machine_id = mr.machine_id and mr.mach_type = 6) m, (select tracking_num, sequence_num, defect_id, defect_location_id from s_tr_tracking_defect where main = 1) td, (select tt.barcode, mr.name from s_tr_tracking tt, s_tr_tracking_machine tm, s_trl_machine_resource mr where tt.process_id = 31 and tt.barcode != (select barcode from s_tr_barcodes where barcode_id = 1) and tt.tracking_num = tm.tracking_num and tt.sequence_num = tm.sequence_num and -- tt.shift_date <= case when to_date('MAY/11/04') < to_date('MAY/20/04') then 'MAY/20/04' else 'MAY/11/04' end and tm.machine_id = mr.machine_id and mr.mach_type = 6) tff, (select distinct barcode, name bench from s_tr_tracking s, (select tracking_num, sequence_num, name from s_tr_tracking_machine s, (select distinct machine_id, name from t_machines where mach_type != 4 and name = 'A10(30)') t where s.machine_id=t.machine_id) t where process_id = 21 and s.tracking_num=t.tracking_num and s.sequence_num=t.sequence_num and barcode <> 'ÍÅÇÀËßÒÎ')b where t.tracking_num = m.tracking_num (+) and t.sequence_num = m.sequence_num (+) and t.tracking_num = td.tracking_num (+) and t.sequence_num = td.sequence_num (+) and t.barcode = tff.barcode (+) and t.barcode=b.barcode group by t.barcode, t.shift_date, t.start_date, t.shift, t.attribute_2, t.attribute_4, bench, m.name, decode(t.prod_qty, 1, 'GOOD', decode(t.prod_def_qty, 1, 'EP_GR', decode(t.rework_qty, 1, 'REW', 'LOSS'))), td.defect_id, td.defect_location_id, tff.name;
-
12th September 2018 15:04 #2
Çàÿâêàòà å äîñòà äåáåëà è ÷åñòíî êàçàíî ìå ìúðçè äà âëèçàì â äåòàéëè (îùå ïîâå÷å íå çíàåì êàêâà å ñõåìàòà, âúðõó êîÿòî ñå ïóñêà), íî àç ëè÷íî ìîãà äà äàì êàòî ñúâåò äà ñè ñðàâíèòå äàííèòå è òàáëèöèòå, âúðõó êîèòî ÿ èçïúëíÿâàòå. Óâåðåòå ñå, ÷å ñà èäåíòè÷íè. Çàùîòî àç ïàòèõ äîñòà ñ åäíè ñïðàâêè, íàêðàÿ ñå îêàçà, ÷å çàïèñà íà äàííèòå íà åäíàòà ñðåäà íå å ñå îñúùåñòâÿâàëà êîðåêòíî, èìàøå null çàïèñè, êîèòî êàðàõà ñïðàâêàòà äà íå âàäè î÷àêâàíèòå ðåçóëòàòè.
Asus A320M-K | R5 1600X | 2x8GB Crucial 3200 | RX 480 4GB | Team 256GB M2 | CM 500W | Zalman Case | Sennheiser HD598SR + Creative tactic 3d sigma | Redragon Varuna|MSI 27'' 144hz|Noblechairs Epic| Ñàéòúò ìè çà ìåõàíè÷íè êëàâèàòóðè |
-
12th September 2018 15:21 #3
À ëîãîâåòå ðàçãëåäà ëè ? Ïðåç êàêâî ÿ ïóñêàø ?
Last edited by elby; 12th September 2018 at 15:45.
Jetway I35P-SG-PB/C2D E2140@3333MHz BOX,8x415 1,45V/2x1GB Kingmax DDR2 1038MHz/XFX 7600GT 256MB DDR3/Fortron350W
-
12th September 2018 15:28 #4
 äîïúëíåíèå êúì @amd_fan, êàòî ñëåäâàùà ñòúïêà ìîæå äà ïîãëåäíåòå çà ðàçëèêà â âåðñèèòå íà RDBMS è/èëè çà ðàçëèêè â config ôàéëîâåòå, òåçè íåùà ñúùî ìîãàò äà óêàæàò âëèÿíèå â-õó êðàéíèÿ ðåçóëòàò. Äðóãî, êîåòî ìîæå äà ïðîáâàòå å äà äåáúãâàòå subquery ïî subquåry (ïî÷âàéêè îò íàé-âúòðåøíîòî, íàâúíêà) çà ðàçëèêè â ðåçóëòàòà (ãëåäàì ÷å íå ñà ìàëêî).
Last edited by haho; 12th September 2018 at 15:29.
It seems that perfection is attained not when there is nothing more to add, but when there is nothing more to remove.
-
12th September 2018 16:10 #5Registered User
Join Date: Mar:2018
Location: Áúëãàðèÿ
Posts: 93
Áëàãîäàðÿ çà ñúâåòèòå
. Ùå ïðîáâàì íåùàòà, êîèòî ïðåïîðú÷àõòå. Èíà÷å ïóñêàì çàÿâêòå ïðåç SQL Developer
-
13th September 2018 09:20 #6
Åé òàçè õàêåðùèíà êîìåíòèðàíà ëè å è ïðè äâàìàòà:
?Code:tt.shift_date <= case when to_date('MAY/11/04') < to_date('MAY/20/04') then 'MAY/20/04' else 'MAY/11/04' end
Îò òóê ñå âèæäà, ÷å èìà íÿêàêâà îñîáåíîñò ñ äàííèòå, ñëåäîâàòåëíî ìîæå è äà ñà íàìàçàíè îùå. Àç çàëàãàì íà ðàçëè÷íè (èëè ðàçëè÷íî îìàçàíè) äàííè ïðè òåá è ïðè êîëåãàòà, êîåòî åñòåñòâåíî âîäè äî ðàçëè÷íè ðåçóëòàòè. Îòäåëíî, âèæäàì, ÷å barcode ìîæå äà å "ÍÅÇÀËßÒÎ", êîåòî ñúùî íÿìà ñìèñúë.
Èçîáùî òàçè çàÿâêà å èçëèøíî ñëîæíà, à íàé-âåðîÿòíî è ñõåìàòà íå å ñúâñåì ñïîðåä äàííèòå. Êàêòî ñà êàçàëè êîëåãèòå, ïóñêàé âúòðåøíèòå çàÿâêè åäíà ïî åäíà è âèæ êàêâî ùå èçëåçå. Çà äà íå âèæäàø äàííè, íÿêîå îò óñëîâèÿòà íå ñå èçïúëíÿâà.
Ìîæå äà ïðîáâàø è äà àíàëèçèðàø çàÿâêàòà. MySQL ïîíÿêîãà ïîäñêàçâà äîáðå, êàòî êàçâà, ÷å íà åäè êîÿ ñè ñòúïêà èìà "Impossible Where".Fujistu Lifebook E756 | Core i7-6500U / 400MHz-3.1GHz | 8 GB DDR4-2133 | Samsung PM871 / 256 GB SSD | 15" 1920x1080 | Manjaro Linux + kernel 4.19
-
7th February 2019 15:24 #7Registered User
Join Date: Jan:2019
Location: around
Posts: 1,973
Êàêâî å çíà÷åíèåòî çà êðàéíèÿ ïîòðåáèòåë(íÿêîé , êîéòî ëóïà çàÿâêè çà îò÷åòè ïî öÿë äåí) äàëè áàçàòà å SQL, Oracle...? Îñâåí ÷å îïðåäåëåíè òóëóâî èçèñêâàò äàäåí òèï áàçà, êàêâî å äðóãîòî çíà÷åíèå çà ïèøåùèÿ ñåëåêòè?
Last edited by luibitel; 8th February 2019 at 10:22.




Reply With Quote
Lenovo ThinkPad 15 èëè IdeaPad 15
5th May 2023, 22:16 in Ìîáèëíè êîìïþòðè