现在我有这个查询：
SELECT
Categoria.Descricao AS Categoria,
Marca.descricao AS Marca,
Modelo.descricao AS Modelo,Material.n_serie As 'Nº Série',
SUM(Entrada.Qtd-COALESCE(Saida.Qtd,0)) AS QTD ,username AS 'User'
FROM Marca,Modelo,Categoria,Material,Users,Entrada
LEFT JOIN Saida ON Entrada.n_serie=Saida.n_serie
WHERE Marca.id_marca=Modelo.id_marca
AND Modelo.categoria= Categoria.id_categoria
AND Modelo.id_modelo=Material.id_modelo
AND Material.n_serie=Entrada.n_serie
AND Entrada.user=Users.id
GROUP BY Categoria.descricao,Marca.descricao,Modelo.descricao,Material.n_serie;
输出
Categoria, Marca, Modelo, Nº Série, QTD, User
'Cabo', 'UTP', '3 metros', '1234', '2', 'admin'
如您所见，当我只有一根时，它告诉我剩下两根UTP电缆。
表Entrada（输入）：
n_serie, data_entrada, user, qtd, obs
'1234', '2014-05-12 14:37:34', '6', '50', ''
'1234', '2014-05-12 14:37:43', '6', '2', ''
50 + 2 = 52
表赛达（外）：
n_serie, data_saida, user, qtd, obs
'1234', '2014-05-12 14:50:16', '6', '2', ''
'1234', '2014-05-12 14:50:22', '6', '49', ''
49 + 2 = 51
有人可以告诉我这个查询在哪里接另一个查询吗？
提前致谢。
最佳答案
问题是您要将2行和2行连接起来，结果得到4行，然后合计这4行...
（Entrada中的每一行都与Saida中的两行都匹配。）
Entrada: Saida:
n_serie, qtd n_serie, qtd a - b
'1234' , 50 '1234' , 2 48
'1234' , 50 '1234' , 49 1
'1234' , 2 '1234' , 2 0
'1234' , 2 '1234' , 49 -47
-----
2
-----
您需要做的是首先聚合每个表，然后将它们连接在一起。
(
SELECT n_serie, SUM(qtd) AS qtd
FROM Entrada
GROUP BY n_serie
)
AS entrada
LEFT JOIN
(
SELECT n_serie, SUM(qtd) AS qtd
FROM Saida
GROUP BY n_serie
)
AS Saida
ON Saida.n_serie = Entrada.n_serie
那会给你...
Entrada: Saida:
n_serie, qtd n_serie, qtd a - b
'1234' , 52 '1234' , 51 1