Is possible to reuse subqueries?

问题:

I'm having some problems trying to perform a query. I have two tables, one with elements information, and another one with records related with the elements of the first table. The idea is to get in the same row the element information plus several records information.

Structure could be explain like this:

 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']

And this is want I would like to achieve:

 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']

The following query gets the proper result, but it seems to me extremely inefficient, having to iterate table2 for each column. Would be possible in anyway to do a subquery and reuse it?

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

回答1:

You can take the aggregations out into a CTE (common table expression):

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

Whether this is actually any benefit (or even supported) or not depends on your environment and dataset, of course.

Another approach:

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

回答2:

Some of later database products (Oracle, SQL Server 2005, SQL Server 2008 and so on) provide the ability to create common table expressions (CTE for short). With that you could reuse a subquery like so:

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

Granted this wouldn't be much different than:

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