一条SQL语句

现在有表

id  flag
 1   01
 1   02
 1   03
 2   03
 2   04
 3   01
 3   01
 4   02
 4   04

算法是 按id统计 如果同一ID中有02则结果为02  如果有03、04、、则结果为Err  如果只有01 则为01

也就是说结果为
id   flag
 1    02
 2    Err
 3    01
 4    02
 

CREATE TABLE [dbo].[t1] (
[id] [int] NULL ,
[flag] [char] (2) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

insert into t1 values (1, '01')
insert into t1 values (1, '02')
insert into t1 values (1, '03')
insert into t1 values (2, '03')
insert into t1 values (2, '04')
insert into t1 values (3, '01')
insert into t1 values (3, '01')
insert into t1 values (4, '02')
insert into t1 values (4, '04')
insert into t1 values (5, '01')
insert into t1 values (5, '04')
insert into t1 values (5, '02')
insert into t1 values (6, '03')
insert into t1 values (6, '04')
insert into t1 values (6, '05')
insert into t1 values (7, '01')
insert into t1 values (7, '01')
insert into t1 values (7, '01')
insert into t1 values (8, '01')
insert into t1 values (8, '06')
insert into t1 values (8, '07')
insert into t1 values (9, '01')
insert into t1 values (9, '01')
insert into t1 values (9, '02')

总结以上人失败的原因和好的思想，现在把最后我的成果写给大家分享：
这个问题的解决要分一下三个步骤：
一、分离出flag='02'记录
select distinct id,flag from T1 where flag='02'
二、找出同一 id 对应只有 '01' 的记录
select distinct id,max(flag) as flag  from T1  group by [id] having max(flag)='01'
三、除了一、二不之外的id都是'err'
select id,'err' as flag from T1 where id not in
(select distinct id from T1 where flag='02'
union
select distinct id from T1  group by [id] having max(flag)='01')

最后把三个记录连接起来
select distinct id,flag from T1 where flag='02'
union
select distinct id,max(flag) as flag  from T1  group by [id] having max(flag)='01'
union
select id,'err' as flag from T1 where id not in
(select distinct id from T1 where flag='02'
union
select distinct id from T1  group by [id] having max(flag)='01')