Subselect w jednej tabeli, Wyświetlić listę produktów z sumowaną ilością towaru Opcje

[bojak]

Witam.

Na wstępie powiem, że do tej pory jakość sobie radziłem z różnymi problemami, ale.. brak mi pomysłu i trochę czas nagli (jest wiele osób, które posiadają ten sam sklep (Shoper lub ClikShop) i ten sam problem, a aktualizacja z poprawką tego szybko pewnie nie nadejdzie znając życie). Nie liczę na gotowe rozwiązanie a chociaż na pokazanie mi drogi do rozwiązania.

Mam sklep internetowy ale niestety nie aktualizują się stany magazynowe produktu głównego na podstawie wariantów. chciałbym:
1. uzyskać listę produktów, które należy zaktualizować - lista muszę wyeksportować do pliku tylko sam product_id w przypadku gdy suma magazynowa wariantów jest różna od stanu magazynowego produktu głównego
2. aktualizacja stanu magazynowego na podstawie stanu magazynowego wariantów

Do rzeczy. Struktura tabeli (część).

[SQL] pobierz, plaintext 
mysql> SELECT `product_id` , `stock`, extended FROM products_stock;
[SQL] pobierz, plaintext 

+------------+-------+----------+
| product_id | stock | extended |
+------------+-------+----------+
| 110 | 7 | 0 |
| 110 | 1 | 1 |
| 110 | 1 | 1 |
| 112 | 5 | 0 |
| 112 | 0 | 1 |
| 113 | 9 | 0 |
| 113 | 2 | 1 |
| 113 | 1 | 1 |
| 113 | 2 | 1 |
| 113 | 2 | 1 |
| 115 | 4 | 0 |
| 115 | 1 | 1 |
| 115 | 1 | 1 |
| 115 | 2 | 1 |

extended =0 - produkt główny, 1-wariant produktu

wymyśliłem zapytanie (Osobne zapytania działają, ale nie wiem jak je połączyć w całość):

[SQL] pobierz, plaintext 
SELECT `product_id`,stock,(SELECT sum(stock) FROM products_stock WHERE extended=1 GROUP BY product_id)
FROM products_stock
WHERE extended=0
GROUP BY product_id
[SQL] pobierz, plaintext 

chciałem chociaż uzyskać wynik produck_id, stock (główny), sum(stock - wariantów)

ale było by to zbyt piękne by się udało - wynik wyszedł spodziewany - subquery returns more then 1 row.
wynika to z tego, że nie wszystkie produkty główne mają warianty (czyli są product_id gdzie brak jest extended=1)

Rozważałem też wynik z

[SQL] pobierz, plaintext 
SELECT `product_id`,sum(stock) FROM products_stock WHERE extended=1 GROUP BY product_id
[SQL] pobierz, plaintext 

zrobić jako tabelę tymczasową (macierz), ale zależy mi na jak najprostszym rozwiązaniu - skrypt będzie uruchamiany co 15-30 minut

Co podobnego już robiłem, ale jak pewne rzeczy robi się raz na rok to się tego nie pamięta.

[kapslokk]

[SQL] pobierz, plaintext 
SELECT 
x.product_id, 
x.stock AS IloscGlownego,
sum(x1.stock) AS IloscWariantow,
(x.stock + sum(x1.stock)) AS Razem
FROM XX x
LEFT JOIN XX x1 ON 
x.product_id = x1.product_id &&
x1.`extended` = 1
WHERE x.`extended` = 0 GROUP BY product_id;
[SQL] pobierz, plaintext 

http://sqlfiddle.com/#!9/170eb/8
O coś takiego chodzi? Jeśli nie to podaj prawidłową tablicę wyjściową dla danych z pierwszego postu.

[bojak]

Jest bardzo blisko - będę musiał poprawić

dokładniej to potrzebuję samą listę product_ID gdy IlośćGłównego<>Iloscwariantów -potrzebna do eksportu do pliku i wykorzystane będzie w skrypcie sh który jest już gotowy
a potem muszę zrobić aktualizację ilośćGłówna na podstawie IlośćWariantów

Wrócę z pracy wieczorem to będę przy tym siedział.

Bardzo dziękuję.

Siedzę od 1,5h no i mam dumam jak rozwiązać problem z dodaniem jeszcze jednego warunku.

[SQL] pobierz, plaintext 
SELECT product_id,stock,extended FROM `products_stock` LIMIT 10
[SQL] pobierz, plaintext 

product_id | stock | extended
110 | 7 | 0
110 | 1 | 1
110 | 1 | 1
112 | 1 | 0
112 | 0 | 1
113 | 7 | 0
113 | 1 | 1
113 | 2 | 1
113 | 2 | 1
113 | 2 | 1

i teraz chciałbym aby sprawdzić czy x.stok gdzie x.extended=0 jest równy/różny sum(x1.stock) gdzie x1.extended=1. Biorąc sytuację że chciałbym mieć tylko product_ID gdy x.stock<>sum(x1.stock) to tabela powinna wyglądać tak

product_id | IloscGlownego | IloscWariantow
110 | 7 | 2
112 | 1 | 0


113 - nie wyświetla się bo jest ok - IloscGlownego=7 i suma IloscWariantow =7

Podany kod działa dobrze, ale tylko gdy wyświetlamy. Próbowałem na różne kombinacje ale niestety nie wychodzi mi. Dopisanie warunku

[SQL] pobierz, plaintext 
AND 'IloscGlownego'<>'IloscWariantow'
[SQL] pobierz, plaintext 

niestety nie zmienia nic. Próbowałem z wykorzystaniem HAVING.i też nic

[SQL] pobierz, plaintext 
SELECT x.product_id, x.stock AS IloscGlownego ,sum(x1.stock) AS IloscWariantow
FROM products_stock x
RIGHT JOIN products_stock x1 
ON x.product_id=x1.product_id && x1.extended=1
WHERE x.extended=0
GROUP BY product_id
HAVING  'x.stock'<>(SELECT sum('x1.stock') FROM products_stock x1 WHERE x1.extended=1  GROUP BY product_id LIMIT 1)
[SQL] pobierz, plaintext 

Otrzymuję nic

Czy może jakaś sugestia (IMG:style_emoticons/default/questionmark.gif)

a jednak działa z having - inaczej zapisane i działa

[SQL] pobierz, plaintext 
SELECT 
x.product_id, 
x.stock AS IloscGlownego,
sum(x1.stock) AS IloscWariantow
 
FROM products_stock x
LEFT JOIN products_stock x1 ON 
x.product_id = x1.product_id && x1.`extended` = 1
WHERE x.`extended` = 0 
GROUP BY product_id
HAVING x.stock<>sum(x1.stock)
[SQL] pobierz, plaintext 

teraz tylko kod do aktualizacji IloscGlownego na podstawie IloscWariantow.

[trueblue]

Pod warunkiem, że główny jest tylko jeden:

[SQL] pobierz, plaintext 
SELECT product_id, SUM(IF(extended=0,stock,0)) AS main, SUM(IF(extended=1,stock,0)) AS extended
FROM product
GROUP BY product_id
HAVING main<>extended
[SQL] pobierz, plaintext 

[bojak]

Mam pytanie jeszcze z innej beczki.

napisałem zapytanie które tworzy plik ze ścieżką do usuwania plików (skrypt basch)

[SQL] pobierz, plaintext 
SELECT concat('find /home/bojak/domains/image-collection.pl/public_html/environment/cache/objects/ -type f -name "zend_cache---productStock_',stock_id,'" -exec rm {} \\;')
[SQL] pobierz, plaintext 

problem dotyczy ostatnich znaków "\\". prawidłowa komenda to

[SQL] pobierz, plaintext 
-exec rm {} \;
[SQL] pobierz, plaintext 

. Problem jest taki, że jak na końcu polecenia wpiszę jeden \ to baza wywala błąd, skolei jak piszę \\ to w PHPMyadmin widzę tylko jedno \, natomiast jak wysyłam to do pliku to otrzymuję też \\. potem muszę użyć dodatkowego polecenia

[HTML] pobierz, plaintext 
sed -i 's/\\;/\; /g' 1-wariant-zero.out.sh
[HTML] pobierz, plaintext 

do usuwania jednego \.

czy jest jakieś rozwiązanie bez dodatkowych poleceń tak by w pliku zapisał się \. Próbowałem zastąpić \ poleceniem char(92). Ale jest tak samo w PHPmyadmin jest \ a w pliku \\

Pod warunkiem, że główny jest tylko jeden:

[SQL] pobierz, plaintext 
SELECT product_id, SUM(IF(extended=0,stock,0)) AS main, SUM(IF(extended=1,stock,0)) AS extended
FROM product
GROUP BY product_id
HAVING main<>extended
[SQL] pobierz, plaintext 

Jest pięknie napisane, ale wynik nie uwzględnia produktów głównych, które nie mają wariantów - a niestety takie tez bywają.

[trueblue]

Jest pięknie napisane, ale wynik nie uwzględnia produktów głównych, które nie mają wariantów - a niestety takie tez bywają.

