如何连接四个表并使用SUM和分组依据

Question

我有一个Firebird数据库，有一个连接四个表信息的查询，但我还需要求和并按分组

我的问题是：

SELECT ARQOS.CNPO AS CNPOARQOS, 
ARQOS.CODSEQ AS CODSEQARQOS, 
ARQOS.CCLIENTE, 
ARQOS.CREF, 
ARQOS.CQUANT, 
ARQOS.CCHAR5, 
ARQOS.EMAIL, 
ARQOS.ORCAGERA, 
PRIDAT.CODSEQ as CODSEQPRIDAT,
PRIDAT.CCHAR1, 
PRIDAT.QUANTIDADE, 
PRIDAT.CLIENTE, 
PRIDAT.PRECOUNITARIO, 
PRIDAT.PRECOFINAL, 
PRIDAT.PRECOPORMIL, 
REPLACE(CODITEM,'.','') AS CODITEM, 
MATEMPEN.NOMEITEM, 
CLIENTES.CRAZAO, 
REPLACE(REPLACE(REPLACE(CCGC,'-',''), '/', ''),'.','') AS CCGC, 
CLIENTES.CINSCEST, 
CLIENTES.CINSCMUN
from ARQOS
JOIN PRIDAT ON ARQOS.CODSEQ = PRIDAT.CODSEQ
JOIN MATEMPEN SELECT NOMEITEM, SUM (QUANTORIG) AS QTDTOTAL
FROM MATEMPEN
GROUP BY NOMEITEM ON MATEMPEN.CODEMPENHO=ARQOS.CNPO
JOIN CLIENTES ON CLIENTES.CCODIGO=ARQOS.CODCLIENTE
WHERE ARQOS.CNPO=32838 

Answer 1

将查询更改为：

SELECT 
ARQOS.CNPO AS CNPOARQOS, 
ARQOS.CODSEQ AS CODSEQARQOS, ARQOS.CCLIENTE, ARQOS.CREF, 
ARQOS.CQUANT, 
ARQOS.CCHAR5, 
ARQOS.EMAIL, 
ARQOS.ORCAGERA, 
PRIDAT.CODSEQ as CODSEQPRIDAT,
PRIDAT.CCHAR1, 
PRIDAT.QUANTIDADE, 
PRIDAT.CLIENTE, PRIDAT.PRECOUNITARIO, 
PRIDAT.PRECOFINAL, 
PRIDAT.PRECOPORMIL, 
REPLACE(CODITEM,'.','') AS CODITEM, 
MATEMPEN.NOMEITEM, 
CLIENTES.CRAZAO, 
REPLACE(REPLACE(REPLACE(CCGC,'-',''), '/', ''),'.','') AS CCGC, 
CLIENTES.CINSCEST, 
CLIENTES.CINSCMUN
from ARQOS
JOIN PRIDAT ON ARQOS.CODSEQ = PRIDAT.CODSEQ
JOIN MATEMPEN ON MATEMPEN.CODEMPENHO=ARQOS.CNPO
JOIN CLIENTES ON CLIENTES.CCODIGO=ARQOS.CODCLIENTE
JOIN 
(  SELECT NOMEITEM, 
SUM (QUANTORIG) AS QTDTOTAL
FROM MATEMPEN
GROUP BY NOMEITEM 
) as t1 ON t1.column = table.column ---here add the join condition (the t1 columns are NOMEITEM or QTDTOTAL)

WHERE ARQOS.CNPO=32838

Answer 2

您需要给这个联接子查询一个别名：

JOIN (SELECT NOMEITEM, SUM (QUANTORIG) AS QTDTOTAL
FROM MATEMPEN
GROUP BY NOMEITEM) /*add alias here*/
JOIN CLIENTES
ON CLIENTES.CCODIGO=ARQOS.CODCLIENTE
WHERE ARQOS.CNPO=32838