日期:2014-05-18 浏览次数:20659 次
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