如何在不使用声明变量的情况下编写单选 [英] How do i write single select without using declared variables

问题描述

如何在不使用声明变量的情况下编写单选。我们无法使用更多变量可能会导致性能降低。使用Stuff Function。我想要相同的查询但是使用Stuff Function我将如何做.no声明变量。

  SET   NOCOUNT   ON ; 
  DECLARE   @ CountryPL   varchar  （ 200 ）
  DECLARE   @ StreetNumber   varchar （ 200 ）
  DECLARE   @ StreetName   varchar （ 200  ）
  DECLARE   @ City   varchar （ 200 ）
  SET   @ CountryPL  =  NULL  
  SET   @ StreetNumber  =  NULL  
  SET   @ StreetName  =  NULL  
  SELECT   @ CountryPL  =  COALESCE （ @ CountryPL  + ' ，'，' '）+ CountryPL， @ StreetNumber  =  COALESCE （ @ StreetNumber  + ' ，' ，' '）+ StreetNumber，
  @ StreetName  =  COALESCE （ @ StreetName  + '  ，'，' '）+ StreetName， @ City  =  COALESCE （ @ City  + ' ，'，' '）+ City 
  FROM 学生S 
 
 内部 加入人P  On  S.PersonID = P.PersonID 
 内部  JOIN  PersonAddress PA  ON  P.PersonID = PA.PersonID 
  INNER   JOIN 地址A  ON  A.AddressID = PA.AddressID 
 其中​​ S.StudentID =  @ StudentID  
 
  SELECT   @ CountryPL ， @ StreetNumber ， @ StreetName ， @ City  
  END  

我的尝试：



我试过这个（使用STUFF功能）：

  SET   NOCOUNT   ON ; 
  DECLARE   @ PersonID   INT  
  SELECT  @ PersonID = PersonID 来自 dbo.Student  WHERE  StudentID = @ studentid 
 组 按 StudentID 
 < span class =code-keyword> SELECT  
  @ studentid   AS  StudentID ，
 STUFF（（ SELECT  ' ，'  + A. [StreetNumber] 
  FROM  dbo.PersonAddress PA 
  INNER   JOIN  dbo。[地址] A 
  ON  PA.AddressID = A.AddressID 
  WHERE  PA.PersonID = p.PersonID 
  FOR  xml PATH（' '）， TYPE ）
 .value（' 。'，'  NVARCHAR（MAX）'）， 1 ， 1 ，' '），
 STUFF（（ SELECT  
 ' ，' + 
 A. [StreetName] 
  FROM  dbo.PersonAddress PA 
  INNER   JOIN  dbo。[Address] A 
  ON  PA.AddressID = A.AddressID 
  WHERE  PA.PersonID = P.PersonID 
  FOR  xml PATH（' '），< span class =code-keyword> TYPE ）
 .value（' 。'，'  NVARCHAR（MAX）'）， 1 ， 1 ，' '），
 STUFF（（ SELECT  
 ' ，' + 
 A. [城市] 
  FROM  dbo.PersonAddress PA 
  INNER   JOIN  dbo。[地址] A 
  ON  PA.AddressID = A.AddressID 
  WHERE  PA.PersonID = P.PersonID 
  FOR  xml PATH （' '）， TYPE ）
 .value（' 。'，'  NVARCHAR（MAX）'）， 1 ， 1 ，' '）
  FROM  dbo.Person S 
  WHERE  P.PersonID =  @ PersonID  
 
  END  
 

解决方案

如果您想使用STUFF（）函数来连接行，请阅读这些文章：

TSQL - 使用FOR XML PATH（）连接行Sql And Me [ ^ ]

字符串连接的STUFF和FOR XML PATH - SQL Server存储库 [ ^ ]

在Transact-SQL中连接行值 [ ^ ]

  GO  
 
  SET   ANSI_NULLS   ON  
 < span class =code-keyword> GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
 
 
  create   PROCEDURE  [dbo]。[RajeshStudentAddressesByStudentID]  @ studentid   int  
  AS  
  BEGIN  
 
 
   -    EXEC dbo.uspSelectStudentAddressesByStudentID 1  
 
  SET   NOCOUNT   ON  ; 
  DECLARE   @ PersonID   INT  
  SELECT  @ PersonID = PersonID 来自 dbo.Student  WHERE  StudentID = @ studentid 
 组 按 StudentID 
 < span class =code-keyword> SELECT  
  @ studentid   AS  StudentID ，
 
 STUFF（（ SELECT  
 ' ，' + 
 CAST（A. [AddressID]  AS   VARCHAR ）
  FROM  dbo.PersonAddress SA 
  INNER   JOIN  dbo。[地址] A 
  ON  SA.AddressID = A.AddressID 
  WHERE  SA.PersonID = S.PersonID 
  FOR  xml PATH（' '），  TYPE ）
 .value（' 。' ，'  NVARCHAR（MAX）'）， 1 ， 1 ，' '），
 
 STUFF（（ SELECT  
 ' ，' + 
 A. [StreetNumber] 
 
  FROM  dbo .PersonAddress SA 
  INNER   JOIN  dbo。[地址] A 
  ON  SA.AddressID = A.AddressID 
  WHERE  SA.PersonID = S.PersonID 
  FOR  xml PATH（' '）， TYPE ）
 .value（' 。'， '  NVARCHAR（MAX）'）， 1 ， 1 ，' '）， 
 STUFF（（ SELECT  
 ' ，' + 
 A. [StreetName] 
  FROM  dbo.PersonAddress SA 
  INNER   JOIN  dbo。[地址] A 
  ON  SA .AddressID = A.AddressID 
  WHERE  SA.PersonID = S.PersonID 
  FOR  xml PATH（' '）， TYPE ） 
 .value（' 。'，'   NVARCHAR（MAX）'）， 1 ， 1 ，' '），
 STUFF（（ SELECT  
 ' ，' + 
 A. [城市] 
  FROM  dbo.PersonAddress SA 
  INNER   JOIN  dbo。[地址] A 
  ON  SA.AddressID = A.AddressID 
 < span class = code-keyword> WHERE  SA.PersonID = S.PersonID 
  FOR  xml PATH（'  '）， TYPE ）
 .value（' 。'，'  NVARCHAR（MAX）'）， 1 ， 1 ，' '）
  FROM  dbo.Person S 
  WHERE  S.PersonID =  @ PersonID  
 
  END  

