SQL HAVING bandi

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

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

HAVING sintaksisi

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 namunaviy ma’lumotlar bazasidagi "Customers" jadvalidan tanlanma keltirilgan:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

SQL HAVING misollari

Quyidagi SQL har bir mamlakatdagi mijozlar sonini qaytaradi - lekin faqat 5 tadan ortiq mijozi bor 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 kattadan kichikka qarab saralangan holda qaytaradi (va faqat 5 tadan ortiq mijozi bor 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 tanlanma 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 tanlanma "Employees" jadvali:

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....

Boshqa HAVING misollari

Quyidagi SQL 10 dan ortiq Orders ro‘yxatdan o‘tgan Employees ni 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 »

Agar Employees "Davolio" yoki "Fuller" 25 dan ortiq Orders ro‘yxatdan o‘tgan bo‘lsa, quyidagi SQL qaytariladi:

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