2.できる
14301 ワード
1.JOIN(INNERJOIN)-内部連結は、両方とも値を持つ行(NOT NULL) を返す.「INNER」はオプションの の他端にデータ(NULL)があるか否かにかかわらず、選択方向において出力-行数 を決定する.「OUTER」はオプションの 無条件にすべての組合せ(A*B) を返す
SELECT * FROM Categories C
JOIN Products P
ON C.CategoryID = P.CategoryID;
SELECT C.CategoryID, C.CategoryName, P.ProductName
FROM Categories C
JOIN Products P
ON C.CategoryID = P.CategoryID;
-- ambiguous 주의!
SELECT
CONCAT(
P.ProductName, ' by ', S.SupplierName
) AS Product,
S.Phone, P.Price
FROM Products P
JOIN Suppliers S
ON P.SupplierID = S.SupplierID
WHERE Price > 50
ORDER BY ProductName;
💡 JOINはテーブルをたくさん作ることができますSELECT
C.CategoryID, C.CategoryName,
P.ProductName,
O.OrderDate,
D.Quantity
FROM Categories C
JOIN Products P
ON C.CategoryID = P.CategoryID
JOIN OrderDetails D
ON P.ProductID = D.ProductID
JOIN Orders O
ON O.OrderID = D.OrderID;
💡 JOINテーブルGROUPSELECT
C.CategoryName,
MIN(O.OrderDate) AS FirstOrder,
MAX(O.OrderDate) AS LastOrder,
SUM(D.Quantity) AS TotalQuantity
FROM Categories C
JOIN Products P
ON C.CategoryID = P.CategoryID
JOIN OrderDetails D
ON P.ProductID = D.ProductID
JOIN Orders O
ON O.OrderID = D.OrderID
GROUP BY C.CategoryID;
SELECT
C.CategoryName, P.ProductName,
MIN(O.OrderDate) AS FirstOrder,
MAX(O.OrderDate) AS LastOrder,
SUM(D.Quantity) AS TotalQuantity
FROM Categories C
JOIN Products P
ON C.CategoryID = P.CategoryID
JOIN OrderDetails D
ON P.ProductID = D.ProductID
JOIN Orders O
ON O.OrderID = D.OrderID
GROUP BY C.CategoryID, P.ProductID;
💡 SELF JOIN-同一テープSELECT
E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee,
E2.EmployeeID, CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee
FROM Employees E1 JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID;
-- 1번의 전, 마지막 번호의 다음은?
2.LEFT/RIGHT OUTER JOIN-外部接続SELECT
E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee,
E2.EmployeeID, CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee
FROM Employees E1
LEFT JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID
ORDER BY E1.EmployeeID;
-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것
SELECT
C.CustomerName, S.SupplierName,
C.City, C.Country
FROM Customers C
LEFT JOIN Suppliers S
ON C.City = S.City AND C.Country = S.Country;
-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것
SELECT
IFNULL(C.CustomerName, '-- NO CUSTOMER --'),
IFNULL(S.SupplierName, '-- NO SUPPLIER --'),
IFNULL(C.City, S.City),
IFNULL(C.Country, S.Country)
FROM Customers C
LEFT JOIN Suppliers S
ON C.City = S.City AND C.Country = S.Country;
-- LEFT를 RIGHT로 바꿔서도 실행해 볼 것
3.CROSS JOIN-クロスジョイントSELECT
E1.LastName, E2.FirstName
FROM Employees E1
CROSS JOIN Employees E2
ORDER BY E1.EmployeeID;
Reference
この問題について(2.できる), 我々は、より多くの情報をここで見つけました https://velog.io/@sshin/MySQL-2-2テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol