在我的SQL select语句中汇总并添加Summary Rows

本文介绍了在我的SQL select语句中汇总并添加Summary Rows的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我的查询是用于汇总表格中的总数，但我需要一些帮助来自定义查询以生成我正在寻找的结果。到目前为止，这是我所拥有的查询，但它缺少2行结算股票和差异这些是计算行。我如何提出结算股票和差异  SELECT TxDate，TxStage，ItemCode，ItemName，SupplierWeight，CustomerOrderedWeight，DecanterWeight， SupplierWeight + CustomerOrderedWeight + DecanterWeight AS Quantity， CASE WHEN TxStage ='BITUTAINER IN'THEN'Inbound' WHEN TxStage ='BITUTAINER OUT'那么'出境'当TxStage ='STOCK TAKE ON'那么'股票买入'当TxStage ='BOX接受'那么'实物股票' WHEN TxStage ='DECANTER IN'那么'Decanter' END作为TypeOfTransaction，情况当TxStage ='BITUTAINER IN'那么'3'当TxStage ='BITUTAINER OUT'时那么'5'当TxStage ='STOCK TAKE ON'那么'1'当TxStage ='BOX TAKE ON'那么'6'当TxStage ='DECANTER''那么'4 ' END AS DisplayOrder 来自dbo.Transactions_Transactions _Main 上图显示了我在上面运行查询时得到的结果 如果你认为你可以实现它解决方案 看看这是否适合你 WITH cte AS （ SELECT TxDate，TxStage，ItemCode，ItemName，SupplierWeight，CustomerOrderedWeight，DecanterWeight， SupplierWeight + CustomerOrderedWeight + DecanterWeight AS数量， 情况当TxStage ='BITUTAINER IN'那么'入境'  当TxStage ='BITUTAINER OUT'那么'Outbound' 当TxStage ='STOCK TAKE ON'那么'股票上涨'¥b $ b当TxStage ='BOX TAKE ON'那么'实体股票'为 WHEN  TxStage ='DECANTER IN'那么'Decanter' 结束作为TypeOfTransaction， 案例当TxStage ='BITUTAINER''那么'3'  当TxStage ='BITUTAINER OUT'然后'5' 当TxStage ='STOCK TAKE ON'那么'1' 当TxStage ='BOX TAKE ON'那么'6'时 WHEN  TxStage ='DECANTER IN'THEN'4' $ END作为DisplayOrder 来自dbo.Transactions_Transactions_Main  ）  SELECT *，variance = closing_stocks +' - '+ TypeOfTransaction ='Outbound'时的情况 然后TypeOfTransaction 否则''结束  FROM  （ SELECT *，closing_stocks =当TypeOfTransaction ='Stock Take On'时的情况'然后TypeOfTransaction + '+'其他''  结束+案例，当TypeOfTransaction ='入境' 然后TypeOfTransaction +'+'else''结束+  案例何时TypeOfTransaction ='Decanter' 然后TypeOfTransaction +'+'else''结束    + CaseOfTransaction ='Outbound'时的情况 然后TypeOfTransaction +'+'else''结束，   FROM cte ）AS I have my query that I am using to summarize the totals in my table but I need some help customizing the query to produce the results that I am looking for. So far this is the query That I have , but it is missing 2 Rows the closing stock and the Variance which are calculated rows. How do I come up with the Closing Stock and the Variance SELECT TxDate, TxStage, ItemCode, ItemName, SupplierWeight, CustomerOrderedWeight,DecanterWeight,SupplierWeight+CustomerOrderedWeight+DecanterWeight AS Quantity,CASE WHEN TxStage='BITUTAINER IN' THEN 'Inbound' WHEN TxStage='BITUTAINER OUT' THEN 'Outbound'WHEN TxStage='STOCK TAKE ON' THEN 'Stock Take On'WHEN TxStage='BOX TAKE ON' THEN 'Physical Stock'WHEN TxStage='DECANTER IN' THEN 'Decanter'END AS TypeOfTransaction,CASE WHEN TxStage='BITUTAINER IN' THEN '3' WHEN TxStage='BITUTAINER OUT' THEN '5'WHEN TxStage='STOCK TAKE ON' THEN '1'WHEN TxStage='BOX TAKE ON' THEN '6'WHEN TxStage='DECANTER IN' THEN '4'END AS DisplayOrderFROM dbo.Transactions_Transactions_Main The image above shows the result I get when I run the query aboveIf you think it you can achieve it 解决方案 See if this works for youWITH cteAS(SELECT TxDate, TxStage, ItemCode, ItemName, SupplierWeight, CustomerOrderedWeight,DecanterWeight,SupplierWeight+CustomerOrderedWeight+DecanterWeight AS Quantity,CASE WHEN TxStage='BITUTAINER IN' THEN 'Inbound' WHEN TxStage='BITUTAINER OUT' THEN 'Outbound' WHEN TxStage='STOCK TAKE ON' THEN 'Stock Take On'WHEN TxStage='BOX TAKE ON' THEN 'Physical Stock'WHEN  TxStage='DECANTER IN' THEN 'Decanter'END AS TypeOfTransaction,CASE WHEN TxStage='BITUTAINER IN' THEN '3' WHEN TxStage='BITUTAINER OUT' THEN '5' WHEN TxStage='STOCK TAKE ON' THEN '1'WHEN TxStage='BOX TAKE ON' THEN '6'WHEN  TxStage='DECANTER IN' THEN '4'END AS DisplayOrderFROM dbo.Transactions_Transactions_Main ) SELECT * ,variance =closing_stocks+ '-'+case when TypeOfTransaction ='Outbound'  then TypeOfTransaction  else '' end FROM (SELECT * , closing_stocks =case when TypeOfTransaction ='Stock Take On'  then TypeOfTransaction+'+' else '' end + case when TypeOfTransaction ='Inbound'  then TypeOfTransaction +'+' else '' end + case when TypeOfTransaction ='Decanter'  then TypeOfTransaction +'+' else '' end  +case when TypeOfTransaction ='Outbound'  then TypeOfTransaction +'+' else '' end ,  FROM cte) AS der 这篇关于在我的SQL select语句中汇总并添加Summary Rows的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！