SQL Functions
TOP
❮ ❯

SQL HAVING

SQL HAVING 説明

WHERE キーワードは集計関数では使用できないため、HAVING 式が SQL に追加されました。

SQL HAVING は、GROUP BY ステートメントによって作成されたグループによって値をフィルター処理するために使用されます。


HAVING 構文

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s)

デモデータベース

以下は、「Northwind」データベースの「Customers」(「Customers」) テーブルのサンプルです。

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.5021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.5023Mexico
4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

SQL HAVING の例

次の SQL クエリは、各国の顧客数をリストします。顧客数が 5 を超える国のみが含まれます。

Run SQLSELECT COUNT(CustomerID), Country 
FROM Customers 
GROUP BY Country 
HAVING COUNT(CustomerID) > 5

次の SQL クエリは、各国の顧客数を最大から最小の順に並べてリストします (顧客数が 5 人を超える国のみを含みます)。

Run SQLSELECT COUNT(CustomerID), Country 
FROM Customers 
GROUP BY Country 
HAVING COUNT(CustomerID) > 5 
ORDER BY COUNT(CustomerID) DESC

デモデータベース

テーブル "Orders" ("Orders") のサンプルを見てみましょう。

ProductIDOrderIDCustomerIDEmployeeIDOrderDateShipperID
1102489051996-07-043
2102498161996-07-051
3102503441996-07-082
4102518431996-07-081
5102527641996-07-092

テーブル「Employees」(「従業員」) のサンプル:

EmployeeIDLastNameFirstNameBirthDatePhotoNotes
1Davolio Nancy 12/8/1968 EmpID1.pic Education includes a BA in psychology from Colorado State University. She also completed (The Art of the Cold Call). Nancy is a member of 'Toastmasters International'.
2Fuller Andrew 2/19/1952 EmpID2.pic Andrew received his BTS commercial and a Ph.D. in international marketing from the University of Dallas. He is fluent in French and Italian and reads German. He joined the company as a sales representative, was promoted to sales manager and was then named vice president of sales. Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.
3Leverling Janet 8/30/1963 EmpID3.pic Janet has a BS degree in chemistry from Boston College). She has also completed a certificate program in food retailing management. Janet was hired as a sales associate and was promoted to sales representative.
4Peacock Margaret 9/19/1958 EmpID4.pic Margaret holds a BA in English literature from Concordia College and an MA from the American Institute of Culinary Arts. She was temporarily assigned to the London office before returning to her permanent post in Seattle.
5Buchanan Steven 3/4/1955 EmpID5.pic Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree. Upon joining the company as a sales representative, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London, where he was promoted to sales manager. Mr. Buchanan has completed the courses 'Successful Telemarketing' and 'International Sales Management'. He is fluent in French.

その他の HAVING の例

次の SQL クエリは、10 件を超える注文を登録した従業員をリストします。

Run SQLSELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders 
FROM (Orders 
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID) 
GROUP BY LastName 
HAVING COUNT(Orders.OrderID) > 10

次の SQL ステートメントは、「Davolio」または「Fuller」の従業員が 25 を超える注文を登録した場合のデータを表示します。

Run SQLSELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders 
FROM Orders 
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID 
WHERE LastName = 'Davolio' OR LastName = 'Fuller' 
GROUP BY LastName 
HAVING COUNT(Orders.OrderID) > 25
❮ Previous Next ❯