1 F001 M001 78
2 F002 M001 67
3 F003 M001 89
4 F004 M001 76
5 F001 M002 89
6 F002 M002 67
7 F003 M002 84
8 F004 M002 96
9 F001 M003 70
10 F002 M003 87
11 F003 M003 92
12 F004 M003 56
13 F001 M004 80
14 F002 M004 78
15 F003 M004 97
16 F004 M004 66
17 F001 M006 88
18 F002 M006 55
19 F003 M006 86
20 F004 M006 79
21 F002 M007 77
22 F003 M008 65
23 F004 M007 48
24 F004 M009 75
25 F002 M009 88这是数据,要求用一条SELECT语句查询出 F00 的这一例的值从1-4都存在的 M00 的这一列的值
比如说 M009 它所对应的只有 F002 和 F004 这就是要抛弃的
2 F002 M001 67
3 F003 M001 89
4 F004 M001 76
5 F001 M002 89
6 F002 M002 67
7 F003 M002 84
8 F004 M002 96
9 F001 M003 70
10 F002 M003 87
11 F003 M003 92
12 F004 M003 56
13 F001 M004 80
14 F002 M004 78
15 F003 M004 97
16 F004 M004 66
17 F001 M006 88
18 F002 M006 55
19 F003 M006 86
20 F004 M006 79
21 F002 M007 77
22 F003 M008 65
23 F004 M007 48
24 F004 M009 75
25 F002 M009 88这是数据,要求用一条SELECT语句查询出 F00 的这一例的值从1-4都存在的 M00 的这一列的值
比如说 M009 它所对应的只有 F002 和 F004 这就是要抛弃的
from tab a,(
select m00
from tab
where f00 in ('F001','F002','F003','F004')
group by M00
having count(distinct F00)=4
) as b
where a.M00=b.M00
from 表
group by MOO
having count(distinct FOO) =4-- 如果更精确的查询,那么使用如下的查询语句select MOO,
sum(cast(replace(FOO,'FOO','') as int)) as FOO_SUM,
count(distinct FOO) as FOO_CT
from 表
group by MOO
having sum(cast(replace(FOO,'FOO','') as int))=10 and count(distinct FOO)=4