sql-server - 使用变量在 CTE 查询中计数

问题描述

我有一个查询，想计算其他表中的所有相关 id。我正在使用 CTE，并且我还分配了变量。

当我打印时，我得到了所有的 ID，但我如何计算相同的 ID？我的意思是如果我有一个 id "254aswer" 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

我试图创建一个这样的新选择：

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

但它只计算所有项目，我需要显示每个 ID 的计数。我怎么能得到那个？

标签： sql-servertsqlcountcommon-table-expression

sql-server - 使用变量在 CTE 查询中计数

问题描述

解决方案

推荐阅读