1-laboratoriya topshirig’i

5-savol. Har bir mamlakat uchun musiqaga eng ko‘p pul sarflagan mijozni aniqlaydigan so‘rov yozing. Eng yaxshi mijoz bilan birga mamlakatni va qancha pul sarflaganligini chiqaradigan so‘rov yozing. Agar eng ko‘p miqdorda pul sarflagan mijoz ko‘p bo‘lsa, ularning barchasini chiqaring.
Izoh: Savolni tahlil qilaylik. Har bir mamlakat uchun eng ko’p pul sarflagan mijozni aniqlash lozim. Bunda 4-savolga o’xshash tarzda eng ko’p pul sarflagan mijoz so’ralyapti, undan farqli ravishda agar mijozlar ko’p bo’lsa ularning hammasini chiqarishimiz kerak. Bunda bir necha jadvaldan foydalanganimiz uchun WITH buyrug’idan foydalanamiz. SELECT dan so’ng esa so’ralgan ma’lumotlarni qaytaramiz, ya’ni bular mijozlarning ism-shariflaridir. Bir vaqtning o’zida Customer va Invoice jadvallaridan foydalanganimiz uchun JOIN buyrug’ini ishlatamiz. Country bo’yicha guruhlaymiz hamda ORDER BY orqali tartiblaymiz. Shularni inobatga olgan holda SQL so’rovi quyidagicha bo’ladi:
1-yechim:
WITH tab1 AS
(
SELECT c.CustomerId, c.FirstName, c.LastName, c.Country, SUM(i.Total) TotalSpent
FROM Customer c
JOIN Invoice i ON c.CustomerId = i.CustomerId
GROUP BY c.CustomerId
)
SELECT tab1.*
FROM tab1
JOIN
(
SELECT CustomerId, FirstName, LastName, Country, MAX(TotalSpent) AS TotalSpent
FROM tab1
GROUP BY Country
) tab2
ON tab1.Country = tab2.Country
WHERE tab1.TotalSpent = tab2.TotalSpent
ORDER BY Country;
2-yechim:
WITH t1 AS
(
SELECT Customer.Country, SUM(Invoice.Total) TotalSpent, Customer.FirstName,
Customer.LastName, Customer.CustomerId
FROM Customer
JOIN Invoice ON Customer.CustomerId = Invoice.CustomerId
GROUP BY Customer.CustomerId
)

SELECT t1.CustomerId, t1.FirstName, t1.LastName, t1.Country, t1.TotalSpent

FROM t1
INNER JOIN (
SELECT t1.Country, MAX(t1.TotalSpent) as max_ts FROM t1 GROUP BY Country) AS t2
ON t1.Country=t2.Country
WHERE t1.TotalSpent=t2.max_ts
ORDER BY t1.Country