sql server语言 - sql在同一列和表的不同条件下设置不同的变量

Question

我已经掌握了SQL的基础知识。但是我可能需要一些高级的帮助来解决这个问题。

我正在开发一个执行不同SQL准备语句的API，现在我正在尝试优化这些查询。

目前我的一个准备语句有多个select语句从同一个表中进行选择，但每个语句都有自己的条件，例如:

DECLARE 
  @DESCRIPTION_1 NVARCHAR(200)
, @DESCRIPTION_2 NVARCHAR(200)
, @DESCRIPTION_3 NVARCHAR(200)
SELECT @DESCRIPTION_1 = [DESCRIPTION] FROM [Objectives] WHERE [OBJECTIVE_ID] = '4E2DEA7B-025A-4958-8696-2D13EDD3E08E';
SELECT @DESCRIPTION_2 = [DESCRIPTION] FROM [Objectives] WHERE [OBJECTIVE_ID] = '81B9B7E5-A833-4C5A-9B73-CBACFB055404';
SELECT @DESCRIPTION_3 = [DESCRIPTION] FROM [Objectives] WHERE [OBJECTIVE_ID] = 'A065CF91-01C9-41A8-A326-A00402329833';

*这只是我的查询的一个浓缩版本。原来是这样的:

SELECT 
  @OBJECTIVE_1_DESCRIPTION = [dbo].[Objectives].[OBJECTIVE_DESCRIPTION]
, @OBJ1_LVL = [dbo].[Objectives].[OBJECTIVE_LEVEL]
, @OBJ1_TARGET = [dbo].[Objectives].[OBJECTIVE_COMPETION_COUNT]
, @REWARD_1_CODE = [dbo].[Objectives].[OBJECTIVE_REWARD]
FROM [dbo].[Objectives] 
WHERE [dbo].[Objectives].[OBJECTIVE_ID] = @OBJECTIVE1_ID;

是否有任何方法可以将其合并到单个SELECT语句中?

Answer 1

DECLARE 
  @DESCRIPTION_1 NVARCHAR(200)
, @DESCRIPTION_2 NVARCHAR(200)
, @DESCRIPTION_3 NVARCHAR(200)
Select @DESCRIPTION_1 = max(case when [OBJECTIVE_ID] = '4E2DEA7B-025A-4958-8696-2D13EDD3E08E' then [DESCRIPTION] else null end) 
      ,@DESCRIPTION_2 = max(case when [OBJECTIVE_ID] = '81B9B7E5-A833-4C5A-9B73-CBACFB055404' then [DESCRIPTION] else null end)      
      ,@DESCRIPTION_3 = max(case when [OBJECTIVE_ID] = 'A065CF91-01C9-41A8-A326-A00402329833' then [DESCRIPTION] else null end)      
FROM [Objectives]
Where [OBJECTIVE_ID] in ('4E2DEA7B-025A-4958-8696-2D13EDD3E08E','81B9B7E5-A833-4C5A-9B73-CBACFB055404','A065CF91-01C9-41A8-A326-A00402329833')

我也会声明3 @OBJECTIVE_ID's来减少文本和冗余

WHERE也是可选的，如果你有一个小的表，删除它

Answer 2

为什么不立即用子选择设置它们呢?

DECLARE @DESCRIPTION_1 NVARCHAR(200)=(SELECT [DESCRIPTION] FROM [Objectives] WHERE [OBJECTIVE_ID] = '4E2DEA7B-025A-4958-8696-2D13EDD3E08E');
DECLARE @DESCRIPTION_2 NVARCHAR(200)=(SELECT [DESCRIPTION] FROM [Objectives] WHERE [OBJECTIVE_ID] = '81B9B7E5-A833-4C5A-9B73-CBACFB055404');
DECLARE @DESCRIPTION_3 NVARCHAR(200)=(SELECT [DESCRIPTION] FROM [Objectives] WHERE [OBJECTIVE_ID] = 'A065CF91-01C9-41A8-A326-A00402329833');

