我有一张表里面两个字段LineNo和IfSave，LineNo为正整数，IfSave为'T' or 'F'
对数据的要求是，LineNo相同的记录有且只有一条IfSave='T',超过一条为'T'或者全部为'F'的即为不合格数据。
请问怎么筛选出不合格的数据？
用的数据库是Firebird。
------解决方案--------------------
- SQL code
goif OBJECT_ID('tbl')is not nulldrop table tblgocreate table tbl( [LineNo] int, [IfSave] varchar(1))goinsert into tbl select 1,'T' union allselect 1,'T' union allselect 1,'T' union allselect 1,'F' union all select 2,'T' union allselect 2,'F' union allselect 2,'F' union allselect 2,'F' union allselect 3,'T' union allselect 4,'F' union allselect 5,'T' union allselect 5,'T' union allselect 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 Ffrom tbl group by [LineNo])awhere T>1 or (T=0 and F>=1)/*LineNo1456*/
------解决方案--------------------
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