求一个sql

表一：

表二：

表一和表二为一对多关系（A和X对应）

结果是要 表一中对应表二所有Y值为2的结果集。

解释：看表二： 192对应的所有Y值为2，符合，193对应的Y值中有1，不符合，194对应的Y值中有4，不符合。

查询的结果

A         B

192     0

不知道有没有表述清楚

。。。。你的条件是越来越多，等晚上回去的

什么数据库？

[code="sql"]SELECT t1.*
FROM (SELECT t2.x
FROM t2
GROUP BY t2.x
HAVING SUM(CASE WHEN t2.y = '2' THEN 0 ELSE 1 END) = 0
ORDER BY t2.x) m
INNER JOIN t1 ON m.x = t1.a[/code]

A,B所在的表为t1，X,Y,Z所在表为t2。
[code="java"]select A,B from t1 where A not in(select X from t2 group by X,Y having Y !=2);[/code]

为啥非得exsits?先接着前面的sql，
[code="sql"]SELECT t1.*
FROM (SELECT *
FROM (SELECT t2.x
FROM t2
GROUP BY t2.x
HAVING SUM(CASE WHEN t2.y = '2' THEN 0 ELSE 1 END) = 0
ORDER BY t2.x)
UNION ALL
SELECT t2.x
FROM t2
INNER JOIN t3 ON t2.z = t3.m
WHERE t3.n = '4') m
INNER JOIN t1 ON m.x = t1.a[/code]

9i之后，优化器会自动优化，in和exsits没有区别，你数据量大，就要考虑尽可能少的扫描表

我理解错了，看看这个呢
[code="sql"]SELECT t1.*
FROM (SELECT *
FROM (SELECT t2.x
FROM t2
GROUP BY t2.x
HAVING SUM(CASE WHEN t2.y = '2' THEN 0 ELSE 1 END) = 0
ORDER BY t2.x)
UNION ALL
SELECT t2.x
FROM t2
INNER JOIN t3 ON t2.z = t3.m
GROUP BY t2.x
HAVING SUM(CASE WHEN t3.n = '4' THEN 0 ELSE 1 END) = 0) m
INNER JOIN t1 ON m.x = t1.a[/code]

你的第2个条件，是对应T2的所有Y值为1？？，按照以前你给数据，应该是满足一个Y值为1。

考虑到数据量大的情况下的效率问题，还是用exists吧，在t1.a和t2.x上建索引，然后使用以下语句：
[code="sql"]
select t1.*
from t1
where not exists (select 1
from t2
where t1.a = t2.x
and t2.y <> 2)
[/code]

not exists也是最简单的语句

my god，以下语句。。。给楼主个建议，为了代码的可读性和查询效率可以把表扩展一下，增加一些查询字符，如果是老系统可以先用脚本切换之后再改造业务系统

[code="sql"]
select t1.*
from tbl_1 t1
where not exists (select 1
from tbl_2 t2
where t1.a = t2.x
and t2.y <> 2) --条件 1
or not exists (select 1
from tbl_2 t2
where t1.a = t2.x
and t2.y <> 1
or exists (select 1
from tbl_3 t3
where t2.z = t3.m
and t3.n = 5)) --条件2
or (not exists (select 1
from tbl_2 t2
where t1.a = t2.x
and (t2.y <> 2 and t2.y <> 4 and
(t2.y <> 1 or exists
(select 1
from tbl_3 t3
where t2.z = t3.m
and t3.n = 5))))

    and not exists (select 1
                      from tbl_1 t2
                     where not exists (select 1
                              from tbl_2 t3
                             where t2.a = t3.x
                               and t3.y <> 4)
                       and t1.a = t2.a) --条件3
   ) --条件4

[/code]

增加一些查询字段，之前打错了

下面的数据满足条件2吗？？
t2：
X Y Z
196 1 93
196 1 94
196 1 95

t3：
M N
95 5
99 1

兄弟，看一眼，我那sql完全满足你说的条件

select *
from t1
where exists (select 1
from (select x,
count(x) cnt1,
sum(case
when y = 2 then
1
else
0
end) cnt2
from t2
group by x) t2
where t2.x = t1.a
and t2.cnt1 = t2.cnt2);
不知道这个是不是你要的结果！

问题解决了吗？我看着上面一堆都进了套套了！

不知道这样可不可以呢？
SELECT t1.* FROM tab_1 AS t1,
(
SELECT t2.x AS t3_id, SUM(t2.y) AS t3_sum, COUNT(t2.x) AS t3_count
FROM tab_2 AS t2
GROUP BY t2.x
) AS t3
WHERE t3.t3_id=t1.a
AND t3.t3_sum%t3.t3_count=0;

仔细看了下，题目变了？出来个表三？
我上面的没有考虑表三，你看看可以不？在这个基础上加表三还是简单的！
只是想法和他们的不一样而已！