假设你的OBJECTIVE_ID上有一个索引，这应该是非常快的…

Answer 3

好的，如果可以的话再拍一次。如你所知，SQL Server不支持数组或动态创建变量(无动态SQL)

我应该补充一句，IDENTITY保持适当的顺序，以防它很重要。

Declare @KeyID Table (Key_PS int identity,Key_Value varchar(100))
Insert Into @KeyID values (@OBJECTIVE1_ID),(@OBJECTIVE2_ID),(@OBJECTIVE3_ID)
SELECT @OBJECTIVE_1_DESCRIPTION  = max(IIF(Key_PS=1 ,[OBJECTIVE_DESCRIPTION],null))
     , @OBJ1_LVL                 = max(IIF(Key_PS=1 ,[OBJECTIVE_LEVEL],null))
     , @OBJ1_TARGET              = max(IIF(Key_PS=1 ,[OBJECTIVE_COMPETION_COUNT],null))
     , @REWARD_1_CODE            = max(IIF(Key_PS=1 ,[OBJECTIVE_REWARD],null)) 
     --... [2 - 17] ... --
     , @OBJECTIVE_18_DESCRIPTION = max(IIF(Key_PS=18,[OBJECTIVE_DESCRIPTION],null))
     , @OBJ18_LVL                = max(IIF(Key_PS=18,[OBJECTIVE_LEVEL],null))
     , @OBJ18_TARGET             = max(IIF(Key_PS=18,[OBJECTIVE_COMPETION_COUNT],null))
     , @REWARD_18_CODE           = max(IIF(Key_PS=18,[OBJECTIVE_REWARD],null)) 
FROM (Select B.*,A.* 
       From  [dbo].[Objectives] 
       Join  @KeyID B on (A.OBJECTIVE_ID=B.Key_Value)
     ) A

Answer 4

Use UNION:

SELECT 
  @Objective1_Id Objective_Id,
  @OBJECTIVE_1_DESCRIPTION = [dbo].[Objectives].[OBJECTIVE_DESCRIPTION]
, @OBJ1_LVL = [dbo].[Objectives].[OBJECTIVE_LEVEL]
, @OBJ1_TARGET = [dbo].[Objectives].[OBJECTIVE_COMPETION_COUNT]
, @REWARD_1_CODE = [dbo].[Objectives].[OBJECTIVE_REWARD]
FROM [dbo].[Objectives] 
WHERE [dbo].[Objectives].[OBJECTIVE_ID] = @OBJECTIVE1_ID
UNION
SELECT 
  @Objective2_Id,
  @OBJECTIVE_1_DESCRIPTION = [dbo].[Objectives].[OBJECTIVE_DESCRIPTION]
, @OBJ1_LVL = [dbo].[Objectives].[OBJECTIVE_LEVEL]
, @OBJ1_TARGET = [dbo].[Objectives].[OBJECTIVE_COMPETION_COUNT]
, @REWARD_1_CODE = [dbo].[Objectives].[OBJECTIVE_REWARD]
FROM [dbo].[Objectives] 
WHERE [dbo].[Objectives].[OBJECTIVE_ID] = @OBJECTIVE2_ID
UNION
SELECT 
  @Objective3_Id,
  @OBJECTIVE_1_DESCRIPTION = [dbo].[Objectives].[OBJECTIVE_DESCRIPTION]
, @OBJ1_LVL = [dbo].[Objectives].[OBJECTIVE_LEVEL]
, @OBJ1_TARGET = [dbo].[Objectives].[OBJECTIVE_COMPETION_COUNT]
, @REWARD_1_CODE = [dbo].[Objectives].[OBJECTIVE_REWARD]
FROM [dbo].[Objectives] 
WHERE [dbo].[Objectives].[OBJECTIVE_ID] = @OBJECTIVE3_ID;

假设行存在于所有的@ objective_id，你有一个查询将返回3行，每一行包含它相关的objective_id