Составить запрос без подзапросов

AVRob

Дата: 09.02.2013 22:43:28

имеется таблица со столбцами номер заказа 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

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

qwerty112

Дата: 09.02.2013 23:00:53

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

AVRob

Дата: 09.02.2013 23:19:31

спасибо

AVRob

Дата: 09.02.2013 23:37:12

в общем случае у нас

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

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

qwerty112

Дата: 09.02.2013 23:41:23

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

но так может - и хуже оказатся ... а может и нет ...

AVRob

Дата: 09.02.2013 23:48:44

qwerty112
select orderid from @a where left(goods, 3) in ('it1','it3','it4') group by orderid having count(*)=3

Интересная идея, но наверное не подойдет
длины искомых значений goods могут быть различными, и длины значений колонки goods тоже различны.

invm

Дата: 10.02.2013 00:04:00

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 = на >=.

Cygapb-007

Дата: 10.02.2013 00:05:30

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

invm

Дата: 10.02.2013 00:06:13

Насчет ">=" наврал.

Cygapb-007

Дата: 10.02.2013 00:10:46

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