如何在cte查询中记录记录？ [英] How count record in cte query?

问题描述

我需要从cte查询中计算记录，



我做了这样，

Hi,

I need to count the record from the cte query,

I did this way,

with cte as (

select distinct cust.Name as CompanyName,ROW_NUMBER() OVER(order by (InvoiceDate) Desc)  as RowNumber,DATEDIFF(DAY  ,inv.InvoicedueDate ,getdate()) DaysTillDate ,                              
inv.InvoiceId,dbo.fn_GetOrderFromInv(inv.InvoiceId) as OrderNumber ,inv.InvoiceDate, (inv.Prefix + '' + Convert(varchar,inv.InvoiceNo)) as InvoiceNo  ,Del.DeliveryNo,Hcomp.Name as HandlingCompany,                              
CONVERT(decimal(18,3), inv.InvoiceValue) as Base_Invoiceamount,CONVERT(decimal(18,2), inv.Currency_InvoiceValue ) as Cust_Invoiceamount,inv.InvoiceDueDate,             
Isnull(((inv.InvoiceValue) - (select SUM(Amount) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),inv.InvoiceValue) as Base_Outstanding,
Convert(decimal(12,2), Isnull(((inv.Currency_InvoiceValue) - (select (SUM(Currency_Amount) ) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),(inv.Currency_InvoiceValue))) as Cust_Outstanding,
inv.InvoiceTypeId,        
dbo.fn_GetinvoiceCNType(inv.InvoiceTypeId,493 ,inv.InvoiceId,inv.InvoiceValue) Note        
from genInvoices inv                              
left join genInvoiceOrders invord on invord.InvoiceId =inv.InvoiceId                        
left join genOrders ord on ord.OrderId =invord.OrderId                               
left join genCompanies cust on ord.CompanyId = cust.CompanyId                              
left join genDelivery Del on Del.DeliveryId = inv.DeliveryId                              
left join genCompanies Hcomp on Hcomp.CompanyID = inv.HandCompanyId  
where ( inv.StatusId=307 ) and ( inv.InvoiceTypeId = 3 or inv.InvoiceTypeId = 1 ) and ((isnull(inv.IsInvoiceDeliver,0)=1 and isnull(inv.IsEinvoice,0)=1) or (isnull(inv.IsDeliverbyPost,0)=1) or (isnull(inv.IsEinvoice,0)=0 and isnull(inv.IsDeliverbyPost,0)=0) )  and inv.CustCompanyId = 45084 and  1=1 and  1=1)
, cte2 as (select count(*) as cnt from cte)
select  RowNumber,cnt, CompanyName,DaysTillDate,InvoiceId,OrderNumber, dbo.fn_globaldateformat(InvoiceDate,493) as InvoiceDate,InvoiceDate as InvoiceDatesort,InvoiceNo,DeliveryNo,
HandlingCompany,Base_Invoiceamount,Cust_Invoiceamount,dbo.fn_globaldateformat(InvoiceDueDate,493) as InvoiceDueDate,InvoiceDueDate as InvoiceDueDatesort,
Base_Outstanding,Cust_Outstanding,InvoiceTypeId,Note from cte,cte2 where RowNumber between  1 and 20

此查询需要700毫秒才能执行，



但是当我删除另一个cte（即删除count（））比执行只需要250ms，



删除后查询查询是，

this query takes 700ms to execute,

but when I remove another cte(i.e. removing count()) than it takes only 250ms to exeute,

after removing Count the query is,

with cte as (

select distinct cust.Name as CompanyName,ROW_NUMBER() OVER(order by (InvoiceDate) Desc)  as RowNumber,DATEDIFF(DAY  ,inv.InvoicedueDate ,getdate()) DaysTillDate ,                              
inv.InvoiceId,dbo.fn_GetOrderFromInv(inv.InvoiceId) as OrderNumber ,inv.InvoiceDate, (inv.Prefix + '' + Convert(varchar,inv.InvoiceNo)) as InvoiceNo  ,Del.DeliveryNo,Hcomp.Name as HandlingCompany,                              
CONVERT(decimal(18,3), inv.InvoiceValue) as Base_Invoiceamount,CONVERT(decimal(18,2), inv.Currency_InvoiceValue ) as Cust_Invoiceamount,inv.InvoiceDueDate,             
Isnull(((inv.InvoiceValue) - (select SUM(Amount) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),inv.InvoiceValue) as Base_Outstanding,
Convert(decimal(12,2), Isnull(((inv.Currency_InvoiceValue) - (select (SUM(Currency_Amount) ) from genPaymentRegistration where InvoiceId = inv.InvoiceId )),(inv.Currency_InvoiceValue))) as Cust_Outstanding,
inv.InvoiceTypeId,        
dbo.fn_GetinvoiceCNType(inv.InvoiceTypeId,493 ,inv.InvoiceId,inv.InvoiceValue) Note        
from genInvoices inv                              
left join genInvoiceOrders invord on invord.InvoiceId =inv.InvoiceId                        
left join genOrders ord on ord.OrderId =invord.OrderId                               
left join genCompanies cust on ord.CompanyId = cust.CompanyId                              
left join genDelivery Del on Del.DeliveryId = inv.DeliveryId                              
left join genCompanies Hcomp on Hcomp.CompanyID = inv.HandCompanyId  
where ( inv.StatusId=307 ) and ( inv.InvoiceTypeId = 3 or inv.InvoiceTypeId = 1 ) and ((isnull(inv.IsInvoiceDeliver,0)=1 and isnull(inv.IsEinvoice,0)=1) or (isnull(inv.IsDeliverbyPost,0)=1) or (isnull(inv.IsEinvoice,0)=0 and isnull(inv.IsDeliverbyPost,0)=0) )  and inv.CustCompanyId = 45084 and  1=1 and  1=1)

select  RowNumber, CompanyName,DaysTillDate,InvoiceId,OrderNumber, dbo.fn_globaldateformat(InvoiceDate,493) as InvoiceDate,InvoiceDate as InvoiceDatesort,InvoiceNo,DeliveryNo,
HandlingCompany,Base_Invoiceamount,Cust_Invoiceamount,dbo.fn_globaldateformat(InvoiceDueDate,493) as InvoiceDueDate,InvoiceDueDate as InvoiceDueDatesort,
Base_Outstanding,Cust_Outstanding,InvoiceTypeId,Note from cte where RowNumber between  1 and 20

我唯一的问题是如何从cte查询中计算记录？



谢谢。

My only question is that how can I count record from cte query?

Thanks.

推荐答案

如果你有所有索引，那么查询就像它看起来一样复杂如果你不想每500毫秒运行一次，那么700毫秒就不那么多了。



如我所见，这是一个生成的查询。我建议您尝试优化它，不要添加额外的约束，例如1 = 1，并将所有转换移动到最外层。你真的需要那些ISNULL电话吗？



如果你有能力将结果存放在临时表中一段时间​​你也应该考虑这种方法。

If you have all indexes in place, and the query is as complicated as it seems to be, 700ms is not so much, if you don''t want to run it every 500ms :)

As I see, this is a generated query. I suggest you try to optimize it, by not adding extra constraints like 1=1, and moving all conversions to the most outher level. Do you really need those ISNULL calls?

If you can afford to store the result in a temporary table for a while you should consider this approach too.

这篇关于如何在cte查询中记录记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！