Próbuję wykonać ten kod: SELECT ItName, ItCode, Qty, Qt2, Qt3 FROM (SELECT A.ItName, A.ItCode, COUNT(B.Qty) AS 'Ilość 1', COUNT(B.Qty2) AS' Ilość 2', COUNT(B.Ilość3) JAKO 'Ilość 3'...

0
Iseca 24 czerwiec 2021, 11:21

4 odpowiedzi

Najlepsza odpowiedź

W przypadku trzech różnych A.ItCode nie potrzebujesz trzech różnych zapytań wybierających, aby połączyć je z sumą wszystkich, zamiast tego możesz użyć klauzuli in, aby uzyskać trzy różne produkty z jednym zapytaniem wybierającym.

SELECT A.ItCode, A.ItName, COUNT(B.Qty) 'Quantity', COUNT(B.Qty2) 'Quantity2', COUNT(B.Qty3) 'Quantity3'
FROM tblitem A
INNER JOIN tblstocksummary B ON A.ItCode = B.ItCode AND A.ItCode in ('IL001-000151','IL001-000373', 'IL001-000166')
GROUP BY A.ItCode 
HAVING 'Quantity' !='0' AND 'Quantity2' !='0' AND 'Quantity3' !='0'
1
Kazi Mohammad Ali Nur 24 czerwiec 2021, 09:07

Ponieważ masz Quantity1 Quantity2 jako kolumny nie Qty Q

Może spróbuj tego

SELECT ItName, ItCode, Qty, Qt2, Qt3
FROM
(
SELECT A.ItName, A.ItCode, COUNT(B.Qty) AS 'Qty', COUNT(B.Qty2) AS 'Qty2', 
COUNT(B.Qty3) AS 'Qty3' 
FROM tblitem A
INNER JOIN tblstocksummary B ON A.ItCode = B.ItCode
WHERE A.Itcode='IL001-000151'
GROUP BY A.ItCode
HAVING COUNT(B.Qty) !='0'AND COUNT(B.Qty2) !='0' AND COUNT(B.Qty3) !='0'

UNION

SELECT A2.ItName, A2.ItCode, COUNT(B2.Qty), COUNT(B2.Qty2), COUNT(B2.Qty3) FROM tblitem A2
INNER JOIN tblstocksummary B2 ON A2.ItCode = B2.ItCode
WHERE A2.Itcode='IL001-000373' 
GROUP BY A2.ItCode
HAVING COUNT(B2.Qty) !='0'AND COUNT(B2.Qty2) !='0' AND COUNT(B2.Qty3) !='0'
) t
UNION ALL

SELECT A3.ItName, A3.ItCode, COUNT(B3.Qty), COUNT(B3.Qty2), COUNT(B3.Qty3) FROM tblitem A3
INNER JOIN tblstocksummary B3 ON A2.ItCode = B3.ItCode
WHERE A3.Itcode='IL001-000166' 
GROUP BY A3.ItCode
HAVING COUNT(B3.Qty) !='0'AND COUNT(B3.Qty2) !='0' AND COUNT(B3.Qty3) !='0';
1
DhruvJoshi 24 czerwiec 2021, 08:25

Masz więcej problemów niż tylko błędne aliasy:

To jest poprawny kod (kod bez błędów):

    SELECT ItName, ItCode, `Quantity 1`, `Quantity 2`, `Quantity 3`
FROM
(
SELECT A.ItName, A.ItCode, COUNT(B.Qty) AS 'Quantity 1', COUNT(B.Qty2) AS 'Quantity 2', 
COUNT(B.Qty3) AS 'Quantity 3' 
FROM tblitem A
INNER JOIN tblstocksummary B ON A.ItCode = B.ItCode
WHERE A.Itcode='IL001-000151'
GROUP BY  A.ItName, A.ItCode
HAVING COUNT(B.Qty) !='0'AND COUNT(B.Qty2) !='0' AND COUNT(B.Qty3) !='0'

UNION

SELECT A2.ItName, A2.ItCode, COUNT(B2.Qty), COUNT(B2.Qty2), COUNT(B2.Qty3) 
FROM tblitem A2
INNER JOIN tblstocksummary B2 ON A2.ItCode = B2.ItCode
WHERE A2.Itcode='IL001-000373' 
GROUP BY A2.ItName, A2.ItCode
HAVING COUNT(B2.Qty) !='0'AND COUNT(B2.Qty2) !='0' AND COUNT(B2.Qty3) !='0'
) t
UNION ALL

SELECT A3.ItName, A3.ItCode, COUNT(B3.Qty), COUNT(B3.Qty2), COUNT(B3.Qty3) FROM tblitem A3
INNER JOIN tblstocksummary B3 ON A3.ItCode = B3.ItCode
WHERE A3.Itcode='IL001-000166' 
GROUP BY A3.ItName, A3.ItCode
HAVING COUNT(B3.Qty) !='0'AND COUNT(B3.Qty2) !='0' AND COUNT(B3.Qty3) !='0';
  • Zamieniłem Qty, Qty2 and Qty3 na 'Quantity 1', 'Quantity 2', 'Quantity 3'
  • Zamieniono również ON A2.ItCode = B3.ItCode na ON A3.ItCode = B3.ItCode, gdzie możesz zobaczyć, że twój alias tabeli to źle dla tego zapytania.
  • Dodałem kolumnę ItName w każdej grupie przez.

Oto moje demo: DEMO

1
VBoka 24 czerwiec 2021, 10:16

Próbowałem tego i zadziałało tak, jak chcę

SELECT A.ItCode 'Item Code', A.ItName 'Item Name', COUNT(B.Qty) 'Quantity', COUNT(B.Qty2) 'Quantity2', COUNT(B.Qty3) 'Quantity3'
FROM tblitem A
INNER JOIN tblstocksummary B ON A.ItCode = B.ItCode AND A.ItCode = 'IL001-000151'
GROUP BY A.ItCode 
HAVING 'Quantity' !='0' AND 'Quantity2' !='0' AND 'Quantity3' !='0'

UNION ALL 

SELECT A.ItCode, A.ItName, COUNT(B.Qty) 'Quantity', COUNT(B.Qty2) 'Quantity2', COUNT(B.Qty3) 'Quantity3'
FROM tblitem A
INNER JOIN tblstocksummary B ON A.ItCode = B.ItCode AND A.ItCode = 'IL001-000373'
GROUP BY A.ItCode 
HAVING 'Quantity' !='0' AND 'Quantity2' !='0' AND 'Quantity3' !='0'

UNION ALL

SELECT A.ItCode, A.ItName, COUNT(B.Qty) 'Quantity', COUNT(B.Qty2) 'Quantity2', COUNT(B.Qty3) 'Quantity3'
FROM tblitem A
INNER JOIN tblstocksummary B ON A.ItCode = B.ItCode AND A.ItCode = 'IL001-000166'
GROUP BY A.ItCode 
HAVING 'Quantity' !='0' AND 'Quantity2' !='0' AND 'Quantity3' !='0'

Ale myślę, że ten kod może być krótszy. Ale nie wiem, jak to skrócić. Jakakolwiek rada ?

0
Iseca 24 czerwiec 2021, 08:55