带变量的CTE查询中的计数

Question

我有一个查询，想要计算其他表中所有相关ids的数量。我正在使用CTE，我也在赋值变量。

当我打印时，我得到了所有的I，但是我如何计算相同的I呢？我的意思是，如果我有一个id“254asser”3次，它应该显示3

以下是查询：

;WITH cte AS (
select q.fecha, '31' as cdocu, q.Cotizacion__c, cli.codcli, 
q."name", q.RUC__c, c.Nombre_contacto__c as atte, a.Direcci_n__c, 
q.Referencia__c, q.Requisicion__c, con.mone, q.T_Cambio__c, q.TotalPrice, 
(q.TotalPrice*q.IGV__c)/100 as toti,(q.TotalPrice+ 
(q.TotalPrice*q.IGV__c)/100) as totn, '0' as flag, ven.codven, cd.codcdv, 
'00' as codscc,q.fecha as fven ,q.Validez_por__c,'0' as selchk,'0' as 
estado,q.Descripcion__c,'0' as word,'0' as frontera,'0' as 
idalias,q.T_Cambio__c as tcme,'0'as idcliente,'0' as idcontacto,'0' as 
persnt,'0'as flag_rp,'0' as origen,ti.IDdespacho,'0' as nrosal,'0' as 
iddirfis,tr.codtra,q.fecha as fecreg,ROW_NUMBER() OVER(ORDER BY 
q.Cotizacion__c) AS 'Row1', q.Id

from quote AS q
LEFT JOIN tbl01cor cor ON q.Cotizacion__c = cor.nroini
LEFT JOIN mst01cli cli ON q.RUC__c = cli.ruccli
LEFT JOIN Contact c ON q.RUC__c = c.RUC_contacto__c
LEFT JOIN Account a ON q.RUC__c = a.RUC__c
LEFT JOIN tbl01cdv cd ON q.Condicion__c = cd.nomcdv
LEFT JOIN tbl_tipo_despacho ti ON q.T_Entrega__c = ti.despacho
LEFT JOIN tbl01tra tr ON q.Transportista__c = tr.nomtra
LEFT JOIN consulta con ON q.CurrencyIsoCode = con.CurrencyIsoCode
LEFT JOIN tbl01ven ven ON q.Vendedor__c = ven.nomven

select @fecha = cte.fecha, @cdocu = cte.cdocu, @ndocu = cte.Cotizacion__c, @codcli = cte.codcli, @nomcli = cte."name", @ruccli = cte.RUC__c, @atte = cte.atte,
@dirent = cte.Direcci_n__c, @nrefe = cte.Referencia__c, @requ = cte.Requisicion__c, @mone = cte.mone, @tcam = cte.T_Cambio__c, @tota = cte.TotalPrice, @toti = cte.toti,
@totn = cte.totn, @flag = cte.flag, @codven = cte.codven, @codcdv = cte.codcdv,@codscc = cte.codscc,@fven = cte.fven,  @dura =cte.Validez_por__c, @selchk = cte.selchk,
@estado = cte.estado, @obsere = cte.Descripcion__c, @word= cte.word, @frontera = cte.frontera,@idalias = cte.idalias,@tcme = cte.tcme, @idcliente = cte.idcliente,
@idcontacto = cte.idcontacto, @persnt = cte.persnt, @flag_rp= cte.flag_rp, @origen = cte.origen,@tipent = cte.IDdespacho, @nrosal = cte.nrosal,
@iddirfis = cte.iddirfis, @CodTra = cte.codtra, @fecreg = cte.fecreg, @ID = cte.Id
FROM cte
--WHERE cte.fecha >= dateadd(day,datediff(day,0,GETDATE()),0)
WHERE Row1 = @COUNTER +1

SET @ITEM = (Select COUNT(*) from QuoteLineItem where Id = @ID)
PRINT @ITEM

我尝试创建一个新的select，如下所示：

SET @ITEM = (Select COUNT(*) from QuoteLineItem where Id = @ID)

但它只计算所有的项，我需要显示每个it的计数。我怎么才能做到这一点？

Answer 1

我用下面的代码修复了它：

select count(QuoteId) from QuoteLineItem Where QuoteId = q.Id Group by QuoteId