[SQL] pobierz, plaintext 
SELECT product_id, SUM(IF(extended=0,stock,0)) AS main, SUM(IF(extended=1,stock,0)) AS extended
FROM product
GROUP BY product_id
HAVING main<>extended OR extended=0
[SQL] pobierz, plaintext 

Jeśli nie jest poprawnie, podaj przykładowy rekord dla głównego bez wariantów.

[bojak]

No właśnie problem w tym, że w tej tabeli nie ma żadnego znacznika że produkt ma warianty. przeglądałem inne tabele i też nic nie zauważyłem.

[HTML] pobierz, plaintext 
product_id | stock | extended
140 | 7 | 0
141 | 1 | 0
141 | 1 | 1
[HTML] pobierz, plaintext 

produkt główny 140 nie ma wariantów, produkt 141 ma 1 wariant

[trueblue]

Sprawdzałeś zapytanie, które podałem wyżej?

[bojak]

[SQL] pobierz, plaintext 
SELECT product_id, SUM(IF(extended=0,stock,0)) AS main, SUM(IF(extended=1,stock,0)) AS extended
FROM product
GROUP BY product_id
HAVING main<>extended OR extended=0
[SQL] pobierz, plaintext 

Jeśli nie jest poprawnie, podaj przykładowy rekord dla głównego bez wariantów.

Niestety też nie do końca działa bo pokazuje wynik zawierający produkty, których produkt główny i wariant tego produktu =0, ale wpadłem na inny pomysł. skoro produkty główne występują zawsze 1 raz a produkty z wariantami więcej niż 1 to po prostu je zliczyłem

[SQL] pobierz, plaintext 
SELECT stock_id,product_id, count(product_id) AS zlicz, IF(extended=0,stock,0) AS main, SUM(IF(extended=1,stock,0)) AS extended
    FROM products_stock
    GROUP BY product_id
    HAVING main<>extended AND zlicz>1
[SQL] pobierz, plaintext 

i działa

Ten post edytował bojak 18.02.2016, 16:08:54

[trueblue]

W ten sposób nie pokaże produktu o id=140, który nie ma wariantów.

[bojak]

O to mi właśnie chodziło - jak pisałem wcześniej. Być może mało treściwie bo mam problem z przekazywaniem informacji - niestety.
Zależy mi, aby z bazy wyciągnąć dane gdzie:

stock where expended=0 <> sum(stock) where expended=1.

Potrzebuję taką listę z product_id do wysłana do pliku na podstawie którego będą usuwane pliki z cache. bez tego zmiany w bazie nie są widoczne w sklepie. Pewnie można to zrobić kodem php, ale ja nie jestem programistą php,html.

na podstawie takiego pobrania danych, będę pisał update żeby suma stanu wariantów nadpisała się na stan magazynowy produktu głównego.

Za nim problem będzie rozwiązany przez sklep shopera mnogą potrwać miesiące a czasem lata, a pilnowanie stanów ręcznie to strata czasu. Jak mam trochę czasu to zawsze wychylam się i wysyłam na formu shopera jakieś rozwiązanie problemu- trochę prowizoryczne, ale działa (IMG:style_emoticons/default/smile.gif)

[trueblue]

[...] wynik nie uwzględnia produktów głównych, które nie mają wariantów - a niestety takie tez bywają.

Nie wiem czy to jest mało treściwie:)
Ale jeśli teraz działa, to ok.

[bojak]

Nie wiem czy to jest mało treściwie:)
Ale jeśli teraz działa, to ok.

No właśnie (IMG:style_emoticons/default/smile.gif) sam wprowadziłem w błąd. dziękuję bardzo za za pomoc i za angażowanie - a w tym chodziło mi że skrypt UWZGLĘDNIA produkty bez wariantów. co innego myślałem, a co innego pisałem - to wszystko przez brak snu 2-5h dziennie

Czy mógł bym prosić o rzucenie okiem w wolnej chwili na zapytanie UPDATE

[SQL] pobierz, plaintext 
UPDATE products_stock m JOIN
(SELECT stock_id,product_id, count(product_id) AS zlicz, IF(extended=0,stock,0) AS main, SUM(IF(extended=1,stock,0)) AS extended
    FROM products_stock
    GROUP BY product_id
    HAVING main<>extended AND zlicz>1 
) e
ON m.stock_id=e.stock_id
SET m.stock=e.extended
[SQL] pobierz, plaintext 

testowałem na kopi i teoretycznie działa poprawnie, ale jak by ktoś mógł rzucić fachowym okiem na poprawność