2-1.ᄏᄏᄏᄏᄏᄏᄏᄏᄏᄏᄏᄏ
10146 ワード
1.非常関所サブクエリ
SELECT
CategoryID, CategoryName, Description,
(SELECT ProductName FROM Products WHERE ProductID = 1)
FROM Categories;
SELECT * FROM Products
WHERE Price < (
SELECT AVG(Price) FROM Products
);
SELECT
CategoryID, CategoryName, Description
FROM Categories
WHERE
CategoryID =
(SELECT CategoryID FROM Products
WHERE ProductName = 'Chais');
SELECT
CategoryID, CategoryName, Description
FROM Categories
WHERE
CategoryID IN
(SELECT CategoryID FROM Products
WHERE Price > 50);
演算子の未~ALLサブクエリのすべての結果~ANYサブクエリの1つ以上の結果~~SELECT * FROM Products
WHERE Price > ALL (
SELECT Price FROM Products
WHERE CategoryID = 2
);
SELECT
CategoryID, CategoryName, Description
FROM Categories
WHERE
CategoryID = ANY
(SELECT CategoryID FROM Products
WHERE Price > 50);
2.関連サブクエリSELECT
ProductID, ProductName,
(
SELECT CategoryName FROM Categories C
WHERE C.CategoryID = P.CategoryID
) AS CategoryName
FROM Products P;
SELECT
SupplierName, Country, City,
(
SELECT COUNT(*) FROM Customers C
WHERE C.Country = S.Country
) AS CustomersInTheCountry,
(
SELECT COUNT(*) FROM Customers C
WHERE C.Country = S.Country
AND C.City = S.City
) AS CustomersInTheCity
FROM Suppliers S;
SELECT
CategoryID, CategoryName,
(
SELECT MAX(Price) FROM Products P
WHERE P.CategoryID = C.CategoryID
) AS MaximumPrice,
(
SELECT AVG(Price) FROM Products P
WHERE P.CategoryID = C.CategoryID
) AS AveragePrice
FROM Categories C;
SELECT
ProductID, ProductName, CategoryID, Price
-- ,(SELECT AVG(Price) FROM Products P2
-- WHERE P2.CategoryID = P1.CategoryID)
FROM Products P1
WHERE Price < (
SELECT AVG(Price) FROM Products P2
WHERE P2.CategoryID = P1.CategoryID
);
EXISTS/NOT EXISTS演算子SELECT
CategoryID, CategoryName
-- ,(SELECT MAX(P.Price) FROM Products P
-- WHERE P.CategoryID = C.CategoryID
-- ) AS MaxPrice
FROM Categories C
WHERE EXISTS (
SELECT * FROM Products P
WHERE P.CategoryID = C.CategoryID
AND P.Price > 80
);
Reference
この問題について(2-1.ᄏᄏᄏᄏᄏᄏᄏᄏᄏᄏᄏᄏ), 我々は、より多くの情報をここで見つけました https://velog.io/@sshin/MySQL-2-1テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol