将一个返回多列的子查询放在
FROM列表中,然后从中选择。
首先,相关子查询将是一个坏主意。但是,您的查询甚至没有关联,但是 没有关联
(没有链接到外部查询),并且似乎返回了多行。这导致产生笛卡尔积的(可能非常昂贵且荒谬的)交叉连接,可能不是您的(秘密)意图。
看起来您确实想要:
SELECt m1.mat AS mat1, m1.sumtotal AS sumtotal1 ,m2.mat AS mat2, m2.sumtotal AS sumtotal2FROM ( SELECt mat.mat, sum(stx.total) AS sumtotal FROM stx LEFT JOIN mat ON mat.matid = stx.matid LEFT JOIN sale ON stx.saleid = sale.id WHERe stx.date BETWEEN '2013-05-01' AND '2013-08-31' AND sale.userid LIKE 'A%' GROUP BY mat.mat ) m1JOIN ( SELECt mat.mat, sum(stx.total) AS sumtotal FROM stx LEFT JOIN mat ON mat.matid = stx.matid LEFT JOIN sale ON sale.id = stx.saleid WHERe stx.date BETWEEN '2013-05-01' AND '2013-08-31' AND sale.userid LIKE 'b%' GROUP BY mat.mat ) m2 USING (mat);
两者
LEFT JOIN也都是毫无意义的。WHERe条件将一个
sale强制为a
INNER JOIN。垫子上的那个似乎毫无意义,因为您
GROUPBY mat.mat-除非您有兴趣
mat IS NULL?(我对此表示怀疑。)
这种情况可能可以进一步简化为:
SELECt m.mat ,sum(CASE WHEN s.userid LIKE 'A%' THEN x.total END) AS total_a ,sum(CASE WHEN s.userid LIKE 'B%' THEN x.total END) AS total_bFROM sale s JOIN stx x ON x.saleid = s.idJOIN mat m ON m.matid = x.matidWHERe (s.userid LIKE 'A%' OR s.userid LIKE 'B%')AND x.date BETWEEN '2013-05-01' AND '2013-08-31'GROUP BY 1;
WHERe根据您的秘密数据类型和索引,可以进一步简化该条件。在dba.SE的相关答案中,提供了大量有关该情况的信息。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)