Возможно снова использовать подзапросы?
У меня есть некоторые проблемы при попытке выполнить запрос. У меня есть две таблицы, один с информацией об элементах и другим с записями, связанными с элементами первой таблицы. Идея состоит в том, чтобы войти в ту же строку информация об элементе плюс несколько информации о записях.
Структура могла быть, объясняют как это:
table [ id, name ]
[1, '1'], [2, '2']
table2 [ id, type, value ]
[1, 1, '2009-12-02']
[1, 2, '2010-01-03']
[1, 4, '2010-01-03']
[2, 1, '2010-01-02']
[2, 2, '2010-01-02']
[2, 2, '2010-01-03']
[2, 3, '2010-01-07']
[2, 4, '2010-01-07']
И это, хотят, я хотел бы достигнуть:
result [id, name, Column1, Column2, Column3, Column4]
[1, '1', '2009-12-02', '2010-01-03', , '2010-01-03']
[2, '2', '2010-01-02', '2010-01-02', '2010-01-07', '2010-01-07']
Следующий запрос получает надлежащий результат, но это кажется мне чрезвычайно неэффективным, имея необходимость выполнить итерации table2 для каждого столбца. Было бы возможно в так или иначе сделать подзапрос и снова использовать его?
SELECT
a.id,
a.name,
(select min(value) from table2 t where t.id = subquery.id and t.type = 1 group by t.type) as Column1,
(select min(value) from table2 t where t.id = subquery.id and t.type = 2 group by t.type) as Column2,
(select min(value) from table2 t where t.id = subquery.id and t.type = 3 group by t.type) as Column3,
(select min(value) from table2 t where t.id = subquery.id and t.type = 4 group by t.type) as Column4
FROM
(SELECT distinct id
FROM table2 t
WHERE (t.type in (1, 2, 3, 4))
AND t.value between '2010-01-01' and '2010-01-07') as subquery
LEFT JOIN table a ON a.id = subquery.id
2 ответа
Вы можете вынести агрегаты в CTE (общее табличное выражение):
with minima as (select t.id, t.type, min(value) min_value
from table2 t
where t.type in (1,2,3,4)
group by t.id, t.type)
select a.id, a.name,
(select min_value from minima where minima.id = subquery.id and minima.type = 1) as column1,
(select min_value from minima where minima.id = subquery.id and minima.type = 2) as column2,
(select min_value from minima where minima.id = subquery.id and minima.type = 3) as column3,
(select min_value from minima where minima.id = subquery.id and minima.type = 4) as column4
from (select distinct id from table2 t where t.type in (1,2,3,4) and t.value between '2010-01-01' and '2010-01-07') as subquery
left join a on a.id = subquery.id
Будет ли это на самом деле какое-либо преимущество (или даже поддержка) или нет, зависит от ваша среда и набор данных, конечно.
Другой подход:
select xx.id, a.name, xx.column1, xx.column2, xx.column3, xx.column4
from (
select id,
max(case type when 1 then min_value end) as column1,
max(case type when 2 then min_value end) as column2,
max(case type when 3 then min_value end) as column3,
max(case type when 4 then min_value end) as column4
from (select t.id, t.type, min(value) min_value
from table2 t
where t.type in (1,2,3,4)
group by t.id, t.type) minima
group by id
) xx left join a on a.id = xx.id
order by 1
Некоторые из более поздних продуктов баз данных (Oracle, SQL Server 2005, SQL Server 2008 и т. Д.) Предоставляют возможность создавать общие табличные выражения (CTE для краткости) . При этом вы можете повторно использовать такой подзапрос:
With Subquery As
(
Select Id
, Min( Case When T.TypeId = 1 Then Value End ) As MinType1
, Min( Case When T.TypeId = 2 Then Value End ) As MinType2
, Min( Case When T.TypeId = 3 Then Value End ) As MinType3
, Min( Case When T.TypeId = 4 Then Value End ) As MinType4
From Table2 As T
Where T.Type In(1,2,3,4)
And T.Value Between '2010-01-01' And '2010-01-07'
Group By Id
)
Select A.Id, A.Name, S.MinType1, S.MinType2, S.MinType3, S.MinType4
From Subquery As S
Left Join Table As A
On A.Id = S.Id
Конечно, это не будет сильно отличаться от:
Select A.Id, A.Name, S.MinType1, S.MinType2, S.MinType3, S.MinType4
From (
Select Id
, Min( Case When T.TypeId = 1 Then Value End ) As MinType1
, Min( Case When T.TypeId = 2 Then Value End ) As MinType2
, Min( Case When T.TypeId = 3 Then Value End ) As MinType3
, Min( Case When T.TypeId = 4 Then Value End ) As MinType4
From Table2 As T
Where T.Type In(1,2,3,4)
And T.Value Between '2010-01-01' And '2010-01-07'
Group By Id
) As S
Left Join Table As A
On A.Id = S.Id