Sql查询以减去这两个表。怎么啦？

Question

select table1.t1 from
(
(
select   
         ItemCategory.Name as Category,
         InventoryItems.Name as ItemName,
         sum(SalesItems.Quantity) as Quantity,
         (InventoryItems.Weight*sum(SalesItems.Quantity)) as Weight,
         sum(SalesItems.Amount) as Amount
   from SalesInvoices 
   inner join Sales on Sales.ID = SalesInvoices.SalesID 
   inner join SalesItems on SalesItems.SalesID = Sales.ID
   inner join InventoryItems on InventoryItems.ID = SalesItems.InventoryItemID 
   inner join ItemCategory on ItemCategory.ID = InventoryItems.ItemCategoryID 
   inner join BusinessPartners on Sales.BusinessPartnerID = BusinessPartners.ID
   where SalesInvoices.Date >= '2013-07-1' and SalesInvoices.Date <= '2013-11-7'
   group by ItemCategory.Name,InventoryItems.Name,InventoryItems.Weight
) as t1,
(
select  
        ItemCategory.Name as Category,
        InventoryItems.Name as ItemName,
        sum(SalesAdjustmentItems.AdjustedQuantity)*-1 as Quantity,
        (sum(SalesAdjustmentItems.AdjustedQuantity)*InventoryItems.Weight)*-1 as  
         Weight,
         sum(SalesAdjustmentItems.AmountReturn)*-1 as Amount
 from SalesInvoices 
 inner join Sales on Sales.ID = SalesInvoices.SalesID 
 inner join SalesItems on SalesItems.SalesID = Sales.ID
 inner join SalesAdjustmentItems on SalesAdjustmentItems.SalesItemID = SalesItems.ID 
 inner join InventoryItems on InventoryItems.ID = SalesItems.InventoryItemID
 inner join ItemCategory on ItemCategory.ID = InventoryItems.ItemCategoryID 
 inner join SalesAdustment on SalesAdustment.SalesInvoiceID = SalesInvoices.ID
 inner join BusinessPartners on Sales.BusinessPartnerID = BusinessPartners.ID 
 where SalesAdustment.Date>= '2013-07-1' and SalesAdustment.Date <= '2013-11-7'
 group by ItemCategory.Name,InventoryItems.Name,InventoryItems.Weight
 ) as t2
 )
 as table1

我在这个查询中做错了什么。第一个查询是销售查询，第二个查询是销售返回查询。我想得到销售和回报的差额。却给了我错误。

谢谢

Answer 1

SQL减号操作符称为EXCEPT，例如查找没有发票的sales：

-- Sales minus SalesInvoices
SELECT ID
  FROM Sales
EXCEPT
SELECT SalesID
  FROM SalesInvoices;

Answer 2

如果您使用的是旧版本，

SELECT ID
  FROM Sales 
where not exists(SELECT SalesID  FROM SalesInvoices where sales.ID=SalesID);