Results 1 to 8 of 8

Thread: Ïîìîù SQL

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Registered User
    Join Date: Oct:2012
    Location: Ñîôèÿ
    Posts: 3

    Ïîìîù SQL

    Çäðàâåéòå,
    èìàì íóæäà îò ìàëêî ïîìîù ñ SQL.
    Èìàì ñëåäíàòà çàÿâêà:

    Code:
    SELECT OPERATORS.OPERATOR_NAME, DOCUMENT_GL.NOMERFAKT, DOCUMENT_GL.WSICHKO
    FROM DOCUMENT_GL JOIN OPERATORS
         ON DOCUMENT_GL.OPER_ID IN (
     	SELECT OPERATORS.OPER_ID
    	FROM OPERATORS
    	WHERE OPERATORS.OPER_ID = DOCUMENT_GL.OPER_ID
         )
    WHERE OPERATORS.OPERATOR_NAME = 'Èâàí Ãåîðãèåâ';
    Ðåçóëòàòà å:

    Code:
    OPERATOR_NAME	  NOMERFAKT	WSICHKO
    Èâàí Ãåîðãèåâ	  465	                30.84
    Èâàí Ãåîðãèåâ	  467	                140.4
    Èâàí Ãåîðãèåâ	  2143                21.12
    Êàê äà ïðåïðàâÿ çàÿâêàòà, òàêà, ÷å â ñïèñúêà äà èçëåçå ÑÀÌÎ òîçè ðåä, íà êîéòî ñòîéíîñòòà âúâ êîëîíà WSICHKO e íàé - ãîëÿìà - ò.å. äà ôèëòðèðà ðåçóëòàòèòå ïî max ñòîéíîñò íà êîëîíà WSICHKO.

    Áëàãîäàðÿ ïðåäâàðèòåëíî!

  2. #2
    The Unknown rager's Avatar
    Join Date: Jul:2004
    Location: Edge Of Darkness
    Posts: 30,360
    Ïúðâî ïðîáâàé òàêà, ÷å íÿìàì ïîä ðúêà êúäå äà ãî òåñòâàì:
    Code:
    SELECT OPERATORS.OPERATOR_NAME, DOCUMENT_GL.NOMERFAKT, MAX(DOCUMENT_GL.WSICHKO)
    FROM DOCUMENT_GL JOIN OPERATORS
         ON DOCUMENT_GL.OPER_ID IN (
     	SELECT OPERATORS.OPER_ID
    	FROM OPERATORS
    	WHERE OPERATORS.OPER_ID = DOCUMENT_GL.OPER_ID
         )
    WHERE OPERATORS.OPERATOR_NAME = 'Èâàí Ãåîðãèåâ'
    GROUP BY DOCUMENT_GL.WSICHKO;
    È àêî íå ñòàíå, ùå òðÿáâà äà ñå ïðåïðàâè ïî-ñëîæíî.
    Asus CH6H|Ryzen 1800X@4.1GHz|4xBLS8G4D32AESTK@3666MHz 14-19-12-36-58-1T|GB GTX1060 3GB OC|MP510 960GB|SS-1000XP|CM STACKER STC-T01-UW1
    Thermochill PA120.2+PA120.3+PA140.3|Yate Loon 5xD12SH-12+3xD14SH-12|Laing DDC 2+Toshiba DIYINHK|EK DDC X-Top v2+EK Reservoir|EK Supreme HF #6

  3. #3
    Registered User
    Join Date: Jul:2001
    Location: Ñîôèÿ
    Posts: 329
    Ðåøåíèåòî íà rager íÿìà äà ïðîðàáîòè, çàùîòî ñå èçèñêâà ïîëåòàòà âêëþ÷åíè â select äà ïðèñúñòâàò è â group by èëè â àãðåãàòíè ôóíêöèè.
    Àç ïðåäëàãàì ñëåäíîòî:
    Code:
    SELECT top 1 OPERATORS.OPERATOR_NAME, DOCUMENT_GL.NOMERFAKT, DOCUMENT_GL.WSICHKO
    FROM DOCUMENT_GL JOIN OPERATORS
         ON DOCUMENT_GL.OPER_ID IN (
     	SELECT OPERATORS.OPER_ID
    	FROM OPERATORS
    	WHERE OPERATORS.OPER_ID = DOCUMENT_GL.OPER_ID
         )
    WHERE OPERATORS.OPERATOR_NAME = 'Èâàí Ãåîðãèåâ'
    ORDER BY DOCUMENT_GL.WSICHKO desc;
    Òîâà å íà äèàëåêò çà Microsoft SQL Server. Çà MySQL âìåñòî "top 1" òðÿáâà äà ñå âúâåäå "limit 1" ñëåä ïîñëåäíîòî ïîëå îò select è ïðåäè from.

  4. #4
    Registered User
    Join Date: Oct:2012
    Location: Ñîôèÿ
    Posts: 3
    Quote Originally Posted by Dorkas View Post
    Ðåøåíèåòî íà rager íÿìà äà ïðîðàáîòè, çàùîòî ñå èçèñêâà ïîëåòàòà âêëþ÷åíè â select äà ïðèñúñòâàò è â group by èëè â àãðåãàòíè ôóíêöèè.
    Àç ïðåäëàãàì ñëåäíîòî:
    Code:
    SELECT top 1 OPERATORS.OPERATOR_NAME, DOCUMENT_GL.NOMERFAKT, DOCUMENT_GL.WSICHKO
    FROM DOCUMENT_GL JOIN OPERATORS
         ON DOCUMENT_GL.OPER_ID IN (
     	SELECT OPERATORS.OPER_ID
    	FROM OPERATORS
    	WHERE OPERATORS.OPER_ID = DOCUMENT_GL.OPER_ID
         )
    WHERE OPERATORS.OPERATOR_NAME = 'Èâàí Ãåîðãèåâ'
    ORDER BY DOCUMENT_GL.WSICHKO desc;
    Òîâà å íà äèàëåêò çà Microsoft SQL Server. Çà MySQL âìåñòî "top 1" òðÿáâà äà ñå âúâåäå "limit 1" ñëåä ïîñëåäíîòî ïîëå îò select è ïðåäè from.
    Ñòàâà âúïðîñ çà oracle, çà ñúæàëåíèå è 3-òå ïðåäëîæåíèÿ íå ðàáîòÿò.

  5. #5
    Registered User
    Join Date: Jul:2001
    Location: Ñîôèÿ
    Posts: 329
    Â Oracle íÿìà òàêàâà êîìàíäà, íî íàìåðèõ ïðåïîðúêà êàê ìîæå äà ñå çàîáèêîëè:
    Code:
    select * from (
    SELECT OPERATORS.OPERATOR_NAME, DOCUMENT_GL.NOMERFAKT, DOCUMENT_GL.WSICHKO
    FROM DOCUMENT_GL JOIN OPERATORS
         ON DOCUMENT_GL.OPER_ID IN (
     	SELECT OPERATORS.OPER_ID
    	FROM OPERATORS
    	WHERE OPERATORS.OPER_ID = DOCUMENT_GL.OPER_ID
         )
    WHERE OPERATORS.OPERATOR_NAME = 'Èâàí Ãåîðãèåâ'
    ORDER BY DOCUMENT_GL.WSICHKO desc;
    )
    where rownum < 2
    Çà ñúæàëåíèå íÿìàì âúçìîæíîñò äà ïðîâåðÿ äàëè ðàáîòè.

  6. #6
    Registered User
    Join Date: Oct:2012
    Location: Ñîôèÿ
    Posts: 3

    Ðåøåíî!

    Quote Originally Posted by vp_petrov View Post
    Ñòàâà âúïðîñ çà oracle, çà ñúæàëåíèå è 3-òå ïðåäëîæåíèÿ íå ðàáîòÿò.
    Áëàãîäàðÿ Âè îùå âåäíúæ çà ñúäåéñòâèåòî, ïîíå ìå íàêëîíèõòå äà ðàáîòÿ â ïðàâèëíàòà ïîñîêà

    Ðåøåíèåòî å:

    Code:
    SELECT
    C1.OPERATOR_NAME,
    D1.NOMERFAKT,
    D1.DATAFAKT,
    D1.WSICHKO
    FROM
    DOCUMENT_GL D1, OPERATORS C1
    WHERE
    D1.WSICHKO=
    (SELECT MAX(D2.WSICHKO)
    FROM DOCUMENT_GL D2)
    AND
    C1.OPERATOR_NAME='Èâàí Ãåîðãèåâ'

  7. #7
    Âåãàí íà ìåñíà äèåòà XaMaB's Avatar
    Join Date: Nov:2001
    Location: Ñîôèÿ
    Posts: 20,387
    Quote Originally Posted by vp_petrov View Post
    Áëàãîäàðÿ Âè îùå âåäíúæ çà ñúäåéñòâèåòî, ïîíå ìå íàêëîíèõòå äà ðàáîòÿ â ïðàâèëíàòà ïîñîêà

    Ðåøåíèåòî å:

    Code:
    ...
    Ìàëêî ñúì ãî ïîçàáðàâèë îðàêúëà, íî êàêâî ñòàâà àêî 'Èâàí Ãåîðãèåâ' íå å òîÿ ñ MAX(D2.WSICHKO)?

    Èíà÷å àíàëîãà íà top 1/limit 1 áåøå íåùî êàòî
    Code:
    SELECT *
      FROM (SELECT * FROM table ORDER BY column desc)
     WHERE ROWNUM = 1;
    Ïîñëåäíà ðåäàêöèÿ: èçâúðøåíà îò XaMaB; íà äíåøíà äàòà. 0.42 ñåêóíäè ñëåä ïóñêàíå íà ïîñòà

    In God we Trust (all others must submit a X.509 certificate). Àêî ñïîðèø ñ èäèîò, âåðîÿòíî è òîé ïðàâè ñúùîòî èëè ñè ïîïàäíàë íà ïàðòèåí (íåïúëåí) ÷ëåí

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Copyright © 1999-2011 Õàðäóåð ÁÃ. Âúçìîæíî å ñúäúðæàíèåòî íà òàçè ñòðàíèöà äà å îáåêò íà àâòîðñêè ïðàâà.
iskamPC.com | mobility.BG | Bloody's Techblog | Êðèïòîâàëóòè è ìàéíèíã | 3D Vision Blog | Ìàãàçèí çà åëåêòðîííè öèãàðè