一个是我感到困惑的查询，有例表，第一回答正确的100分全给

[仙鹤]

表名：cases

caesid statusname f_count
2001 0001 0
2001 0001 1
2001 0002 3
2001 0001 0
2001 0001 0
2002 0001 0
2002 0002 3
2002 0003 0
2003 0002 4
2004 0003 2
2005 0001 0
2006 0001 0
2006 0001 1
2006 0002 2

查找 caseid
条件 statusname=0001 并且 f_count全为0

比如上面的表符合条件的caseid为 2002,2005

如何写一个sql语句实现上面的这个查找功能？

[TigerSuper]

select caseid from cases
where statusname=0001
group by caseid,statusname,f_count
having sum(f_count)=0

[子陌红尘]

select
a.caesid
from
cases a
where
statusname = '0001'
and
f_count='0'
and
not exists(select 1 from cases where caesid=a.caesid and statusname=a.statusname and f_count!=a.f_count)

[仙鹤]

非常抱歉，我忘记了说明一点，不能对f_count用求和的方式,
这是我举例的失误，f_count的值有可能是字母！

[子陌红尘]

create table cases(
caesid int,
statusname varchar(10),
f_count int)

insert into cases select 2001,'0001',0
insert into cases select 2001,'0001',1
insert into cases select 2001,'0002',3
insert into cases select 2001,'0001',0
insert into cases select 2001,'0001',0
insert into cases select 2002,'0001',0
insert into cases select 2002,'0002',3
insert into cases select 2002,'0003',0
insert into cases select 2003,'0002',4
insert into cases select 2004,'0003',2
insert into cases select 2005,'0001',0
insert into cases select 2006,'0001',0
insert into cases select 2006,'0001',1
insert into cases select 2006,'0002',2

select
a.caesid
from
cases a
where
a.statusname = '0001'
and
a.f_count='0'
and
not exists(select 1 from cases where caesid=a.caesid and statusname=a.statusname and f_count!=a.f_count)

[TOMATOTO]

这样试一下
select caseid from cases
where statusname=0001 and f_count=0
group by caseid，statusname,f_count
having count(caseid)=1

[wxqq2001]

是不是我想的简单了?
select distinct caseid from cases where statusname=0001 AND Max(f_count)=0

[TOMATOTO]

select * from cases
where caesid not in
(select caesid from cases where statusname<>0001 or f_count<>0)

[TOMATOTO]

有误差，是这样
select distinct caseid from cases
where caseid not in
(select casesid from cases
where statusname<>0001 or f_count<>0)

[TOMATOTO]

或者是：
select distinct caesid from cases
where caesid not in
(select caesid from cases
where statusname<>0001 or f_count<>0)
and caesid not in (select caesid from cases where statusname=0001 and f_count<>0)

[仙鹤]

谢谢大家这么热心！

libin_ftsafe(子陌红尘) 和 TOMATOTO(蓝蓝) 回答的可以得到相同的结果。

觉得 TOMATOTO(蓝蓝) 的
select * from cases
where caesid not in
(select caesid from cases where statusname<>0001 or f_count<>0)
在数据量很大的表里有可能会比较慢。

但是我没有搞懂libin_ftsafe(子陌红尘)的sql中 下面语句的意思：
not exists(select 1 from cases where caesid=a.caesid and statusname=a.statusname and f_count!=a.f_count)

能请libin_ftsafe(子陌红尘) 解释一下吗？

[paoluo]

其实这样也可以，而且比较容易理解。


Select a.caesid from cases
where a.statusname = '0001'
And caesid Not In (Select Distinct caesid from cases Where statusname = '0001' And f_count<>'0' )

[paoluo]

写错了，改下。


Select caesid from cases
where statusname = '0001'
And caesid Not In (Select Distinct caesid from cases Where statusname = '0001' And f_count<>'0' )

[子陌红尘]

但是我没有搞懂libin_ftsafe(子陌红尘)的sql中 下面语句的意思：
not exists(select 1 from cases where caesid=a.caesid and statusname=a.statusname and f_count!=a.f_count)

能请libin_ftsafe(子陌红尘) 解释一下吗？
--------------------------------------------------------------------------------
如果not exists返回true，则表内不存在与当前记录的caesid、statusname字段相同且f_count字段不同的记录

[仙鹤]

paoluo(一天到晚游泳的鱼) 和 TOMATOTO(蓝蓝) 结果都对。

多谢了！

[仙鹤]

谢谢 libin_ftsafe(子陌红尘) 的解释

如果 把下面记录作修改
2001 0001 0
2001 0001 1
2001 0002 3
2001 0001 0
2001 0001 0

修改为：

2001 0001 0
2001 0001 0
2001 0002 3
2001 0001 0
2001 0001 0

这时候 caseid 2001 也是符合我要求的 记录，

那么你写的sql还适合这种情况吗？是否会出现重复caseid?

[子陌红尘]

如果不希望获得重复的记录，使用distinct关键字过滤重复记录：
-----------------------------------------------------------
select
distinct a.caesid
from
cases a
where
a.statusname = '0001'
and
a.f_count='0'
and
not exists(select 1 from cases where caesid=a.caesid and statusname=a.statusname and f_count!=a.f_count)

[paoluo]

我的一样，也要加个Distinct

Select Distinct caesid from cases
where statusname = '0001'
And caesid Not In (Select Distinct caesid from cases Where statusname = '0001' And f_count<>'0' )

[rocklabzhang]

--drop table cases
create table cases
(
caseid varchar(10) not null,
statusname varchar(10) not null,
f_count int not null
)

insert into cases
select '2001', '0001', 0
union all
select '2001', '0001', 1
union all
select '2001', '0002', 3
union all
select '2001', '0001', 0
union all
select '2001', '0001', 0
union all
select '2002', '0001', 0
union all
select '2002', '0002', 3
union all
select '2002', '0003', 0
union all
select '2003', '0002', 4
union all
select '2004', '0003', 2
union all
select '2005', '0001', 0
union all
select '2006', '0001', 0
union all
select '2006', '0001', 1
union all
select '2006', '0002', 2

select distinct caseid from cases C1 where C1.statusname='0001' and C1.f_count = '0'and
not exists( select 1 from cases C2 where C2.statusname=C1.statusname and C2.f_count<>0 and C1.caseid=C2.caseid )

drop table cases

[仙鹤]

谢谢 rocklabzhang() 你的表述让我更清楚了！

多谢各位！