SQL SERVER 将多条记录转化为一条输出

最新推荐文章于 2025-01-05 10:50:57 发布

原创最新推荐文章于 2025-01-05 10:50:57 发布 · 923 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

sql 专栏收录该内容

30 篇文章

订阅专栏

创建了一个名为[dbo].[ufn_GetPromotionTimeByClientID]的SQL函数，该函数接受两个参数@inClient和@intuploadID。函数通过游标遍历Web_tblInitOrderUploadDetails等相关表，获取指定客户ID和上传ID对应的促销代码及其描述，并统计每个促销的出现次数。结果以'促销描述 [次数]'的格式组合成一个字符串返回。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE FUNCTION [dbo].[ufn_GetPromotionTimeByClientID] (@inClient int,@intuploadID int) Returns nvarchar(1000)  AS

BEGIN

Declare @RetRecordList nvarchar(1000)

Declare @PromotionCode nvarchar(30)

Declare @PromotionDesci nvarchar(50)

Declare @Cnt int

Declare @LastItem nvarchar(30)

Declare RecCursor CURSOR FOR



Select ph.PromotionCode, ph.PromotionDesci, Count(*) NoOfTimes

From Web_tblInitOrderUploadDetails iud 

Join sales_tblOrderH oh on iud.ECINVID = oh.OrderHID 

Left Join Web_tblInitUploadPromotion iup on iud.InitOrderUploadID = iup.UploadID 

Left Join sales_tblPromotionHeader ph on iup.PromotionID = ph.PromotionID 

Where iup.PromotionID IS NOT NULL And oh.ClientID =@inClient

and  ph.PromotionID  in(select PromotionID  from Web_tblInitUploadPromotion where UploadID =@intuploadID  )

Group By ph.PromotionCode, ph.PromotionDesci

Set @LastItem  = ''

Open RecCursor 

	Fetch Next From RecCursor Into @PromotionCode, @PromotionDesci,@Cnt

	Set @RetRecordList = RTrim(@PromotionDesci) + ' [' + Convert(varchar, @Cnt) + '],'

	

	

	WHILE @@FETCH_STATUS = 0

	BEGIN

		FETCH NEXT FROM RecCursor INTO @PromotionCode, @PromotionDesci,@Cnt

			BEGIN

				IF @@FETCH_STATUS = 0

					Set @RetRecordList = @RetRecordList + RTrim(@PromotionDesci) + ' [' + Convert(varchar, @Cnt) + '],'

			END 

	END

Close RecCursor

Deallocate RecCursor

--PRINT @RetRecordList

Return @RetRecordList 

END