MySQL HAVING

MySQL HAVING bandi

HAVING bandi GROUP BY so‘rovi natijalarini agregat funksiyalar asosida filtrlash uchun ishlatiladi.

Guruhlashdan oldin alohida satrlarni filtrlaydigan WHERE bandidan farqli o‘laroq, HAVING bandi yig‘ish amalga oshirilgandan so‘ng guruhlarni filtrlaydi.

Sintaksisga ega

SELECT column1, aggregate_function(column2), column3, ...
FROM table_name
WHERE condition
GROUP BY column1, column3
HAVING condition -- The condition on grouped data
ORDER BY column_name;

Demo ma’lumotlar bazasi

Quyida Northwind namunasidagi "Mijozlar" jadvalidan tanlov mavjud ma’lumotlar bazasi:

Mijoz ID	Mijoz nomi	Kontakt nomi	Manzil	Shahar	Pochta kodi	Mamlakat
1	Alfreds Futterkiste	Mariya Anders	Obere ko‘chasi. 57	Berlin	12209	Germaniya
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Konstitutsiya 2222	Meksika D.F.	05021	Meksika
3	Antonio Moreno Taqueria	Antonio Moreno	Mataderos 2312	Meksika D.F.	05023	Meksika
4	Shox atrofida	Tomas Xardi	120 Gannover kv.	London	WA1 1DP	UK
5	Berglunds snabbköp	Kristina Berglund	Berguvsvägen 8	Luleå	S-958 22	Shvetsiya

MySQL HAVING misollar

Quyidagi SQL har bir mamlakatdagi mijozlar sonini qaytaradi - lekin faqat 5 dan ortiq mijozlari bo‘lgan mamlakatlarni o‘z ichiga oladi:

Misol

SELECT Country, COUNT(CustomerID) AS "Number of Customers"
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

O‘zingiz sinab ko‘ring »

Quyidagi SQL har bir mamlakatdagi mijozlar sonini yuqoridan pastgacha saralaydi (va faqat 5 dan ortiq mijozlari bo‘lgan mamlakatlarni o‘z ichiga oladi):

Misol

SELECT Country, COUNT(CustomerID) AS "Number of Customers"
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

O‘zingiz sinab ko‘ring »

Demo ma’lumotlar bazasi

Quyida Northwind namunaviy ma’lumotlar bazasidagi "Orders" jadvalidan tanlov keltirilgan:

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10248	90	5	1996-07-04	3
10249	81	6	1996-07-05	1
10250	34	4	1996-07-08	2

Va "Xodimlar" jadvalidan tanlov:

EmployeeID	LastName	FirstName	BirthDate	Photo	Notes
1	Davolio	Nancy	1968-12-08	EmpID1.pic	Education includes a BA....
2	Fuller	Andrew	1952-02-19	EmpID2.pic	Andrew received his BTS....
3	Leverling	Janet	1963-08-30	EmpID3.pic	Janet has a BS degree....

Ko‘proq misollar

Quyidagi SQL 10 dan ortiq buyurtmani ro‘yxatdan o‘tkazgan xodimlarni qaytaradi:

Misol

SELECT 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;

O‘zingiz sinab ko‘ring »

Quyidagi SQL "Davolio" yoki "Fuller" xodimlari 25 dan ortiq buyurtmani ro‘yxatdan o‘tkazgan-o‘tkazmaganini qaytaradi:

Misol

SELECT 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;

O‘zingiz sinab ko‘ring »

W3Schools Pathfinder

Yutuqlaringizni kuzating – bu bepul!

Ro'yxatdan o'tish Kirish