将查询中的选定值分配给局部变量 [英] Assign selected value in a query to a local variable

问题描述

我想知道这是否可能..

Hi,I want to know if this is possible..

SELECT  TM.ticketnumber
	,TM.status 				  
	,TM.priority 
	,TD.createddate
	,TD.subject
	,TD.message
	,PU.Name
	,TM.createduserid AS createduserid
	,TC.name AS CategoryName
	,TC.id AS CategoryID
	,PU.Email AS CreatedUserEmail
	,PU1.Email AS AssignedUserEmail
	,TM.assigneduserid AS AssignedTo
	,TA.filename AS Attachment
	,TD.isprivatenote AS PrivateNote
	,TS.emailaddress AS managerMail
	
FROM  ticketmaster TM 
INNER JOIN ticketdetail TD ON TM.id=TD.ticketmasterid 
AND TD.createddate=(SELECT MIN(ticketdetail.createddate) 
FROM ticketdetail  WHERE ticketdetail.ticketmasterid=TM.id)
LEFT JOIN PortalUser PU ON PU.ID=TM.createduserid AND PU.Status=1
LEFT JOIN PortalUser PU1 ON PU1.ID=TM.assigneduserid AND PU1.Status=1
INNER JOIN ticketcategory TC ON TC.id=TM.categoryid AND TC.isactive=1 
LEFT JOIN ticketattachment TA ON TA.ticketdetailid=TD.id
INNER JOIN ticketsettings TS ON TS.DistributorID=@CompanyID OR TS.ResellerID=@CompanyID 
OR TS.BusinessID=@CompanyID OR TS.BusinessSiteID=@CompanyID
WHERE TM.id=@TicketID


SET @assigneduserid=(the assigneduserid from above select)

推荐答案

SET @assigneduserid = (select top(1) P.assigneduserid
(SELECT  TM.ticketnumber
	,TM.status 				  
	,TM.priority 
	,TD.createddate
	,TD.subject
	,TD.message
	,PU.Name
	,TM.createduserid AS createduserid
	,TC.name AS CategoryName
	,TC.id AS CategoryID
	,PU.Email AS CreatedUserEmail
	,PU1.Email AS AssignedUserEmail
	,TM.assigneduserid AS AssignedTo
	,TA.filename AS Attachment
	,TD.isprivatenote AS PrivateNote
	,TS.emailaddress AS managerMail
	
FROM  ticketmaster TM 
INNER JOIN ticketdetail TD ON TM.id=TD.ticketmasterid 
AND TD.createddate=(SELECT MIN(ticketdetail.createddate) 
FROM ticketdetail  WHERE ticketdetail.ticketmasterid=TM.id)
LEFT JOIN PortalUser PU ON PU.ID=TM.createduserid AND PU.Status=1
LEFT JOIN PortalUser PU1 ON PU1.ID=TM.assigneduserid AND PU1.Status=1
INNER JOIN ticketcategory TC ON TC.id=TM.categoryid AND TC.isactive=1 
LEFT JOIN ticketattachment TA ON TA.ticketdetailid=TD.id
INNER JOIN ticketsettings TS ON TS.DistributorID=@CompanyID OR TS.ResellerID=@CompanyID 
OR TS.BusinessID=@CompanyID OR TS.BusinessSiteID=@CompanyID
WHERE TM.id=@TicketID) as P);

这篇关于将查询中的选定值分配给局部变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！