Witam Szanowne Grono,
Przegladajac wiele kursow mySQL-a zauwazylem, ze wiekszosc konczy sie wtedy gdy sie robi ciekawie (niestety). Dlatego zwracam sie do Was z moim pomyslem:
1. nie jest to kurs mySQL-a
2. bedzie to male faq
3. pomijamy pytania typu jak wybrac dane z jednej tabeli - czyli to co jest w kursach podstawowych
4. jezeli ktos chcedodac cos od siebie nie oponuje - zapraszam
dane wyjsciowe:
jest 5 tabel
- <b>klient</b> - [id].[nazwa]
- <b>faktura</b> - [id].[data_wystawienia].[id_klienta]
- <b>faktura_pozycje</b> - [id].[id_faktury].[id_towaru].[ilosc].[kwota_jednostkowa_netto].
.[stawka_vat].[wartosc_vat].[wartosc_netto].[kwota_brutto].[ilosc]
- <b>towar</b> - [id].[nazwa].[jm].[id_gupy_towarowej]
- <b>grupa_towarowa</b> - [id].[nazwa]
z tych tabel uzytkownik chce wyciagnac nastepujace dane:
<b>A</b> sume kwot wartosc_netto sprzedanych towarow z podzialem na kwartaly
<b>B</b> ilosc poszczegolnych stawek podatku VAT z podzialem na miesiace (wynik: 7% - kwieciec 2003 - 13 razy; oczywiscie takie cos nigdy nie jest potrzebne)
<b>C</b> sume kwot podatku VAT z poszczegolnych stawek podatku VAT z podzialem na miesiace (wynik: 7% - kwieciec 2003 - 2045.33 zł)
<b>D</b> ilosc faktur wystawionych na wszystkich klientow z podzialem na klientow i miesiace - pole nadrzedne klient | miesiac | ilosc
<b>E</b> suma wartosc_netto i ilosc z podzialem na sprzedaz danego towaru oraz miesiace
<b>F</b> Ilosc faktur w ktorych pokazal sie dany (wybrany) towar
<b>G</b> Wybranie nazwy_towaru/ilosc sprzedanego w roku 2002 danemu klientowi z danej grupy towarowej (dane znane id klienta, id grupy towarowej)
to sa pytania ktore przyszly mi do glowy. odpowiedz na nie daje duzo mozliwosci innym uzytkownikom. daje mozliwosc sprawdzennia swojej wiedzy i mam nadzieje ze wywola mala merytoryczna dyskusje.
INSTRUKCJA:
- jak udzielamy odpowiedzi - procz zapytania mySQL chcialbym aby pojawilo sie jego wytlumaczenie (SELECT FROM WHERE ORDER BY wykluczam) oto przyklad
2xTAK, ale czy mógłbyś zamieścić kod SQL generujący tabele? Byłoby szybciej, i bardziej "jednolicie".
Update Skleroza nie boli. Do kodu oczywiście przydałyby sie INSERTy z przykładowymi danymi.
ok nastapila zmiana z ogloszenie na przyklejony
zgodnie z glosami w postaci postow na forum oraz informacjami na PW przedstawiam Wam plik ktory zawiera wszystko, aby taka baze stworzyc u siebie lacznie z danymi. Zobaczyc w przegladarce mozna http://mstudio.nq.pl/php_pl/inne/sql_for_php_db.sql.
Zaznaczam jednoczesnie iz to nie jest baza ktora gdzies funkcjonuje. Zrobilem ja na Wasze zyczenie jak i w celu wsparcia Waszych prac. Dlatego prosze o przymkniecie oka na ewentualne bledy (oczywiscie wykazanie bedzie mialo znaczenie).
Wszystkie tabele zawieraja przedrostek php_*.
Pozdrawiam i zycze owocnego myslenia
_____________________________________________
*DeyV - 5 czerwa - powyższe linki zostały aktualizowane
No to zaczynamy.
Wyświetlanie grup towarów, oraz ilości należących do nich produktów:
SELECT `php_grupa_towarowa`.`id`, `php_grupa_towarowa`.`nazwa`, COUNT(`php_towar`.`id`) AS `ile` FROM `php_grupa_towarowa` LEFT JOIN `php_towar` ON (`php_towar`.`id_gupy_towarowej` = `php_grupa_towarowa`.`id`) GROUP BY `php_grupa_towarowa`.`id`
A teraz coś innego:
Ilość faktur, które pojawiły się w poszczególnych miesiącach. Pokazujemy tylko te miesiace i lata, w których coś sie działo:
SELECT count( `id` ) , MONTH ( `data_wystawienia` ) AS `msc` , YEAR( `data_wystawienia` ) AS `rok` FROM `php_faktura` GROUP BY `msc` , `rok`
SELECT count( id ) , MONTH ( `data_wystawienia` ) AS `msc` , YEAR( `data_wystawienia` ) AS `rok` FROM `php_faktura` WHERE ( MONTH ( `data_wystawienia` ) = '1' ) GROUP BY `msc` , `rok`
nie lepiej pole daty zmienic na tym timestap ?
SELECT count(`id`), MONTH(`data_wystawienia`) AS `msc`, YEAR(`data_wystawienia`) AS `rok` FROM `php_faktura` GROUP BY `msc`, `rok`;
CREATE TEMPORARY TABLE bzium SELECT id_klienta FROM faktura WHERE data_wystawienia>="2003-03-01" AND data_wystawienia<="2003-03-31" GROUP BY id_klienta; SELECT klient.id_klienta FROM bzium RIGHT JOIN klient ON klient.id_klienta=bzium.id_klienta WHERE bzium.id_klienta IS NULL;
1. Myślę, że nieco lepiej wyglada to tak:
choć przyznam sie, że mam problem z wykorzystanim takich konstrukcji w php.
CREATE TEMPORARY TABLE `tmp` SELECT `php_klient`.`id` , `php_klient`.`nazwa` , count( `php_faktura`.`id` ) AS `ile` FROM `php_klient` INNER JOIN `php_faktura` ON ( `php_klient`.`id` = `php_faktura`.`id` ) WHERE MONTH (`php_faktura`.`data_wystawienia` ) = 1 GROUP BY `php_klient`.`id` LIMIT 0, 30 SELECT * FROM `tmp` WHERE `ile`= '0';
Troche prosta ta baza... No ale dobra. Mam zadanie:
Zalozmy ze w tabeli php_faktura jest pole wartosc_szacunkowa typu int (nie wnikajcie w sens pola).
Zadanie:
Zwrocic dla kazdego klienta sume wartosci szacunkowych i sume wartosci netto wszystkich faktur jakie klient ma. Jednym zapytaniem w MySQLu, jesli to mozliwe.
Nudzilem sie troche, przypomnial mi sie ten topic, wiec sprobowalem swoich sil...
Zajmowalem sie tylko zadaniami z pierwszego posta w tym topicu.
Oto rozwiazania (mam nadzieje ze dobre ):
A - sume kwot wartosc_netto sprzedanych towarow z podzialem na kwartaly
---------
SELECT SUM(p.wartosc_netto) suma, # sumujemy wartosc netto CASE WHEN MONTH(data_wystawienia) BETWEEN 1 AND 3 THEN "I kwartał" WHEN MONTH(data_wystawienia) BETWEEN 4 AND 6 THEN "II kwartał" WHEN MONTH(data_wystawienia) BETWEEN 7 AND 9 THEN "III kwartał" WHEN MONTH(data_wystawienia) BETWEEN 10 AND 12 THEN "IV kwartał" END kwartal # dajemy na to alias'a kwartal, aby moc pogrupowac FROM `php_faktura_pozycje` p LEFT JOIN `php_faktura` f ON ( p.id_faktury = f.id ) GROUP BY kwartal; # grupujemy po kwartale, w celu uzyskania kwartalnego zestawienia
SELECT `stawka_vat`, COUNT(p.id) ile, # pobieramy stawke i jej ilosc CONCAT_WS(" ",MONTHNAME(data_wystawienia),YEAR(data_wystawienia)) DATA FROM `php_faktura_pozycje` p LEFT JOIN `php_faktura` f ON ( p.id_faktury = f.id ) GROUP BY MONTH(data_wystawienia), YEAR(data_wystawienia), stawka_vat; # grupujemy po roku i miesiacu (zeby kazdy miesiac kazdego roku byl liczony osobno) # i po stawce vat, zeby otrzymac zestawienie
SELECT `stawka_vat`, SUM(p.wartosc_vat) suma, CONCAT_WS(" ",MONTHNAME(data_wystawienia),YEAR(data_wystawienia)) DATA FROM `php_faktura_pozycje` p LEFT JOIN `php_faktura` f ON ( p.id_faktury = f.id ) GROUP BY MONTH(data_wystawienia), YEAR(data_wystawienia), stawka_vat; # to samo co w poprzednim, tylko ze nie zliczamy ilosci tylko sumujemy wartosci
SELECT k.nazwa klient, MONTHNAME( f.data_wystawienia ) miesiac, COUNT( f.id ) ilosc FROM `php_faktura` f INNER JOIN `php_klient` k ON ( f.id_klienta = k.id ) GROUP BY klient,miesiac # grupujemy wg klient, miesiac, zeby kazdy miesiac kazdego klienta byl # liczony osobno
SELECT COUNT(DISTINCT id_faktury) `ile` FROM `php_faktura_pozycje` p WHERE p.`id_towaru` = 5 # dane id_towaru # musi byc DISTINCT, zeby tylko raz liczyc dany towar jesli sie pojawil wiecej niz raz na jednej fakturze SELECT `t`.`nazwa` `towar`, # pobieramy nazwe towaru COUNT(DISTINCT id_faktury) `ile` # ilosc unikalnych (DISTINCT) wartosci id_faktury FROM `php_faktura_pozycje` p LEFT JOIN `php_towar` t ON (`t`.`id` = `p`.`id_towaru`) WHERE p.`id_towaru` = 5 # dane id_towaru GROUP BY `towar`; # to samo, tylko ze jeszcze z pobieraniem nazwy tego towaru
SELECT count( id ) , MONTH ( `data_wystawienia` ) AS `msc` , YEAR( `data_wystawienia` ) AS `rok` FROM `php_faktura` WHERE ( MONTH ( `data_wystawienia` ) = '1' ) GROUP BY `msc` , `rok`
Wszystko to jest piękne i ładne, tyle że w nierealnym świecie, gdzie nie ma Balcerowicza i jego kamratów W naszej cudnej rzeczywistości tak jednak nie jest, zatem należy tu czym prędzej wprowadzić poprawki koncepcyjne, gdyż przyjmując powyższe założenia narażamy się albo na zemstę zdesperowanych sprzedawców albo na odwiedziny organów zaprzyjaźnionych z urzędem skarbowym (prokurator, policja, komornik etc. ). Tak więc by nie pozostał ten trud jedynie uroczym ćwiczeniem umysłowym, lub, co gorsza, wiodącym na manowce znakiem, należałoby już w fazie koncepcyjnej poczynić stosowne korekty.
1) vat (hłe hłe hłe... vat...) należy przypisać do grup towarowych i stamtąd ściągać na fakturę. Przy obecnej strukturze jest on wprowadzany arbitralnie przy każdej pozycji przez użytkownika wystawiającego fakturę, co jest zaproszeniem wprost do przekrętów w rozumieniu ustawy karno-skarbowej. Pół biedy gdy będzie to hurtownia mat. budowlanych, w której vat na wszystko jeden, ale w branży spożywczej, gdzie wystepują dokładnie wszystkie stawki, żywot programu (i programisty ) byłby niepomiernie krótki...
2) a teraz takie ćwiczonko: mamy towary o wartości 1 grosza w stawce 22%. Liczymy: 1 towar = 1 gr netto + 0 gr vat (zgodnie z regułami zaokrąglania). Sprzedajemy 100 różnych takich towarów i mamy : 100 gr netto + 0 gr vat (a Balcerowicz & Co. już zacierają rączki ) Zatem do tabeli 'faktury' należy przerzucić pola 'podatek_vat' z tabeli 'faktura_pozycje' i do której są sumowane pozycje netto z 'faktura_pozycje'. Dopiero tam następuje przeliczenie należnego podatku vat zbiorczo od wszystkich pozycji na fakturze...
To tyle co mi się nasunęło. Oczywiście niekoniecznie to już koniec, bo przepisy są takie, że... Ale zdaje się że to chyba już temat poza treścią tego forum
Mozna tez w inn sposob zrobic zapytanie do bazy czy np jak u mnie pracodawca złozył jakas oferte
SELECT pracodawcy.idpracodawca, pracodawcy.nazwisko FROM pracodawcy LEFT JOIN oferty_pracodawcow USING ( idpracodawca ) WHERE oferty_pracodawcow.IDoferta_pracodawcy IS NULL
a jak zrobić takie zapytanie, które wyszuka np według kontrahentów, pokaże wynik - i teraz po naciśnięciu pokaże faktura pozaycje,
zakładamy że produkty faktura_pozycje mają takie same ID jak faktura.
Zwrócony wynik zapytania powinien tworzyć odnośniki do faktur poszczególnego klienta, a więc trzeba odpowiednio sformatować tabelę wyników, tak aby zwróciła listę kontrahentów, która będzie automatycznie odnośnikami,
Na potrzeby tych skryptów należy przyjąć:
- wszystkie wartości w tabelach jako "id" zostały zamienione zgodnie z nazwą tabeli, np. w tabeli "php_klient" id zmieniono na id_klienta, itd.
Niestety jest to wersja demo, nie testowana.
Tworzymy plik np. "klienci.php" odpowiedzialny za pobranie listy klientów i wyświetlenie odnośników do ich faktur. Listing poniżej.
To teraz ja mam pytanie. Jak z takiej bazy, z tabeli "faktura" ([id].[data_wystawienia].[id_klienta]), wyciągnąć id rekordu, gdzie data wystawienia jest taka sama dla dwóch klientów. Czyli np. mamy klienta 1 i 234, jak sprawdzić, czy w jakimś dniu została wystawiona faktura im obu?
SELECT id_klienta, id FROM php_faktura GROUP BY data_wystawienia
SELECT COUNT(id_klienta) AS count, id, data_wystawienia FROM php_faktura WHERE id_klienta=1 AND id_klienta=234 GROUP BY data_wystawienia
SELECT COUNT(id_klienta) AS count, id, data_wystawienia FROM php_faktura WHERE id_klienta=1 AND id_klienta=234 GROUP BY data_wystawienia
To jest blad logiczny. Id klienta nie moze byc jednoczesnie rowne 1 i 234. Co najwyzej moze byc rowne 1 LUB 234. Czyli powinno byc
id_klienta = 1 AND id_klienta = 234
id_klienta = 1 OR id_klienta = 234
SELECT COUNT( data_wystawienia ) AS ile, data_wystawienia FROM php_faktura WHERE id_klienta = 1 OR id_klienta = 234 GROUP BY data_wystawienia HAVING ile > 1
Znalazłem chyba najbardziej odpowiedni wątek
Chcę wyświetlić zamówienia (order_status BETWEEN '6' AND '7') dla których nie wydano wszystkich towarów z magazynu.
tabele:
orders - order_id
orders_inc - order_id, prod_id, ilosc
fk_docs - order_id (jesli pole korekta == 1 muszę odjąć od liczby wydanych)
fk_docs_items - doc_id, prod_id, ilosc
kod PHP który to robi dla jednego zamowienia działa poprawnie dla dla 16k rekordów to jakaś masakra i wydawało mi się, że może zapytaniem będzie lepiej to zwracać
$totalCounts = http://www.php.net/array(); $list = $fk->getDocs("*", " WHERE typ='3' AND `order_id`='" . $order['order_id'] . "'"); $produkt = new Produkt; foreach ($list as $key => $pos) { $items = $fk->getDocItems("*", " WHERE `doc_id` = '" . $pos['id'] . "'"); foreach ($items as $item) { $in[] = $item['prod_id']; if (!$pos['korekta']) $totalCounts[$item['prod_id']] += $item['ilosc']; else $totalCounts[$item['prod_id']] -= $item['ilosc']; } $in = http://www.php.net/array_unique($in); $products = $produkt->getProduct(http://www.php.net/array(), " AND products.prod_id IN (" . http://www.php.net/implode(',', $in) . ")"); $list[$key]['items'] = $items; $list[$key]['items_full'] = $products; } $Zamowienie = new Zamowienie(); $Zamowione = $Zamowienie->getZamowioneProdukty($order['order_id'], true); $doWydania = 0; foreach ($Zamowione as $k => $v) { $Zamowione[$k]['ilosc'] -= $totalCounts[$v['pid']]; $Zamowione[$k]['magazyn'] = $Zamowione[$k]['mag']; $doWydania += $Zamowione[$k]['ilosc']; }
SELECT o.order_id, sum(oi.ordinc_ilosc) AS zam_ilosc, sum(fkd.ilosc) AS liczba FROM orders o JOIN orders_inc oi ON o.order_id = oi.order_id LEFT JOIN fk_docs fk ON fk.order_id = o.order_id LEFT JOIN fk_docs_items fkd ON fk.id = fkd.doc_id WHERE o.order_status BETWEEN '6' AND '7' GROUP BY o.order_id HAVING liczba IS NULL OR zam_ilosc <> liczba ORDER BY o.order_id DESC
Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)