是否可以根据同一条目中的其他列包含值SELECT列？

Apologies on the heading, not sure I can explain this easily/well. I've inherited a report that runs off a table (StockMovements). This table contains product number, movement type, and quantity (among others, those 3 are the important values). The report is using a ProductMaster table and for each product number trying to group by product and then have a column for each movement type, summing all entries which match. At the moment it is doing something like this:

在标题上道歉,不确定我能否轻松/好好地解释这一点。我继承了一张桌子上的报告(StockMovements)。此表包含产品编号,移动类型和数量(其中,3个是重要值)。该报告使用ProductMaster表,并尝试按产品分组的每个产品编号,然后为每个移动类型添加一列,将所有匹配的条目相加。目前正在做这样的事情:

SELECT PM.ProductNumber,
    (SELECT SUM(Quantity) FROM StockMovements WHERE MovementType='StockIn' AND ProductNumber=PM.ProductNumber) AS [StockIn],
    (SELECT SUM(Quantity) FROM StockMovements WHERE MovementType='StockOut' AND ProductNumber=PM.ProductNumber) AS [StockOut],
    (SELECT SUM(Quantity) FROM StockMovements WHERE MovementType='ClosingStock' AND ProductNumber=PM.ProductNumber) AS [ClosingStock]
FROM ProductMaster AS PM
GROUP BY PM.ProductNumber

This is killing the system as StockMovements is a massive table and as you can see it's doing a new lookup for every product AND every movement type. I'm trying to modify the stored procedure to drive the SELECT from StockMovements grouped by ProductNumber, but am struggling to see how I can have each of the different columns summing values based on MovementType. What I'd love is something like this:

这会杀死系统,因为StockMovements是一个庞大的表格,你可以看到它正在为每个产品和每种移动类型进行新的查找。我正在尝试修改存储过程以从按ProductNumber分组的StockMovements驱动SELECT,但我很难看到如何根据MovementType将每个不同的列加起来。我喜欢的是这样的:

SELECT ProductNumber,
    SUM(Quantity) WHERE MovementType='StockIn',
    SUM(Quantity) WHERE MovementType='StockOut',
    SUM(Quantity) WHERE MovementType='ClosingStock'
FROM StockMovement
GROUP BY ProductNumber

I'm more than willing to rewrite the whole this and drive it off whatever makes most sense, I'm just struggling to find a better way of doing it (please tell me it's simple and I've just been starting at it way too long).

我非常愿意重写这一切,并把它推到最有意义的地方,我只是在努力找到一个更好的方法(请告诉我这很简单,我刚开始也是这样)长)。

1 个解决方案

#1

Close.

SELECT ProductNumber,
    SUM(CASE WHEN MovementType='StockIn' THEN Quantity END),
    SUM(CASE WHEN MovementType='StockOut' THEN Quantity END),
    SUM(CASE WHEN MovementType='ClosingStock' THEN Quantity END)
FROM StockMovement
GROUP BY ProductNumber

If there are other movement types, you might want to consider adding a WHERE clause to restrict to just the 3 types you're interested in.

如果还有其他移动类型,您可能需要考虑添加WHERE子句以限制您感兴趣的3种类型。

If some products may have no rows for a movement type, you might want to wrap the SUM() in a COALESCE():

如果某些产品可能没有移动类型的行,您可能希望将SUM()包装在COALESCE()中:

COALESCE(SUM(CASE WHEN MovementType='StockIn' THEN Quantity END),0)

to give a nicer total.

给出更好的总数。

#1