SQL 多个条件最小语句

Question

所以我有一个包含以下列的表

Member	Version
1	15
2	18
3	316
1	20
3	40
1	316
3	65
2	27
3	65

我想编写一条 SQL 来获取“Member”的最小值，并受到以下约束“版本”的数量（大约有 20 个，但这里只使用了三个）：

Where Version between 15 and 20
Where Version in (40, 316)
Where Version between 65 and 66

因此输出看起来像

Member	Version
1	15
1	316
2	18
3	316
3	65

我在临时表中有这段代码

Select Member, MIN(Version) as Minimum_Version from #Temp
    where Version between 15 and 20
    or Version in (40, 316)
        or Version between 65 and 66
    Group By Member;

我发现我的问题出在分组依据中，因为它正在“滚动”“成员”列。我使用的是 SQL Server v16，因此没有 String_agg。

感谢您的时间和知识。

编辑：所以我收到错误无效的对象名称“成员”。这是更新后的代码：

With #TempCTE_Model as(
Select Member, Version from #Temp
    where Version Between 15 and 20
        or Version in (40, 316)
        or Version Between 65 and 66)

Select distinct #TempCTE_Model.Member,
        SUBSTRING(
        (
            SELECT ','+cast(#Temp.Version as varchar(100))  AS [text()]
            FROM HIC_NB #Temp
            WHERE #Temp.Member = #TempCTE_Model.Member
            ORDER BY #Temp.Member
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Version]
FROM Member #TempCTE_Model`````

Answer 1

首先创建 cte

with a as(
Select a, b from #Temp
    where b between 15 and 20
    or b in (40, 316)
        or b between 65 and 66)

然后使用 For XML PATH 如下

SELECT DISTINCT a2.a, 
    SUBSTRING(
        (
            SELECT ','+cast(a1.b as varchar(100))  AS [text()]
            FROM a a1
            WHERE a1.a = a2.a
            ORDER BY a1.a
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [b]
FROM a a2