使用 SQL Server 2008 R2 的动态案例语句
我有如下case语句如下图:
I have the following case statement as shown below:
例子:
我有 case 语句:
case cola
when cola between '2001-01-01' and '2001-01-05' then 'G1'
when cola between '2001-01-10' and '2001-01-15' then 'G2'
when cola between '2001-01-20' and '2001-01-25' then 'G3'
when cola between '2001-02-01' and '2001-02-05' then 'G4'
when cola between '2001-02-10' and '2001-02-15' then 'G5'
else ''
end
注意:现在我想创建动态案例语句，因为值日期和名称作为参数传递，它可能会改变.
Note: Now I want to create dynamic case statement because of the values dates and name passing as a parameter and it may change.
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'
变量中的值可能会根据要求而变化，它将是动态的.所以case语句应该是动态的，不使用循环.
The values in the variables may change as per the requirements, it will be dynamic. So the case statement should be dynamic without using loop.
我的失败尝试:
DECLARE @Name varchar(max)
DECLARE @Dates varchar(max)
DECLARE @SQL varchar(max)
DECLARE @SQL1 varchar(max)
SET @Name = 'G1,G2,G3,G4,G5'
SET @dates = '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'
SELECT @SQL = STUFF((SELECT ' ' + Value FROM
(
SELECT 'WHEN Cola Between '''' AND '''' THEN ''' + A.Value + '''' AS Value
FROM
(
SELECT
Split.a.value('.', 'VARCHAR(100)') AS Value
FROM
(
SELECT CAST ('<M>' + REPLACE(@Name, ',',
'</M><M>') + '</M>' AS XML) AS Value
) AS A
CROSS APPLY Value.nodes ('/M') AS Split(a)
) AS A
) AS B
FOR XML PATH (''), type).value('.', 'Varchar(max)'),1,1,'') + ''
SET @SQL1 = 'CASE Cola '+@SQL+' ELSE '''' END'
PRINT(@SQL1);
卡住:但卡住了拆分 @dates 2001-01-01to2001-01-05在2001-01-01"和2001-01-05"之间.
推荐答案
只需创建一个临时表(可以动态插入)并在 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版本)，但您可以使用任何您喜欢的拆分器(但请不要使用基于循环的拆分器，因为这很愚蠢).
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
相关文章