我在尝试执行查询时遇到一些问题.我有两个表,一个是元素信息,另一个表与第一个表的元素相关.这个想法是在同一行中获取元素信息加上几个记录信息.
结构可以这样解释：
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
