如何在sql Query中使用Rollup函数？ [英] How to use with Rollup Function in sql Query?

问题描述

大家好，

这里我用汇总来设计一个sql查询。

像这样查询..

Hello everyone,
Here i have desined a sql query using with rollup .
Query like this..

select ROW_NUMBER() OVER (ORDER BY src.INVOICENO ) SRNO,
 ISNULL(CONVERT(varchar,Date,103),'')DATE,ISNULL(INVOICENO,'')INVOICENO,ISNULL(CustName,'')PARTY_NAME,ISNULL(DeliveryAddr,'')DeliveryAddr,ISNULL(Grade,'')GRADE,ISNULL(Details,'GRANDTOTAL')DETAILS,QTY,ISNULL(RATE,0.0000)RATE,ASSESSABLE_Value,BED,EDCESS,HECESS,VAT,RoundOff,TOTALAMOUNT

from
(
  SELECT 
  ExDcDt AS Date,ExDcNo AS INVOICENO,CustName,DeliverAdd1 As DeliveryAddr,ItemDesc AS Grade,itemDescription as Details, SUM (Qty) QTY,RATE,SUM(Amount) AS ASSESSABLE_Value,SUM(BasicDuty) AS BED,SUM(ExEdu) AS EDCESS,SUM(ExHEC) AS HECESS,SUM(Vat) AS VAT,SUM(RoundOff) AS RoundOff,SUM(TotalAmt) AS TOTALAMOUNT  from SalesMaster where( ExDcDt>=CONVERT(VARCHAR,'2015-DEC-11', 103) and ExDcDt<=CONVERT(VARCHAR,'2015-DEC-17', 103))
  GROUP BY ExDcNo, ExDcDt,DeliverAdd1,CustName,ItemDesc ,itemDescription,Rate with rollup 
) src
where Rate  is not null
or
(
  Date is null 
  and INVOICENO is null
  --and CustName is null
)

推荐答案

您需要订购查询，但当然问题就变成了订购的问题它来了！



您可以在 ORDER BY CASE $ c>实现此目的的条款，例如

You need to order your query, but of course the problem becomes what to order it by!

You can use CASE in an ORDER BY clause to achieve this e.g.

ORDER BY CASE WHEN INVOICENO <> 0 THEN INVOICENO ELSE ASSESSABLE_VALUE END

我选择了这些特定的东西，因为我发现 INVOICENO 在我的测试数据中，对于Grand Total行是0 - 你可能需要稍微调整它。



注意，因为这个重新排序 SRNO 从2开始，进入记录数然后显示1.再次显示1到行数的最简单方法是使用

I chose this specific things because I observed that INVOICENO on my test data was 0 for the Grand Total row - you might need to fiddle with it a bit.

Note that because of this re-ordering the values for SRNO start at 2, go through to the number of records and then show 1. The easiest way to get this showing 1 through number of rows again is to use

select ROW_NUMBER() OVER (ORDER BY src.INVOICENO ) - 1 SRNO,

。如果你这样做，那么Grand Total就有 SRNO = 0



[OP评论后编辑]

我使用了你的查询的简化版本来演示几种获取行号以反映新订单的方法，并且我已经删除了纯粹用于此演示的where子句

. If you do this then the Grand Total with have SRNO = 0


I've used a cut-down version of your query to demonstrate a couple of ways of getting the row number to reflect the new order, and I've stripped out the where clauses purely for this demo

WITH CTE AS 
(
	SELECT ROW_NUMBER() OVER (ORDER BY INVOICENO ) SRNO, ISNULL(INVOICENO,'') INVOICENO, 
	ASSESSABLE_Value,TOTALAMOUNT --,ETC
 	from
	(
	  SELECT 
	  ExDcNo AS INVOICENO,SUM(Amount) AS ASSESSABLE_Value,SUM(TotalAmt) AS TOTALAMOUNT  
	  from SalesMaster 
	  GROUP BY ExDcNo with rollup 
	) SRC
),
CTE2 AS 
(
	SELECT MAX(ExDcNo) + 1 ino FROM SalesMaster -- WHERE etc
	-- may have to fiddle with this bit if ExDcNo is not an number
)
select CASE WHEN CTE.SRNO = 1 THEN CTE2.ino ELSE CTE.SRNO - 1 END AS SRNO,
INVOICENO, ASSESSABLE_Value, TOTALAMOUNT -- etc 
FROM CTE, CTE2
ORDER BY 1


-- OR 


DECLARE @GT_INVOICE int = (SELECT MAX(ExDcNo) FROM SalesMaster) + 1 -- insert the where etc
-- again if ExDcNo is not a number find a way of increasing this value past the last one used
;WITH CTE AS 
(
	SELECT ROW_NUMBER() OVER (ORDER BY INVOICENO ) SRNO, ISNULL(INVOICENO,'') INVOICENO, 
	ASSESSABLE_Value,TOTALAMOUNT --,ETC
 	from
	(
	  SELECT 
	  ExDcNo AS INVOICENO,SUM(Amount) AS ASSESSABLE_Value,SUM(TotalAmt) AS TOTALAMOUNT  
	  from SalesMaster 
	  GROUP BY ExDcNo with rollup 
	) SRC
)
select CASE WHEN CTE.SRNO = 1 THEN @GT_INVOICE ELSE SRNO - 1 END AS SRNO,
INVOICENO, ASSESSABLE_Value, TOTALAMOUNT -- etc 
FROM CTE
ORDER BY 1

这篇关于如何在sql Query中使用Rollup函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！