SP中的分组问题 [英] Grouping issue in SP

问题描述

我的SP会返回这样的结果

Hi,


my SP returns the result like this

Doc_Id   Doc_No        Rev    Qty   Category  Material_Type
1        xxx           0       1      A         RCC
1        xxx           1       2      A         RCC

现在，我想再为TotalQty添加一列



用于相同的Doc_no，相同的类别，相同的材料具有不同的Rev然后我想添加数量并显示在另一列中



EX：

Now, i want one more column for TotalQty

for same Doc_no, same Category,same Material having different Rev then i want to add the qty and display in another column

EX:

Doc_Id   Doc_No        Rev    Qty   Category  Material_Type      Total
1        xxx           0       1      A         RCC                1
1        xxx           1       2      A         RCC                1+2=3

这是我的SP的一部分

this is the part of my SP

SELECT   D.Doc_Id,D.Doc_No, D.Dwg_Rev_No,                            
 CONVERT(DECIMAL(16,3), ROUND (tq.Material_Quantity,3)) AS Material_Quantity, D.Dwg_Category_Short_Desc, DED.DED_Description As Material_Type

FROM #Dwg_Rev_Details D                         
  LEFT OUTER JOIN #Client_DCI C ON D.Doc_Id=C.Doc_Id AND D.Dwg_Rev_No=C.Dwg_Rev_No                                      
  LEFT OUTER JOIN #Site_DCI S ON D.Doc_Id=S.Doc_Id AND D.Dwg_Rev_No=S.Dwg_Rev_No                                      
  LEFT OUTER JOIN PTS_T_Drawing_Revision PDR ON D.Doc_Id=PDR.Doc_Id AND D.Dwg_Rev_No=PDR.Dwg_Rev_No              
  LEFT OUTER JOIN PTS_T_Drawing_Transmittals_Quantity TQ ON TQ.Doc_Id=D.Doc_Id AND D.Dwg_Rev_No=TQ.Dwg_Rev_No 
  LEFT OUTER JOIN   DMS_D_Drawing_Element_Details DED ON DED.DED_ID=TQ.MATERIAL_TYPE ,              
   DMS_D_Drawing_Schedule_Details DSD,              
   DMS_M_Company_Schedule_Element_Details SB
               
  WHERE D.Doc_Id=DSD.DDSD_Doc_Id              
  AND DSD.DDSD_COM_Code=SB.MCSED_COM_Code      
  AND DSD.DDSD_COM_Code=2       
  AND DSD.DDSD_SE_Code=@SE_Code      
  AND DSD.DDSD_SED_Code=SB.MCSED_SED_Code      
  AND SB.MCSED_SED_Short_Description=@SED_Code       
 GROUP BY D.Doc_Id,D.Doc_No, D.Dwg_Rev_No,                                       
 tq.Material_Quantity, D.Dwg_Category_Short_Desc, DED.DED_Description
  ORDER BY D.Doc_No

任何人都可以帮我解决这个问题......

can any one help me how to resolve this issue...

推荐答案

这是更新Total的方法。您可以根据自己的要求进行更改。

This is how to Update the Total. You can change it according to your requirements.

DECLARE @temp TABLE(ID int identity(1,1),empno int,Name varchar(50),sal numeric(18,2),total numeric(18,2))

// INSERT INTO TEMP TABLE YOUR LOGIC HERE


DECLARE @currentrow int
DECLARE @totalrows int
DECLARE @totalsum numeric(18,2)


SET @currentrow = 1
SET @totalsum = 0
SELECT @totalrows = COUNT(1) from @temp


WHILE(@currentrow <= @totalrows)
BEGIN


SELECT @totalsum = @totalsum + sal FROM @temp WHERE ID = @currentrow


UPDATE @temp
SET total = @totalsum
WHERE ID = @currentrow


SET @currentrow = currentrow + 1;


END

这篇关于SP中的分组问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！