SQL查询获取股票数据

Question

我正在进行一个查询，以便从特定日期范围的销售和购买数据中获取股票详细信息(销售、购买、返回)。

但是，这个查询在应该返回的时候不会返回任何数据。

这是一个有问题的查询：

select 
    pro.Name 'ItemName',
    (select (ISNULL(SUM(SPurchases.Qty),0)-ISNULL(SUM(SOrder_return.Qty),0))-(ISNULL(SUM(SSales.Qty),0)- ISNULL(SUM(SSales_returns.Qty),0)) 
from
    [Order] _SOrder
left outer join 
    Order_Item SPurchases on _SOrder.Id = SPurchases.[Order] 
                          and SPurchases.OrderType = 1 and SPurchases.Product = pro.Id
left outer join 
    Order_Item SOrder_return on _SOrder.Id = SOrder_return.[Order] 
                             and SOrder_return.OrderType = 2 and SOrder_return.Product = pro.Id
        ,Sale _SSale  
left outer join 
    Sale_Item SSales on _SSale.Id = SSales.Sale and SSales.SaleType = 1 
                     and SSales.Product = pro.Id
left outer join 
    Sale_Item SSales_returns on _SSale.Id = SSales_returns.Sale 
                             and SSales_returns.SaleType = 3 and SSales_returns.Product = pro.Id
where 
    _SOrder.Date<@StartDate
    and _SSale.Date<@StartDate) as 'Opening',   
   isnull(SUM(purchases.Qty),0) as 'Purchase',
   isnull(SUM(order_return.Qty),0) as 'PurRet',
   isnull(SUM(sales.Qty),0) as 'Sale',
   isnull(SUM(sales_returns.Qty),0) as 'SaleRet'
from 
   Product pro, [Order] _Order
left outer join 
   Order_Item purchases on _Order.Id = purchases.[Order] and purchases.OrderType = 1 
left outer join 
   Order_Item order_return on _Order.Id = order_return.[Order] and order_return.OrderType = 2 
    ,Sale _sale
left outer join 
   Sale_Item sales on _sale.Id = sales.Sale and sales.SaleType = 1 
left outer join 
   Sale_Item sales_returns on _sale.Id = sales_returns.Sale and sales_returns.SaleType = 3 
where 
   _Order.Date >= @StartDate and _Order.Date <= @EndDate
   and _sale.Date >= @StartDate and _sale.Date <= @EndDate
   and purchases.Product = pro.Id 
   and order_return.Product = pro.Id 
   and sales.Product = pro.Id 
   and sales_returns.Product = pro.Id 
group by 
   pro.Name, pro.Id

如果您需要更多关于模式和数据的信息，我已经用数据上传了完整的模式到SqlFiddle。

Answer 1

where子句中有许多项

and purchases.Product=pro.Id and order_return.Product=pro.Id and sales.Product=pro.Id and sales_returns.Product=pro.Id 

只有当外部联接中的所有项都存在时，才能为真。考虑到您选择了一个外部连接，我将假设它们是可选的，因此并不总是存在。

与其将它们放在where中，不如将它们放在join语句中：

left outer join Sale_Item sales_returns on 
                    on _sale.Id=sales_returns.Sale 
                       and sales_returns.SaleType=3 
                       and sales_returns.Product=pro.Id