是否可以重用子查询? [英] 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.

结构可以这样解释:

 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.无论如何都可以执行子查询并重用它吗?

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

推荐答案

您可以将聚合取出到 CTE(公共表表达式)中:

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.

另一种方法:

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

这篇关于是否可以重用子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！