Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Посоветуйте правильно написать запрос  [new]
Sergei3004
Member

Откуда:
Сообщений: 8
 		Привет, ребята !

Посоветуйте правильно написать запрос SQL.

Входные данные следующие:

There are two tables Countries and Cities.

Countries:
country_id
name

Cities:
city_id
name
country_id
population

Each city is applied to country as many to one. And not all countries has cities.

Необходимо:
1. Return population for countries (zero if country doesn't have cities).
2. Return country name where amount of cities between 3 and 6

Заранее благодарен !!!! )
13 ноя 16, 12:58    [19887713]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
aleks2
Guest 		Я те выдам страшную тайну успешной сдачи этого теста:

1. Сначала группируешь Cities.
2. Потом присобачиваешь join-ом Countries.

Тогда сдашь.
13 ноя 16, 13:04    [19887721]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Sergei3004
Member

Откуда:
Сообщений: 8
 		aleks2,
спасибо )
готовое решение возможно проверить ?
13 ноя 16, 13:35    [19887755]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Sergei3004
Member

Откуда:
Сообщений: 8
 		Выкладываю все задание и решение.

Проверьте, пожалуйста, на корректность исполнения запросов.

Задания не сложные, но я новичок в SQL. Просьба помочь )))

There are two tables Countries and Cities.

Countries:
country_id
name

Cities:
city_id
name
country_id
population

Each city is applied to country as many to one. And not all countries has cities.

Tasks:


1. Return all unique cities

Решение:

SELECT DISTINCT Name
FROM Cities
ORDER BY Name;

2. Return names and country_id for countries with “rus” in name insensitively (so «Russia» and «Belarus» will be in result)

SELECT Name, Country_id
FROM Countries
WHERE Name LIKE '%rus%'
ORDER BY Country_id;


3. Return population for countries (zero if country doesn't have cities).

здесь не знаю (

4. Return country name where amount of cities between 3 and 6

SELECT Name
FROM Countries
WHERE EXISTS
(SELECT Count(Name) FROM Cities WHERE Count(Name) BETWEEN 3 and 6);


Прошу поправить если, что не так. Спасибо )
13 ноя 16, 14:32    [19887888]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
aleks2
Guest
Sergei3004
3. Return population for countries (zero if country doesn't have cities).

здесь не знаю ( 


with x as ( select country_id, population = SUM(population) from Cities group by country_id )
  select cn.*, population = isnull(x.population, 0)
    from Countries as cn left outer join x on x.country_id = cn.country_id


4. Хреново. Низачот.
13 ноя 16, 17:30    [19888266]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Sergei3004
Member

Откуда:
Сообщений: 8
 		aleks2,
Спасибо большое, Алекс )))

А что с п.4 не так ? Прошу помощи ))
13 ноя 16, 18:40    [19888381]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
_human
Member

Откуда:
Сообщений: 566
Sergei3004
А что с п.4

JOIN

GROUP BY
HAVING
D. Use a GROUP BY clause with a HAVING clause
13 ноя 16, 18:59    [19888409]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
aleks2
Guest
_human
Sergei3004
А что с п.4

JOIN

GROUP BY
HAVING
D. Use a GROUP BY clause with a HAVING clause

Тоже низачот.

Sergei3004
4. Return country name where amount of cities between 3 and 6

SELECT Name
FROM Countries
WHERE EXISTS
(SELECT Count(Name) FROM Cities WHERE Count(Name) BETWEEN 3 and 6);


Прошу поправить если, что не так. Спасибо )


У тя подзапрос в EXISTS ваще не связан с Countries. Он считает ваще одно и то же.
14 ноя 16, 05:49    [19889346]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Massa52
Member

Откуда:
Сообщений: 382
 
declare @Countries table (country_id int primary key, name nvarchar(max));
declare @Cities table (city_id int primary key, name nvarchar(max), country_id int, [population] int);

insert into @Countries values (1, N'US'), (2, N'BZ'), (3, N'CZ'),(4, N'AU');
insert into @Cities values (1, N'V', 1, 3), (2, N'L',1,10), (3, N'A',1, 1), (4, N'B',1, 2),(5, N'R',2,15),(6, N'S',2,2);

SELECT Name
FROM @Countries
WHERE EXISTS 
(SELECT Count(Name) FROM @Cities WHERE Count(Name) BETWEEN 3 and 6);



Error:Msg 147, Level 15, State 1, Line 10
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
14 ноя 16, 06:39    [19889354]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Massa52
Member

Откуда:
Сообщений: 382
 		Начни с

select country_id, count(*) cn 
from @Cities group by country_id
14 ноя 16, 06:44    [19889355]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Sergei3004
Member

Откуда:
Сообщений: 8
 		aleks2,

Привет

