只需创建一个临时表(可以动态插入)并在 LEFT JOIN 中使用它.LEFT JOIN(连同 COALESCE)说明 ELSE '' 条件，但如果没有 ELSE> 条件和所有范围都在数据中表示，应该使用 INNER JOIN(不需要 COALESCE).

Just create a temp table (which can be inserted into dynamically) and use it in a LEFT JOIN. A LEFT JOIN (along with the COALESCE) accounts for the ELSE '' condition, but if there was no ELSE condition and all ranges were represented in the data, an INNER JOIN should be used (and no need for the COALESCE).

为了从两个单独的变量动态填充临时表，这些变量的数据仅按 CSV 列表中的位置对齐，其中之一是需要在逗号和字符串to"上拆分的二维数组"，我使用了 CTE(以便更容易地拆分二维 @Dates 变量)和基于 SQLCLR 的字符串拆分器.我使用的拆分器来自 SQL# 库(我是它的创建者，但此功能在 Free版本)，但您可以使用任何您喜欢的拆分器(但请不要使用基于 WHILE 循环的拆分器，因为这很愚蠢).

In order to dynamically populate the temp table from two separate variables that whose data is aligned only by position within the CSV list, and one of which is a two-dimensional array needing to be split on both comma and the string "to", I used a CTE (to make it easier to split the two-dimension @Dates variable) and a SQLCLR-based string splitter. The splitter I used is from the SQL# library (which I am the creator of but this function is in the Free version) but you can use any splitter you like (but please don't use a WHILE loop-based splitter as that is just silly).

CREATE TABLE #Cola
(
  StartDate DATETIME NOT NULL,
  EndDate DATETIME NOT NULL,
  Name NVARCHAR(50) NOT NULL
);

DECLARE @Dates VARCHAR(MAX) = '2001-01-01to2001-01-05,2001-01-10to2001-01-15,
                               2001-01-20to2001-01-25,2001-02-01to2001-02-05,
                               2001-02-10to2001-02-15';

DECLARE @Names VARCHAR(MAX) = 'G1,G2,G3,G4,G5';

-- dynamic population of temp table from two variables (@Dates being 2 dimensional)
;WITH cte AS
(
  SELECT vals.SplitNum,
         vals.SplitVal,
         CHARINDEX(N'to', vals.SplitVal) AS [WhereToSplit]
  FROM   SQL#.String_Split4k(@dates, ',', 1) vals
)
INSERT INTO #Cola (StartDate, EndDate, Name)
  SELECT CONVERT(DATETIME, SUBSTRING(cte.SplitVal, (cte.WhereToSplit - 10), 10)),
         CONVERT(DATETIME, SUBSTRING(cte.SplitVal, (cte.WhereToSplit + 2), 10)),
         names.SplitVal
  FROM cte
  INNER JOIN SQL#.String_Split4k(@names, ',', 1) names
          ON names.SplitNum = cte.SplitNum; -- keep the values aligned by position


SELECT tab.fields, COALESCE(cola.[Name], '') AS [Cola]
FROM   SchemaName.TableName tab
LEFT JOIN #Cola cola
        ON tab.cola BETWEEN cola.StartDate AND cola.EndDate