语句查询
ID State blockID
1 0 1
2 1 1
3 1 2
4 0 2
5 2 1
6 2 3
7 2 3
8 2 3
查询显示字段为blockID,bState
bState不是数据库中的字段
bState的值是由相同的blockID的所有的state来决定的
如果State都是0,则为0
都是1,则为1
都是2,则为2
如果 部分为2,则为1
结果应为
blockID bState
1 1
2 1
3 2
[解决办法]
declare @a table(ID int,State int, blockID int)
insert @a select 1,0,1
union all select 2,1,1
union all select 3,1,2
union all select 4,0,2
union all select 5,2,1
union all select 6,2,3
union all select 7,2,3
union all select 8,2,3
select blockid,stated=case when sum(state)=0 then 0
when sum(state)/count(1)=1 then 1
when sum(state)/count(1)=2 then 2
else 1 end
from @a
group by blockid
/*
blockid stated
----------- -----------
1 1
2 1
3 2
（所影响的行数为 3 行）
*/