столько мнений по п.4 ...

Алекс, напиши, пожалуйста, как по твоему мнению реализовать данный запрос.

Спасибо большое ! )
14 ноя 16, 13:22    [19890922]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Massa52
Member

Откуда:
Сообщений: 382
 
declare @Countries table (country_id int primary key, name nvarchar(max));
declare @Cities table (city_id int primary key, name nvarchar(max), country_id int, [population] int);

insert into @Countries values (1, N'US'), (2, N'BZ'), (3, N'CZ'),(4, N'AU');
insert into @Cities values (1, N'V', 1, 3), (2, N'L',1,10), (3, N'A',1, 1), (4, N'B',1, 2),(5, N'R',2,15),(6, N'S',2,2);

with x as
(select country_id, count(*) cn 
from @Cities group by country_id
)
select name from @Countries c inner join x on c.country_id=x.country_id
where x.cn between 3 and 6;
--or
select name from @Countries c inner join (select country_id, count(*) cn 
from @Cities group by country_id) x on c.country_id=x.country_id
where x.cn between 3 and 6;
14 ноя 16, 13:53    [19891160]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Sergei3004
Member

Откуда:
Сообщений: 8
 		Massa52,

Спасибо за решение )

Буду разбираться :-)
14 ноя 16, 14:00    [19891200]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
o-o
Guest
Sergei3004
Massa52,

Спасибо за решение )

Буду разбираться :-)

да блин, они в 4-ом хотят HAVING,
а вы его обходите за версту,
так ТС и не узнает о нем.

вот отсюда надо плясать:
SELECT country_id
FROM @Cities
group by country_id
having Count(Name) BETWEEN 3 and 6
14 ноя 16, 14:07    [19891232]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Massa52
Member

Откуда:
Сообщений: 382
 		o-o,
Однако - слона то я и не приметил. Действительно - можно же через HAVING фильтровать, когда все сруппировано.
14 ноя 16, 14:50    [19891531]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
aleks2
Guest 		Этакая пирдуха. Надо ж поучаствовать...

SELECT Name FROM Countries as cn WHERE ( SELECT сount(*) FROM ( select top(6) * from Cities WHERE country_id = cn.country_id  ) as x ) BETWEEN 3 and 6;
14 ноя 16, 15:11    [19891624]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Massa52
Member

Откуда:
Сообщений: 382
 
select name from @Countries
where country_id in (SELECT country_id
FROM @Cities
group by country_id
having Count(name) BETWEEN 5 and 6)
14 ноя 16, 15:44    [19891895]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
serpentariy
Member

Откуда:
Сообщений: 265
aleks2
Этакая пирдуха. Надо ж поучаствовать...

SELECT Name FROM Countries as cn WHERE ( SELECT сount(*) FROM ( select top(6) * from Cities WHERE country_id = cn.country_id  ) as x ) BETWEEN 3 and 6;
Тогда уж SELECT TOP(7)
14 ноя 16, 15:59    [19891972]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
o-o
Guest
aleks2
Этакая пирдуха. Надо ж поучаствовать...

SELECT Name FROM Countries as cn WHERE ( SELECT сount(*) FROM ( select top(6) * from Cities WHERE country_id = cn.country_id  ) as x ) BETWEEN 3 and 6;

А это точно Пир Духа?
Может, пир без духа точнее выразит суть происходящего?
14 ноя 16, 16:15    [19892085]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
aleks2
Guest
o-o
aleks2
Этакая пирдуха. Надо ж поучаствовать...

SELECT Name FROM Countries as cn WHERE ( SELECT сount(*) FROM ( select top(6) * from Cities WHERE country_id = cn.country_id  ) as x ) BETWEEN 3 and 6;

А это точно Пир Духа?
Может, пир без духа точнее выразит суть происходящего?


Шо за гнусные намеки?

ЗЫ. Нащет top(7) - верно.
14 ноя 16, 18:08    [19892749]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Massa52
Member

Откуда:
Сообщений: 382
 		top - лишняя, так как данная операция идет после построения набора данных. Или это только при сортировке?
* - достаточно одного поля, например, city_id
15 ноя 16, 02:16    [19893810]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
aleks2
Guest
Massa52
top - лишняя, так как данная операция идет после построения набора данных. Или это только при сортировке?
* - достаточно одного поля, например, city_id


1. Пирдуха без top не получается.
2. Замена * на city_id бесполезна, ибо ничего не дает.
15 ноя 16, 08:39    [19894000]     Ответить | Цитировать Сообщить модератору
 Re: Посоветуйте правильно написать запрос  [new]
Sergei3004
Member

Откуда:
Сообщений: 8
 		Спасибо ребята за помощь )))
15 ноя 16, 23:04    [19898145]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить