两个表的计算量不同

问题描述：

我想从两个不同的表中减去。结果是第三个表格。这是我的查询SQL：两个表的计算量不同

SELECT 
    a.ma 
    ,a.Ten 
    ,a.Dvt 
    ,ISNULL(SUM(a.Soluong),0) AS Nhap 
    ,ISNULL(SUM(b.Soluong),0) AS Xuat 
    ,(ISNULL(SUM(a.Soluong),0)-ISNULL(SUM(b.Soluong),0)) AS ton 
    ,a.Batch,a.MaKe 
FROM tbNhap a 
    INNER JOIN tbXuat b ON a.ma=b.ma and a.Batch=b.Batch 
GROUP BY 
    a.ma 
    ,a.Ten 
    ,a.Dvt 
    ,a.Batch 
    ,a.MaKe 
HAVING a.Batch LIKE '3217101711' AND a.ma LIKE '11020000000031' 
ORDER BY 
    SUBSTRING(CAST(a.Batch AS varchar(10)), 5, 2) 
    ,SUBSTRING(CAST(a.Batch AS varchar(10)), 3, 2) ASC;

我想显示相同的照片。请。

请添加文本/代码 – Tyron78

不使用的图像预期的结果数据或想要的结果。将其复制/粘贴到格式化文本中。如果需要的话，有些站点可以帮助制作固定的ASCII表格。 https://ozh.github.io/ascii-tables/ –

你的'having'子句应该是'where'子句（在'group by'之前移动它）。具有条款旨在评估汇总值，这些条件不需要这些值。 –

答

我想你应该改变SUM和ISNULL之间的顺序，否则，如果你在你的总和只有一个NULL，总和将是NULL，然后将ISNULL其转换为0。SELECT会像这样：

SELECT 
     a.ma 
    ,a.Ten 
    ,a.Dvt 
    ,SUM(ISNULL(a.Soluong,0)) AS Nhap 
    ,SUM(ISNULL(b.Soluong,0)) AS Xuat 
    ,SUM(ISNULL(a.Soluong,0)-ISNULL(b.Soluong,0)) AS ton 
    ,a.Batch,a.MaKe 
FROM tbNhap a 
    INNER JOIN tbXuat b ON a.ma=b.ma and a.Batch=b.Batch 
GROUP BY 
    a.ma 
    ,a.Ten 
    ,a.Dvt 
    ,a.Batch 
    ,a.MaKe 
HAVING a.Batch LIKE '3217101711' AND a.ma LIKE '11020000000031' 
ORDER BY 
     SUBSTRING(CAST(a.Batch AS varchar(10)), 5, 2) 
    ,SUBSTRING(CAST(a.Batch AS varchar(10)), 3, 2) ASC;

谢谢！但结果是错误的。我不喜欢它 –

答

这会给你想要的东西：

DECLARE @tbNhap TABLE 
(
    [id] INT 
    ,[ma] VARCHAR(24) 
    ,[Ten] VARCHAR(24) 
    ,[Dvt] VARCHAR(2) 
    ,[Soluong] INT 
    ,[Batch] VARCHAR(12) 
    ,[Make] VARCHAR(4) 
); 

DECLARE @tbXuat TABLE 
(
    [id] INT 
    ,[ma] VARCHAR(24) 
    ,[Ten] VARCHAR(24) 
    ,[Dvt] VARCHAR(2) 
    ,[Soluong] INT 
    ,[Batch] VARCHAR(12) 
    ,[Make] VARCHAR(4) 
); 

INSERT INTO @tbNhap ([id], [ma], [Ten], [Dvt], [Soluong], [Batch], [Make]) 
VALUES (1, 11020000000031, 'PTZ5S DN', 'PC', 1200, 3714101711, 'Q209') 
     ,(1, 11020000000031, 'PTZ5S DN', 'PC', 1000, 3217101711, 'R202') 
     ,(1, 11020000000031, 'PTZ5S DN', 'PC', 770, 3217101711, 'C203'); 

INSERT INTO @tbXuat ([id], [ma], [Ten], [Dvt], [Soluong], [Batch], [Make]) 
VALUES (1, 11020000000031, 'PTZ5S DN', 'PC', 500, 3217101711, 'R202') 
     ,(1, 11020000000031, 'PTZ5S DN', 'PC', 100, 3217101711, 'C203') 
     ,(1, 11020000000031, 'PTZ5S DN', 'PC', 350, 3217101711, 'C203'); 

SELECT a.ma 
     ,a.Ten 
     ,a.Dvt 
     ,SUM(ISNULL(a.Soluong,0)) AS Nhap 
     ,MAX(b.Xuat) AS Xuat 
     ,SUM(ISNULL(a.Soluong,0))- MAX(b.Xuat) AS ton 
     ,a.Batch 
     ,a.MaKe 
FROM @tbNhap a 
LEFT JOIN 
(
    SELECT ma 
      ,Ten 
      ,Dvt 
      ,Batch 
      ,make 
      ,SUM(ISNULL(Soluong,0)) AS Xuat 
    FROM @tbXuat 
    GROUP BY ma 
      ,Ten 
      ,Dvt 
      ,Batch 
      ,make 
) b 
    ON a.ma=b.ma 
    and a.Batch=b.Batch 
    and a.Make = b.make 
GROUP BY 
    a.ma 
    ,a.Ten 
    ,a.Dvt 
    ,a.Batch 
    ,a.MaKe 
HAVING a.ma LIKE '11020000000031' 
    ORDER BY 
     SUBSTRING(CAST(a.Batch AS varchar(10)), 5, 2) 
    ,SUBSTRING(CAST(a.Batch AS varchar(10)), 3, 2) ASC;

两个表的计算量不同

相关推荐