SQL语句如何表示除法运算？
表A：商店信息
ANOANAMEWQTYCITY
101韶山书店15长沙
204前门商店89北京
256东风商场501北京
345铁道商店76长沙
620武汉商场413武汉
表B：商品信息
BNOBNAMEPRICE
1毛笔21
2羽毛球4
3收音机325
4书包242
表AB：商店-商品对应关系
ANOBNOQTY
1011105
101242
101325
1014104
204361
2561241
256291
3451141
345218
345474
6204125
现在要找出至少供应代号为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
[解决办法]
select * from AB where ANO in (
select distinct ANO from AB
where BNO in
(select BNO from AB
where ANO= '345 ' )
and FSFURIKAEID <> '345 ')
用这个方法是可行的
exists还有待研究的
