时间戳

首页 > 解决方案 > SQL避免子查询上的多部分标识符

sql - SQL避免子查询上的多部分标识符

问题描述

使用 SQLExpress 2017

我有一些产品分布在仓库中，我想看看需要库存多少物品才能满足给定时期的销售额。

Need pr. warehouse = Stock - CustomerOrders + SupplierOrders - SumOfSalesInPeriod

现在我想对每种产品进行总结，但我对已经满足需求的仓库不感兴趣，所以我只想要负值，但由于遇到多部分标识符错误，我很难让它工作. 使用 distinct 关键字也让我觉得我做了太多的计算，必须有更好的方法来做这件事。

declare @fromDate date = '1900-01-01 12:00:00';
declare @toDate date = '3000-01-01 12:00:00';

select *,
    balance =  
        (select 
            turn = sum(TurnOver)
        from (
        select 
            WarehouseStocks.Id,
            TurnOver = WarehouseStocks.Qty 
                        - WarehouseStocks.OrderedByCustomersQty 
                        + WarehouseStocks.OrderedFromSuppliersQty 
                        - isnull((select Sum(StockEntries.Qty) 
                                    from StockEntries 
                                    where 
                                        StockEntries.Type = 1 
                                        and StockEntries.ProductId = WarehouseStocks.Id 
                                        and WarehouseStocks.WarehouseId = StockEntries.WarehouseId 
                                        and StockEntries.Date >= @fromDate 
                                        and StockEntries.Date <= @toDate), 0) 
        from WarehouseStocks) Product where TurnOver < 0
        group by Product.Id) tp where Products.Id = tp.Id)
from Products

标签： sqlsql-server-express

解决方案

我会改用 CTE 重写它，以分解它并使查询更具可读性。像这样的东西：


declare @fromDate date = '1900-01-01 12:00:00';
declare @toDate date = '3000-01-01 12:00:00';

;with SE 
as
(
    select Sum(StockEntries.Qty) as SumStockEnties , StockEntries.ProductId, StockEntries.WarehouseId 
    from StockEntries 
    where 
        StockEntries.Type = 1 
        and StockEntries.Date >= @fromDate 
        and StockEntries.Date <= @toDate
    group by StockEntries.ProductId, StockEntries.WarehouseId 
),
TP
as
(
    Select WS.Id, WS.Qty - WS.OrderedByCustomersQty + WS.OrderedFromSuppliersQty - isnulle(SE.SumStockEnties, 0) as TurnOver
    from WarehouseStocks as WS
         left join SE
         on  SE.ProductId = WS.Id
          and SE.WarehouseId = WS.WarehouseId 
)
Select *
from TP 
    inner join Products as PR
    on PR.id = TP.id
Where PR.TurnOver < 0

推荐阅读