我不知道如何编写 mssql 选择来解决这个问题。
现有表数据的示例:
Id GroupID Pass
___ ________ _____
1 1 1
2 1 0
3 2 1
4 2 0
5 2 0
6 3 1
7 3 1
我需要的是以下示例(如果未传递组之一,则为 0 else 1(:
GroupID Pass
________ _____
1 0
2 0
3 1
感谢您的帮助!
使用 MIN 聚合
SELECT groupid, MIN(pass)
FROM tablename
GROUP BY groupid
使用窗口函数row_number()
select GroupID,Pass from (
select *,
row_number() over(
partition by GroupID
order by Pass asc
) as [rn]
from [yourtable] as [t]
) as [t1]
where [rn] = 1;
将MIN
函数与GROUP BY
一起使用。
查询
select [GroupID], min([Pass]) as [Pass]
from [your_table_name]
group by [GroupID]
order by [GroupID];
如果Pass
列是BIT
类型,则需要投射到INT
.