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

Откуда:
Сообщений: 12
Есть таблица Store:

Сообщение было отредактировано: 15 дек 19, 01:31
14 дек 19, 22:42    [22040519]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос.... Please!!!!!  [new]
Mihail67
Member

Откуда:
Сообщений: 12
Прошу прощения, первый раз на этом форуме и не разобрался с управлением.
Короче, есть таблица (файл 1.jpg)
К ней есть задание: создать запрос который выведет все уникальные названия поставщиков (SUPPLIER) в рамках названий товаров (PRODUCT).
Я составил запрос
select st1.PRODUCT, st1.SUPPLIER
from store as st1
join
(SELECT count(*) as c, PRODUCT
FROM STORE
GROUP BY PRODUCT) AS st2 ON st1.PRODUCT=st2.PRODUCT and st2.c=1
который со слов препода есть правильным (выводит правильный результат) (файл 2.jpg)
но препод потребовал из этого запроса убрать GROUP BY и count(*), составить новый запрос использовав только JOIN.
Вот уже второй день сижу и ничего не могу сделать: ведь GROUP BY и count(*) - агрегатные функции, а JOIN - обработчик условий, ограничивающий результирующий набор.
Кто-то может подсказать, как можно использовать JOIN вместо GROUP BY и count(*)?
Помогите, в понедельник пересдача, а ничего не готово.
Пожалуйста!!!!!

К сообщению приложен файл. Размер - 104Kb


Сообщение было отредактировано: 14 дек 19, 23:28
14 дек 19, 23:22    [22040527]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос.... Please!!!!!  [new]
Mihail67
Member

Откуда:
Сообщений: 12
Не прогрузился второй скрин (результат запроса)
Вот он

К сообщению приложен файл. Размер - 36Kb
14 дек 19, 23:24    [22040528]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос.... Please!!!!!  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Mihail67
К ней есть задание: создать запрос который выведет все уникальные названия поставщиков (SUPPLIER) в рамках названий товаров (PRODUCT).
Почему не просто:
select distinct st1.PRODUCT, st1.SUPPLIER
from store as st1
order by st1.PRODUCT
14 дек 19, 23:37    [22040529]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос.... Please!!!!!  [new]
Mihail67
Member

Откуда:
Сообщений: 12
alexeyvg, Потому, что Ваш запрос выведет все уникальные пары PRODUCT-SUPPLIER (из получилось 56), а нужно только те пары, у которых PRODUCT имеет только один SUPPLIER, а те, у которых PRODUCT имеет 2 и более SUPPLIER нужно игнорировать.
Результирующий набор должен состоять из 29 строк (для данной таблицы из 59 записей)
14 дек 19, 23:48    [22040532]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос.... Please!!!!!  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20527
Mihail67
задание: создать запрос который выведет все уникальные названия поставщиков (SUPPLIER) в рамках названий товаров (PRODUCT).

Mihail67
только те пары, у которых PRODUCT имеет только один SUPPLIER

Это две принципиально разные задачи.
Последняя решается использованием COUNT(SUPPLIER) OVER (PARTITION BY PRODUCT) в CTE.
14 дек 19, 23:53    [22040534]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос.... Please!!!!!  [new]
Mihail67
Member

Откуда:
Сообщений: 12
Akina,
автор
Это две принципиально разные задачи.

но у прерода такое понимание правильного решения 22040528

автор
Последняя решается использованием COUNT(SUPPLIER) OVER (PARTITION BY PRODUCT) в CTE.


Большое спасибо! Результат остался прежним, но уже без использования GROUP BY!!!!!!!!!!!

А без COUNT() здесь уже точно не обойтись?
15 дек 19, 00:26    [22040541]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54185
SELECT PRODUCT, max(supplier)
FROM STORE
GROUP BY PRODUCT
Having count(distinct supplier) =1

Я бы так написал
Хотя преподавателю может не понравиться

А по ресурсам даже не скажу, что лучше, групп бай или сте с аналитикой
15 дек 19, 01:50    [22040554]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Mihail67
Большое спасибо! Результат остался прежним, но уже без использования GROUP BY!!!!!!!!!!!

А без COUNT() здесь уже точно не обойтись?
Можно с EXISTS
select st1.PRODUCT, st1.SUPPLIER
from store as st1
where not exists( 
	select *
	from store st2
	where st1.PRODUCT=st2.PRODUCT 
		and st1.SUPPLIER <> st2.SUPPLIER
)


Но вот как сделать только с JOIN, никак не соображу...
15 дек 19, 09:17    [22040578]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54185
alexeyvg
Mihail67
Большое спасибо! Результат остался прежним, но уже без использования GROUP BY!!!!!!!!!!!

А без COUNT() здесь уже точно не обойтись?
Можно с EXISTS
select st1.PRODUCT, st1.SUPPLIER
from store as st1
where not exists( 
	select *
	from store st2
	where st1.PRODUCT=st2.PRODUCT 
		and st1.SUPPLIER <> st2.SUPPLIER
)