How do i write Single Select without using declared variables.We cant use more variables may cause reducing performance.Using Stuff Function . I Want same query but using Stuff Function how i will do .no Declare Variable .

 SET NOCOUNT ON;
DECLARE @CountryPL varchar(200)
DECLARE @StreetNumber varchar(200)
DECLARE @StreetName varchar(200)
DECLARE @City varchar(200)
SET @CountryPL = NULL
SET @StreetNumber = NULL
SET @StreetName = NULL
  SELECT @CountryPL =  COALESCE(@CountryPL + ',','')+ CountryPL ,@StreetNumber =  COALESCE(@StreetNumber + ',','')+ StreetNumber ,
 @StreetName =  COALESCE(@StreetName + ',','')+ StreetName,@City =  COALESCE(@City + ',','')+ City
FROM Student S

  Inner Join Person P On S.PersonID=P.PersonID
   Inner JOIN PersonAddress PA ON P.PersonID = PA.PersonID 
 INNER JOIN  Address A ON A.AddressID = PA.AddressID
   Where   S.StudentID =  @StudentID 

	SELECT @CountryPL,@StreetNumber,@StreetName,@City
   END

What I have tried:

I tried this one (using STUFF function):

 SET NOCOUNT ON;
 DECLARE @PersonID INT
SELECT @PersonID=PersonID from dbo.Student WHERE StudentID=@studentid
 Group By StudentID
SELECT
   @studentid AS StudentID,
       STUFF((SELECT ',' + A.[StreetNumber]
          FROM dbo.PersonAddress PA
   INNER JOIN dbo.[Address] A
     ON PA.AddressID = A.AddressID
   WHERE PA.PersonID = p.PersonID
   FOR xml PATH (''), TYPE)
   .value('.', 'NVARCHAR(MAX)'), 1, 1, '') ,
   STUFF((SELECT
     ',' +
       A.[StreetName]
    FROM dbo.PersonAddress PA
   INNER JOIN dbo.[Address] A
     ON PA.AddressID = A.AddressID
   WHERE PA.PersonID = P.PersonID
   FOR xml PATH (''), TYPE)
   .value('.', 'NVARCHAR(MAX)'), 1, 1, '') ,
   STUFF((SELECT
     ',' +
     A.[City]
    FROM dbo.PersonAddress PA
   INNER JOIN dbo.[Address] A
     ON PA.AddressID = A.AddressID
   WHERE PA.PersonID = P.PersonID
   FOR xml PATH (''), TYPE)
   .value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    FROM dbo.Person S
 WHERE P.PersonID = @PersonID

 END

解决方案

If you would like to use STUFF() function to concatenate rows, please read these articles:
TSQL - Concatenate Rows using FOR XML PATH() | Sql And Me[^]
STUFF AND FOR XML PATH for String Concatenation - SQL Server Repository[^]
Concatenating Row Values in Transact-SQL[^]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE   [dbo].[RajeshStudentAddressesByStudentID]   @studentid int
AS
BEGIN


--EXEC dbo.uspSelectStudentAddressesByStudentID 1

  SET NOCOUNT ON;
  DECLARE @PersonID INT
 SELECT @PersonID=PersonID from dbo.Student WHERE StudentID=@studentid
  Group By StudentID
 SELECT
    @studentid AS StudentID,

	 STUFF((SELECT 
      ',' + 
	  CAST(A.[AddressID] AS VARCHAR)
	  FROM dbo.PersonAddress SA
    INNER JOIN dbo.[Address] A
      ON SA.AddressID = A.AddressID
    WHERE SA.PersonID = S.PersonID 
    FOR xml PATH (''), TYPE)
    .value('.', 'NVARCHAR(MAX)'), 1, 1, '') ,

	STUFF((SELECT 
      ',' + 
        A.[StreetNumber]

    FROM dbo.PersonAddress SA
    INNER JOIN dbo.[Address] A
      ON SA.AddressID = A.AddressID
    WHERE SA.PersonID = S.PersonID 
    FOR xml PATH (''), TYPE)
    .value('.', 'NVARCHAR(MAX)'), 1, 1, '') ,
	STUFF((SELECT 
      ',' + 
        A.[StreetName]
     FROM dbo.PersonAddress SA
    INNER JOIN dbo.[Address] A
      ON SA.AddressID = A.AddressID
    WHERE SA.PersonID = S.PersonID 
    FOR xml PATH (''), TYPE)
    .value('.', 'NVARCHAR(MAX)'), 1, 1, '') ,
	STUFF((SELECT
      ',' + 
      A.[City]
     FROM dbo.PersonAddress SA
    INNER JOIN dbo.[Address] A
      ON SA.AddressID = A.AddressID
    WHERE SA.PersonID = S.PersonID 
    FOR xml PATH (''), TYPE)
    .value('.', 'NVARCHAR(MAX)'), 1, 1, '')
	 FROM dbo.Person S
  WHERE S.PersonID = @PersonID

  END

这篇关于如何在不使用声明变量的情况下编写单选的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！