我在尝试执行查询时遇到一些问题.我有两个表,一个是元素信息,另一个表与第一个表的元素相关.这个想法是在同一行中获取元素信息加上几个记录信息.
结构可以这样解释：
- 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,'2010-01-02']
- [2,3,'2010-01-07']
- [2,'2010-01-07']
这是我想要实现的：
- result [id,name,Column1,Column2,Column3,Column4]
- [1,'1','2009-12-02','2010-01-03','2','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,4))
- AND t.value between '2010-01-01' and '2010-01-07') as subquery
- LEFT JOIN table a ON a.id = subquery.id
解决方法
您可以将聚合结果转换为CTE(常用表表达式)：
- with minima as (select t.id,t.type,min(value) min_value
- from table2 t
- where t.type in (1,4)
- group by t.id,t.type)
- select a.id,(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,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,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,min(value) min_value
- from table2 t
- where t.type in (1,4)
- group by t.id,t.type) minima
- group by id
- ) xx left join a on a.id = xx.id
- order by 1
