表A：商店信息
ANO ANAME WQTY CITY
101 韶山书店 15 长沙
204 前门商店 89 北京
256 东风商场 501 北京
345 铁道商店 76 长沙
620 武汉商场 413 武汉
表B：商品信息
BNO BNAME PRICE
1 毛笔 21
2 羽毛球 4
3 收音机 325
4 书包 242
表AB：商店-商品对应关系
ANO BNO QTY
101 1 105
101 2 42
101 3 25
101 4 104
204 3 61
256 1 241
256 2 91
345 1 141
345 2 18
345 4 74
620 4 125
现在要找出至少供应代号为256的商店所供应的全部商品的商店代号ANO，只涉及到表AB。这个SQL语句该怎么写？
具体说，我已经知道关系表达式是：
π ano, bno (AB) ÷ π bno (σ ano = 256 (AB));
我的问题是：如何把这里的除法运算转化为SQL语句呢？
------解决方案--------------------
明白你的意思了，我测试成功的，你试试看~~~
1：如果256的情况：
---------------------sql-------------------------------
select *
from AB AB_4
where exists (select yy.ANO
from (select AB_1.ANO, count(*) as count_B1
from AB AB_1,
(select AB_2.ANO, AB_2.BNO
from AB AB_2
where AB_2.ANO = 256) zz
where AB_1.ANO <> zz.ANO
and AB_1.BNO = zz.BNO
group by AB_1.ANO) yy,
(select count(*) as count_B2
from AB AB_3
where AB_3.ANO = 256) rr
where count_B1 > = rr.count_B2
and yy.ANO = AB_4.ANO);
=====================result================================
ANO BNO QTY
---------- ---------- ----------
101 4 104
101 3 25
101 2 42
101 1 105
345 4 74
345 2 18
345 1 141
7 rows selected
2：如果345的情况：
---------------------sql-------------------------------
select *
from AB AB_4
where exists (select yy.ANO
from (select AB_1.ANO, count(*) as count_B1
from AB AB_1,
(select AB_2.ANO, AB_2.BNO
from AB AB_2
where AB_2.ANO = 345) zz
where AB_1.ANO <> zz.ANO
and AB_1.BNO = zz.BNO
group by AB_1.ANO) yy,
(select count(*) as count_B2
from AB AB_3
where AB_3.ANO = 345) rr
where count_B1 > = rr.count_B2
and yy.ANO = AB_4.ANO);
=====================result================================
ANO BNO QTY
---------- ---------- ----------
101 4 104
101 3 25
101 2 42
101 1 105
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
结果应该是满足LZ需求了, 你可以看看~~~
------解决方案--------------------
UP！
有没有简单的方法？高手们。
------解决方案--------------------
Try：
SELECT ANO
FROM AB T1,
(SELECT BNO, COUNT(*) OVER() CNT FROM AB T1 WHERE ANO = '256 ') T2
WHERE T1.BNO = T2.BNO
GROUP BY ANO, CNT
HAVING COUNT(ANO) = CNT