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') 总结: 之所以我们大家以前所做的语句是错误的,在下结合自己总结有几点供大家参考: 一、没有很好的理解命题人的意图 二、逻辑思想混乱,没有认清数据之间的微妙关系 三、没有做严格的测试 如果有错误 希望大家指点
|