日期:2014-05-18  浏览次数:20495 次

求大神们指点一个SQL语句
我有一张表里面两个字段LineNo和IfSave,LineNo为正整数,IfSave为'T' or 'F'
对数据的要求是,LineNo相同的记录有且只有一条IfSave='T',超过一条为'T'或者全部为'F'的即为不合格数据。
请问怎么筛选出不合格的数据?
用的数据库是Firebird。

------解决方案--------------------
SQL code

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