绕过查询中的最后一个 INNER JOIN [英] Bypass last INNER JOIN in query

问题描述

我有以下存储过程，它采用用户 ID、开始日期、结束日期和逗号分隔列表中的代码列表，并返回这两个日期之间与以下日期之一匹配的所有活动记录列表中的代码.

I have the following stored procedure which takes a user ID, a starting date, an end date, and a list of codes in a comma-delimited list, and it returns all activity records between those two dates which match one of the codes in the list.

ALTER PROCEDURE [dbo].[ActivitiesSummary] 

@UserID varchar(30),
@StartDate datetime,
@EndDate datetime,
@Codes varchar(100)

AS

BEGIN

 SET NOCOUNT ON;

 SELECT act.SectionID, act.UnitID, act.ActivityCode
 FROM dbo.Activities act INNER JOIN ConvertCodeListToTbl(@Codes) i ON act.ActivityCode = i.code
 WHERE act.ActivityDate>=@Startdate AND act.ActivityDate<@EndDate
 GROUP BY act.SectionID, act.UnitID, act.ActivityCode
 ORDER BY act.SectionID, act.UnitID, act.ActivityCode
END

ConvertCodeListToTbl(@Codes) 是一个函数，它采用逗号分隔的代码列表(例如，'A0001、B0001、C0001')并返回一个每行一个代码的表格:

ConvertCodeListToTbl(@Codes) is a function that takes a comma-delimited list of codes (e.g., 'A0001, B0001, C0001') and returns a table with one code per row:

A0001
B0001
C0001

除非没有选择任何代码，否则此方法非常有效.发生这种情况时，我没有收到任何记录，因为 @Codes='' 并且最后一个 INNER JOIN 没有返回任何记录.

This method works really well except when no codes have been selected. When that occurs, I receive no records back because @Codes='' and the last INNER JOIN returns no records.

我想要发生的事情:如果@Codes=''，则忽略最后一个 INNER JOIN，或者找到一种无论代码如何都返回所有记录的方法.

What I want to happen: if @Codes='', ignore the last INNER JOIN, or otherwise find a way to return all records regardless of code.

我有哪些选择?

推荐答案

听起来您需要将 INNER JOIN 行更改为:

Is sounds like you need to change the INNER JOIN line to:

FROM dbo.Activities act INNER JOIN ConvertCodeListToTbl(@Codes) i 
ON (act.ActivityCode = i.code OR @Codes = '')

这篇关于绕过查询中的最后一个 INNER JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！