Но вот как сделать только с JOIN, никак не соображу...
без группировки или аналитики тоже не вижу решения
15 дек 19, 09:47    [22040581]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
court
Member

Откуда:
Сообщений: 2016
без COUNT и с JOIN-ом :))

;with cte as (
    select *, min(SUPPLIER) OVER (PARTITION BY PRODUCT) as xz1, max(SUPPLIER) OVER (PARTITION BY PRODUCT) as xz2  from store )

select t1.*
from cte t1 inner join cte t2 on t1.xz1=t2.xz2 and t1.PRODUCT=t2.PRODUCT


Сообщение было отредактировано: 15 дек 19, 10:39
15 дек 19, 10:38    [22040585]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
Mihail67
а нужно только те пары, у которых PRODUCT имеет только один SUPPLIER, а те, у которых PRODUCT имеет 2 и более SUPPLIER нужно игнорировать.
select distinct
 a.PRODUCT, a.SUPPLIER
from
 store a left join
 store b on b.PRODUCT = a.PRODUCT and b.SUPPLIER <> a.SUPPLIER
where
 b.PRODUCT is null;
15 дек 19, 10:54    [22040588]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
court
Member

Откуда:
Сообщений: 2016
invm
Mihail67
а нужно только те пары, у которых PRODUCT имеет только один SUPPLIER, а те, у которых PRODUCT имеет 2 и более SUPPLIER нужно игнорировать.
select distinct
 a.PRODUCT, a.SUPPLIER
from
 store a left join
 store b on b.PRODUCT = a.PRODUCT and b.SUPPLIER <> a.SUPPLIER
where
 b.PRODUCT is null;

Эквивалентно
select distinct
 a.PRODUCT, a.SUPPLIER
from
 store a

имхо

т.е. условие "у которых PRODUCT имеет 2 и более SUPPLIER нужно игнорировать" не выполнится ...
15 дек 19, 11:06    [22040591]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54185
court
без COUNT и с JOIN-ом :))

;with cte as (
    select *, min(SUPPLIER) OVER (PARTITION BY PRODUCT) as xz1, max(SUPPLIER) OVER (PARTITION BY PRODUCT) as xz2  from store )

select t1.*
from cte t1 inner join cte t2 on t1.xz1=t2.xz2 and t1.PRODUCT=t2.PRODUCT
всё равно аналитика
15 дек 19, 11:15    [22040593]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9347
court
Эквивалентно
select distinct
 a.PRODUCT, a.SUPPLIER
from
 store a
declare @t table (g int, v int);
insert into @t
values
 (1, 1), (1, 1), (2, 1), (2, 2);

select distinct
 g, v
from
 @t;

select distinct
 a.g, a.v
from
 @t a left join
 @t b on b.g = a.g and b.v <> a.v
where
 b.g is null;

select g, v from @t

except

select
 a.g, a.v
from
 @t a join
 @t b on b.g = a.g and b.v <> a.v;
15 дек 19, 11:20    [22040595]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
Mihail67
Member

Откуда:
Сообщений: 12
andreymx, спасибо, это явно не подходит из-за наличия GROUP BY и HAVING
15 дек 19, 11:40    [22040599]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
Mihail67
Member

Откуда:
Сообщений: 12
court, Спасибо, но Ваш запрос пропустил одну деталь: в базе есть записи с идентичными данными в PRODUCT и SUPPLIER соответственно. То-есть, мало того, что они повторяются, они еще и раны между собой (пары [PRODUCT;SUPPLIER]).
Ваш запрос это не учитывает и удваивает эти строки (которых вовсе не должно быть в выборке).
+

Картинка с другого сайта.
15 дек 19, 12:18    [22040622]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
Mihail67
Member

Откуда:
Сообщений: 12
andreymx, Спасибо, но Ваш запрос пропустил одну деталь: в базе есть записи с идентичными данными в PRODUCT и SUPPLIER соответственно. То-есть, мало того, что они повторяются, они еще и раны между собой (пары [PRODUCT;SUPPLIER]).
Ваш запрос это не учитывает и выводит эти строки (которых вовсе не должно быть в выборке).
+

Картинка с другого сайта.
15 дек 19, 12:23    [22040624]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
Mihail67
Member

Откуда:
Сообщений: 12
Mihail67, Извините за наглость (Вы и так мне очень помогли), но не могли-бы Вы еще подсказать, как заменить запрос
SELECT ID_STUFF,STAFF_NAME, E_MAIL
FROM [sale].[dbo].[INVOICE]
GROUP BY ID_STUFF, STAFF_NAME, E_MAIL

через COUNT(...) OVER (PARTITION BY ....)

Задание аналогичное, с разницей: триада ID_STUFF, STAFF_NAME, E_MAIL встечается только один раз (а этим запросом я считаю сколько раз она встретилась, затем Join обрабатываю результирующий набор)

Спасибо.

Сообщение было отредактировано: 15 дек 19, 12:33
15 дек 19, 12:28    [22040630]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
Mihail67
Member

Откуда:
Сообщений: 12
Akina, Извините за наглость (Вы и так мне очень помогли), но не могли-бы Вы еще подсказать?
Задание аналогичное, с разницей: триада ID_STUFF, STAFF_NAME, E_MAIL встечается только один раз.
Этим запросом я считаю сколько раз она встретилась, затем Join обрабатываю результирующий набор.

select count(*) as count_STUFF, ID_STUFF
FROM
(SELECT ID_STUFF,STAFF_NAME, E_MAIL
FROM [sale].[dbo].[INVOICE]
GROUP BY ID_STUFF, STAFF_NAME, E_MAIL) As Inv
group by Inv.ID_STUFF

Этим запросом я получаю выборку с количеством уникальных триад ID_STUFF, STAFF_NAME, E_MAIL
+

Картинка с другого сайта.


Помогите записать мой запрос через COUNT(...) OVER (PARTITION BY ....)

Спасибо.
15 дек 19, 12:47    [22040635]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
Mihail67
Member

Откуда:
Сообщений: 12
Mihail67

Помогите записать мой запрос через COUNT(...) OVER (PARTITION BY ....)


Проблема решена:
Создал представление, формирующее результирующий набор из ID_STUFF и количества различных E_MAIL
Create view Stuff1
as
select COUNT(E_MAIL) OVER (PARTITION BY ID_STUFF) as count_EMAIL, ID_STUFF
from
(SELECT DISTINCT(E_MAIL), ID_STUFF, STAFF_NAME
FROM INVOICE) As inv
+
Первая таблица - внутренний SELECT, вторая - основной
Картинка с другого сайта.


Через JOIN обьеденил две таблицы в итоговый результат
select inv1.*
from INVOICE as inv1
join
(SELECT * FROM Stuff1 ) AS inv2
ON inv1.ID_STUFF=inv2.ID_STUFF and inv2.count_EMAIL=1
+

Картинка с другого сайта.


Результат идентичен решению с использованием GROUP BY.
Надеюсь, у препода не возникнет вопросов к наличию COUNT() в запросах.
Посему, тему можно считать закрытой.

Спасибо всем!
15 дек 19, 17:14    [22040713]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Mihail67
Надеюсь, у препода не возникнет вопросов к наличию COUNT() в запросах.
Так с EXISTS почему не подошло?
А то тут у вас COUNT, вдруг аналитические функции тоже нельзя, как и GROUP BY
15 дек 19, 18:10    [22040739]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
court
Member

Откуда:
Сообщений: 2016
invm
court
Эквивалентно
select distinct
 a.PRODUCT, a.SUPPLIER
from
 store a

declare @t table (g int, v int);
insert into @t
values
 (1, 1), (1, 1), (2, 1), (2, 2);

select distinct
 g, v
from
 @t;

select distinct
 a.g, a.v
from
 @t a left join
 @t b on b.g = a.g and b.v <> a.v
where
 b.g is null;

select g, v from @t

except

select
 a.g, a.v
from
 @t a join
 @t b on b.g = a.g and b.v <> a.v;
да, ерунду сморозил, сори :)
16 дек 19, 10:52    [22040927]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
Mihail67
Member

Откуда:
Сообщений: 12
alexeyvg
Так с EXISTS почему не подошло?


Ваш запрос пропустил одну деталь: в базе есть записи с идентичными данными в PRODUCT и SUPPLIER соответственно. То-есть, мало того, что они повторяются, они еще и раны между собой (пары [PRODUCT;SUPPLIER]).
Ваш запрос это не учитывает и выводит эти строки (которых вовсе не должно быть в выборке).
+

Картинка с другого сайта.


alexeyvg

А то тут у вас COUNT, вдруг аналитические функции тоже нельзя, как и GROUP BY


Можно или нельзя - вторично по сравнению с правильная выборка или неправильная.
Как это сделать без COUNT (и чтоб выборка была правильной) я не знаю, и здесь никто ничего не предложил.
Поэтому ....
16 дек 19, 19:00    [22041514]     Ответить | Цитировать Сообщить модератору
 Re: Помогите изменить SQL-запрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
Mihail67
alexeyvg
Так с EXISTS почему не подошло?


Ваш запрос пропустил одну деталь: в базе есть записи с идентичными данными в PRODUCT и SUPPLIER соответственно. То-есть, мало того, что они повторяются, они еще и раны между собой (пары [PRODUCT;SUPPLIER]).
Ваш запрос это не учитывает и выводит эти строки (которых вовсе не должно быть в выборке).
А, понятно.
Я исходил из того, что PRODUCT,SUPPLIER - уникальны.
Тогда да, такую задачу с exists не решить, если в таблице нету PK
Вообще, очень много нельзя сделать, если в таблице нету PK :-)
16 дек 19, 21:23    [22041597]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить