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

Откуда: СПб
Сообщений: 206
Есть таблица vals (id int, serId...) связанная с табличкой series (id, typeId...) по ключу vals.serId=series.id. Есть справочник types(id,name), на котрую указывает поле series.typeId (по полю types.id).

Требуется выбрать из таблицы все значения из series, при этом для типа type.name='aaa' выбрать одно любое значение, а для остальных типов - все значения. Первая возникшая мысль выразилась в следующем запросе:

SELECT v.id AS v_ID, sr.id as sid, t.name  AS type_name 
from series sr inner join types t on t.id=sr.typeId inner join 
	(select top 1 id, serId
	from vals
	where exists
		(select 8 
		from series sr inner join types t on t.id=sr.typeId
		where sr.id=serId and t.Name='aaa')
	order by id
	union all 
	select id, serId
	from vals
	where exists
		(select 8 
		from series sr inner join types t on t.id=sr.typeId
		where sr.id=serId and t.Name<>'aaa')
	) v on sr.ID=v.serId


Есть у меня некоторое внутреннее неприятие этого запроса. Собственно вопрос, можно ли это как-то оптимизировать, или перестроить запрос на более простой без юнионов, и минимумом сложных подзапросов?

--------------------
"Сogitationus poenam nemo patitur"
Никто не несет наказания за мысли
31 янв 12, 15:04    [12004464]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Можно как-нибудь извратиться, конечно.
Но мне запрос с UNION нравится больше
SELECT TOP(1) WITH TIES v.id AS v_ID, sr.id as sid, t.name  AS type_name 
FROM series sr
JOIN types t ON sr.typeId=t.id
JOIN vals v ON sr.id=v.serId
ORDER BY ROW_NUMBER()OVER(PARTITION BY CASE WHEN t.Name<>'aaa' THEN sr.id END ORDER BY sr.id);
31 янв 12, 15:37    [12004863]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
Но мне запрос с UNION нравится больше
Хотя, я в него не вникал.
Исходил из словесного описания.
И не проверял!
31 янв 12, 15:39    [12004899]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
hdd-killer
Member

Откуда: СПб
Сообщений: 206
Проверил, не работает((( Не очень понял, как вообще работает предложенный вариант (в частности, что значит "SELECT TOP(1) WITH TIES..."). Ушел читать мат. часть. =)
31 янв 12, 16:01    [12005152]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
hdd-killer
Проверил, не работает((( Не очень понял, как вообще работает предложенный вариант (в частности, что значит "SELECT TOP(1) WITH TIES..."). Ушел читать мат. часть. =)
"Не работает" - даёт неверный результат, сообщает об ошибке, ...?
31 янв 12, 16:26    [12005375]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Да и скрипты неплохо бы выложить по созданию и заполнению таблиц...
31 янв 12, 16:27    [12005390]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
hdd-killer
Member

Откуда: СПб
Сообщений: 206
Скрипты заполнения таблиц:
+

declare @vals table 
(id int identity(1,1),
serId int);

declare @series table
(id int identity(1,1), 
typeId int)

declare @types table
(id int identity(1,1),
name varchar(10))

insert into @types (name)
select 'aa'
union all
select 'bb'
union all
select 'cc'
union all
select 'dd'

insert into @series (typeId)
select 1
union all
select 2
union all
select 2
union all
select 4
union all
select 3

insert into @vals (serId)
select 5
union all
select 2
union all
select 2
union all
select 4
union all
select 2
union all
select 4
union all
select 1
union all
select 3
union all
select 6



Вариант с юнионом:
SELECT 
	(select top 1 ID
	from @vals
	where sr.id=serId
	order by id) AS val_ID
	, sr.id as sid, t.name  AS [type_name]
from @series sr inner join @types t on t.id=sr.typeId
where 'bb'=t.name
union all 
select v.id AS val_ID, sr.id as sid, t.name  AS [type_name] 
from @series sr inner join @types t on t.id=sr.typeId
	inner join @vals v on sr.id=v.serId
where t.name<>'bb'

Результат (какой и ожидался, в рамках одной серии только одно значение, если тип серии 'bb'. Для остальных серий все значения):

val_ID sid type_name
----------- ----------- ----------
2 2 bb
8 3 bb
7 1 aa
6 4 dd
4 4 dd
1 5 cc


Ваш вариант:
SELECT TOP(1) WITH TIES v.id AS v_ID, sr.id as sid, t.name  AS [type_name] 
FROM @series sr
JOIN @types t ON sr.typeId=t.id
JOIN @vals v ON sr.id=v.serId
ORDER BY ROW_NUMBER()OVER(PARTITION BY CASE WHEN t.Name<>'bb' THEN sr.id END ORDER BY sr.id);

Результат:

v_ID sid type_name
----------- ----------- ----------
2 2 bb
7 1 aa
4 4 dd
1 5 cc
31 янв 12, 16:57    [12005713]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
hdd-killer,
/*1*/
SELECT TOP(1) WITH TIES v.id AS v_ID, sr.id as sid, t.name  AS [type_name] 
FROM @series sr
JOIN @types t ON sr.typeId=t.id
JOIN @vals v ON sr.id=v.serId
ORDER BY ROW_NUMBER()OVER(PARTITION BY CASE WHEN t.name<>'bb' THEN v.id END ORDER BY v.id);
/*2*/
WITH CTE AS
(
 SELECT N=ROW_NUMBER()OVER(PARTITION BY CASE WHEN t.name<>'bb' THEN v.id END ORDER BY v.id),
  v.id AS v_ID, sr.id as sid, t.name  AS [type_name]
 FROM @series sr
 JOIN @types t ON sr.typeId=t.id
 JOIN @vals v ON sr.id=v.serId
)
SELECT v_ID,sid,[type_name]
FROM CTE
WHERE N=1;
31 янв 12, 17:20    [12005999]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
hdd-killer
Member

Откуда: СПб
Сообщений: 206
Не совсем то, что нужно: в вашем варианте в результат попадает только одна строка с типом 'bb', а нужно что б в каждой серии (а не во всей выборке) было не более одной строки с типом 'bb'.

Но в целом суть понял, спасибо)
31 янв 12, 17:31    [12006125]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить