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

Откуда: Армения, Ереван
Сообщений: 101
имеется таблица со столбцами номер заказа orderid и наименования товара goods

Нужно найти те заказы в которых одновременно есть it1, it3 и it4 товары.

declare @a table (orderid int, goods nvarchar(50))
INSERT INTO @a
VALUES (1,'it1'),(1,'it3'),(1,'it4'),(2,'it1'),(2,'it2'),(2,'it3'),(2,'it4'),(3,'it1'),(3,'it3'),(4,'it3'),(4,'it4')

select * from @a where (goods='it1')
and(orderid in (select orderid from @a where (goods='it3')
and(orderid in (select orderid from @a where (goods='it4')))))

orderid
---
1
2

можно ли тоже самое получить без подзапросов?
9 фев 13, 22:43    [13903058]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
qwerty112
Guest
select orderid from @a
where goods in ('it1','it3','it4')
group by orderid
having count(*)=3
9 фев 13, 23:00    [13903112]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
AVRob
Member

Откуда: Армения, Ереван
Сообщений: 101
спасибо
9 фев 13, 23:19    [13903186]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
AVRob
Member

Откуда: Армения, Ереван
Сообщений: 101
в общем случае у нас

select * from @a where (goods Like 'it1%')
and(orderid in (select orderid from @a where (goods Like 'it3%')
and(orderid in (select orderid from @a where (goods Like 'it4%')))))

тогда

select orderid from @a
where (goods Like 'it1%')or(goods Like 'it3%')or(goods Like 'it4%')
group by orderid
having count(*)=3

или по другому ?
9 фев 13, 23:37    [13903244]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
qwerty112
Guest
AVRob
в общем случае у нас

select * from @a where (goods Like 'it1%')
and(orderid in (select orderid from @a where (goods Like 'it3%')
and(orderid in (select orderid from @a where (goods Like 'it4%')))))

тогда

select orderid from @a
where (goods Like 'it1%')or(goods Like 'it3%')or(goods Like 'it4%')
group by orderid
having count(*)=3 


или по другому ?

в общем - да, - так,
разве что, если вы не "упрощаете", то можно было бы так
select orderid from @a
where left(goods, 3) in ('it1','it3','it4')
group by orderid
having count(*)=3 

но так может - и хуже оказатся ... а может и нет ...
9 фев 13, 23:41    [13903258]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
AVRob
Member

Откуда: Армения, Ереван
Сообщений: 101
qwerty112
select orderid from @a
where left(goods, 3) in ('it1','it3','it4')
group by orderid
having count(*)=3 


Интересная идея, но наверное не подойдет
длины искомых значений goods могут быть различными, и длины значений колонки goods тоже различны.
9 фев 13, 23:48    [13903277]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
declare @t table (p varchar(30));

insert into @t
values
 ('it1%', 'it3%', 'it4%');
 
select orderid from @a
where exists(select * from @t where goods Like p)
group by orderid
having count(distinct goods) = (select count(*) from @t);
Это при условии, что конкретному образцу удовлетворяет только один goods. Если же более одного, то замените в having = на >=.
10 фев 13, 00:04    [13903338]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
declare @a table (orderid int, goods nvarchar(50))
INSERT INTO @a VALUES 
	(1,'it1'),(1,'it3'),(1,'it4'),
	(2,'it1'),(2,'it2'),(2,'it3'),(2,'it4'),
	(3,'it1'),(3,'it2'),(3,'it3'),
	(4,'it1'),(4,'it3'),(4,'it4'),(4,'it4')

select a.orderid
from @a a
group by a.orderid
having count(distinct case when a.goods in ('it1','it3','it4') then a.goods end)=3
10 фев 13, 00:05    [13903344]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Насчет ">=" наврал.
10 фев 13, 00:06    [13903347]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
select a.orderid
from @a a
group by a.orderid
having count(distinct case when a.goods like 'it[134]%' then left(a.goods,3) end)=3
10 фев 13, 00:10    [13903365]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
AVRob
Member

Откуда: Армения, Ереван
Сообщений: 101
Я так и понял ))

Спасибо
10 фев 13, 00:17    [13903396]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
AVRob
Member

Откуда: Армения, Ереван
Сообщений: 101
Cygapb-007
select a.orderid
from @a a
group by a.orderid
having count(distinct case when a.goods like 'it[134]%' then left(a.goods,3) end)=3


тоже интересно, но у нас значения goods не всегда начинаются на it, и длины у них тоже различные
10 фев 13, 00:20    [13903409]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
AVRob
Member

Откуда: Армения, Ереван
Сообщений: 101
Cygapb-007
select a.orderid
from @a a
group by a.orderid
having count(distinct case when a.goods like 'it[134]%' then left(a.goods,3) end)=3


на реальной базе(11 млн строк) этот вариант самый медленный (17 cek), тогда как все остальные выполняются менее чем за секунду,

в чем может быть причина?
10 фев 13, 00:30    [13903437]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
AVRob
у нас значения goods не всегда начинаются на it, и длины у них тоже различные
Так надо сразу говорить, что требуется, а не капризничать, что решение не подходит из-за другой постановки задачи
10 фев 13, 00:32    [13903444]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
AVRob
Member

Откуда: Армения, Ереван
Сообщений: 101
Cygapb-007,

мы просто обсуждаем

спасибо за помощь
10 фев 13, 00:34    [13903450]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
AVRob
Member

Откуда: Армения, Ереван
Сообщений: 101
Cygapb-007,

Я думал эта информация о времени вам будет интересна, и только.

Спасибо еще раз.
10 фев 13, 00:38    [13903466]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
qwerty112
Guest
AVRob
Я думал эта информация о времени вам будет интересна, и только.

AVRob
на реальной базе(11 млн строк) этот вариант самый медленный (17 cek), тогда как все остальные выполняются менее чем за секунду,

в чем может быть причина?

причина в том, что "фильтрация" происходит после группировки (having)
в отличии от этого (ДО группировки)
where (goods Like 'it1%')or(goods Like 'it3%')or(goods Like 'it4%')
10 фев 13, 00:45    [13903487]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
Ennor Tiegael
Member

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

Я думаю, distinct в вашем запросе можно убрать. Он нужен только, если один и тот же товар может проходить дважды разными позициями в одном и том же заказе. Обычно они сразу группируются - разве что для доставки на несколько адресов одновременно. Навскидку только Lands' End вспомнить могу, из тех кто так делает.
10 фев 13, 02:11    [13903675]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
AVRob
имеется таблица со столбцами номер заказа orderid и наименования товара goods

Нужно найти те заказы в которых одновременно есть it1, it3 и it4 товары.

declare @a table (orderid int, goods nvarchar(50))
INSERT INTO @a
VALUES (1,'it1'),(1,'it3'),(1,'it4'),(2,'it1'),(2,'it2'),(2,'it3'),(2,'it4'),(3,'it1'),(3,'it3'),(4,'it3'),(4,'it4')

select * from @a where (goods='it1')
and(orderid in (select orderid from @a where (goods='it3')
and(orderid in (select orderid from @a where (goods='it4')))))

orderid
---
1
2

можно ли тоже самое получить без подзапросов?


А зачем? С подзапросами как раз самый правильный вариант.
10 фев 13, 10:49    [13903955]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
AVRob,

Идеальный запрос —
select *	from orders o
Where exists ( select 1 from order_detail od1  where od1.order_id = o.order_id and od1.goods_id = @goods_id1 )
and exists ( ... )
...


Про аналогии нужно добавить про одному подзапросу на каждый товар из списка.
10 фев 13, 11:05    [13903968]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34705
qwerty112
select orderid from @a
where goods in ('it1','it3','it4')
group by orderid
having count(*)=3


Это неправильный запрос.
10 фев 13, 11:07    [13903970]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
qwerty112
Guest
MasterZiv
qwerty112
select orderid from @a
where goods in ('it1','it3','it4')
group by orderid
having count(*)=3


Это неправильный запрос.

почему ?
10 фев 13, 11:14    [13903976]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
Ennor Tiegael
Я думаю, distinct в вашем запросе можно убрать. Он нужен только, если один и тот же товар может проходить дважды разными позициями в одном и том же заказе.
Конечно можно. Но условие уникальности товара в заказе не было озвучено ТСом, отсюда и общее решение с distinct.
10 фев 13, 11:22    [13903984]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
Dima T
Member

Откуда:
Сообщений: 15865
qwerty112
MasterZiv
пропущено...


Это неправильный запрос.

почему ?

Потому что в условии задачи не заявлена уникальность пар (orderid, goods)

Надо это учитывать
select orderid from @a 
where goods in ('it1','it3','it4')
group by orderid
having count(distinct goods)=3
10 фев 13, 12:43    [13904090]     Ответить | Цитировать Сообщить модератору
 Re: Составить запрос без подзапросов  [new]
qwerty112
Guest
Dima T
Потому что в условии задачи не заявлена уникальность пар (orderid, goods)

да, согласен

спасибо
10 фев 13, 12:56    [13904115]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить