日期:2014-05-18 浏览次数:20534 次
go if OBJECT_ID('tbl')is not null drop table tbl go create table tbl ( [LineNo] int, [IfSave] varchar(1) ) go insert into tbl select 1,'T' union all select 1,'T' union all select 1,'T' union all select 1,'F' union all select 2,'T' union all select 2,'F' union all select 2,'F' union all select 2,'F' union all select 3,'T' union all select 4,'F' union all select 5,'T' union all select 5,'T' union all select 6,'F' select [LineNo] from( select [LineNo], sum( case when [IfSave]='T' then 1 else 0 end) as T, sum( case when [IfSave]='F' then 1 else 0 end) as F from tbl group by [LineNo])a where T>1 or (T=0 and F>=1) /* LineNo 1 4 5 6 */
------解决方案--------------------
select lineNo,count(1)
from tab
where IfSave='T'
group by lineNo
having count(1)>1
union all
select lineNo,count(1)
from (select lineNo,IfSave
from tab group by lineNo,IfSave ) as t
where IfSave='F'
group by lineNo
having count(1)=1