如何使用总和制作商店程序？ [英] How can i make a store procedure with sum?
问题描述
大家好
我做了一个商店程序来总结一栏
但它是不工作
结果是这个
1面包130
2面包230
我想要这个
1面包360
谢谢提前
我的尝试：
更改程序[dbo] 。[Get_FoodBread]
@Food NVARCHAR（50），
@FDate DATETIME，
@ FDate1 DATETIME
AS $ / $
BEGIN
SELECT供应商，文档，食品，量度，和（数量）AS Quant，FDate FROM FoodLem
WHERE Food = @ Food并且@FDate和@ FDate1之间的FD $
GROUP BY供应商，文档，食品，措施，FDate
END
Hi guys
I've made a store procedure to sum a column
but it's not working
the result is this
1 Bread 130
2 Bread 230
I want this
1 Bread 360
Thanks in advance
What I have tried:
ALTER PROCEDURE [dbo].[Get_FoodBread]
@Food NVARCHAR (50),
@FDate DATETIME,
@FDate1 DATETIME
AS
BEGIN
SELECT Supplier,Doc,Food,Measure,SUM(Quantity) AS Quant,FDate FROM FoodLem
WHERE Food=@Food AND FDate BETWEEN @FDate AND @FDate1
GROUP BY Supplier,Doc,Food,Measure,FDate
END
推荐答案
GROUP BY将为GROUP BY子句中的每个不同值集创建不同的行。因此，当您指定多个列时，它会根据每个列创建不同的行 - 要在示例中只获取一行，您需要在所有指定列中使用相同的值：Supplier，Doc，Food，Measure和FDate如果有的话其中包含两个不同的值，你得到两个不同的行。
尝试：
GROUP BY will create different rows for each different set of values in your GROUP BY clause. So when you specify several columns, it creates different rows based on each of them - to get just one row in your example, you need the same values in all of the specified columns: Supplier, Doc, Food, Measure, and FDate If any of these contain two different values, you get two different rows.
Try:
SELECT ...
GROUP BY Food但是请记住，如果列出现在GROUP BY子句中，则只能直接指定列 - 如果要引用其他列，则需要使用GROUP BY作为子查询并加入它。
But remember that you can only specify columns directly if they appear in the GROUP BY clause - if you want to reference other columns, you will need to use teh GROUP BY as a subquery and JOIN to it.
这篇关于如何使用总和制作商店程序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
