表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语句呢?
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语句呢?
解决方案 »
- exp导出数据库表时报错,请问是什么原因。
- 如何查Oracle上执行的SQL历史
- 在shell中如何写读文件,然后把文件中的内容存入ORACLE数据库中,需求详见如下:
- oracle动态sql中使用位操作(bitand/bitor)的问题
- 请问一个权限问题
- 表空间中字段“allocation_type”的含义
- 进行冷备份恢复时发生ora-01092强制断开连接
- 在只有oracle10g数据查询权限的情况下,能自已写函数并执行吗?
- 变量赋值问题
- 高分求救,在线等待,急急急!:BDE+ORACLE中select语句的写法
- 关于Primary Key 和Unique的区别
- oledb 的英文全称是什么?
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谢谢大家!
π ano, bno (AB) ÷ π bno (σ ano = 256 (AB));==================================不是很明白哦,能否把想要的结果贴出来?
from AB
where AB.ANO <> 256
and AB.BNO in
(select AB.BNO
from AB
where AB.ANO = 256);====================result=========================
ANO BNO QTY
---------- ---------- ----------
345 1 141
101 1 105
345 2 18
101 2 42
from AB
where AB.ANO <> 101
and AB.BNO in
(select AB.BNO
from AB
where AB.ANO = 101);
==========================result======================================
ANO BNO QTY
---------- ---------- ----------
345 1 141
256 1 241
345 2 18
256 2 91
204 3 61
620 4 125
345 4 747 rows selected
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 1417 rows selected2:如果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需求了, 你可以看看~~~
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.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
256 2 91
256 1 241
345 4 74
345 2 18
345 1 1419 rows selected
如果是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.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
345 4 74
345 2 18
345 1 1417 rows selected
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 distinct ANO from AB
where BNO in
(select BNO from AB
where ANO= '345' )
and FSFURIKAEID <> '345')用这个方法是可行的
exists还有待研究的
SELECT C.ANO
FROM AB as C
WHERE NOT EXISTS (SELECT *
FROM AB as D
WHERE D.ANO = 101
AND NOT EXISTS (SELECT *
FROM AB as E
WHERE C.ANO = E.ANO
AND D.BNO = E.BNO));关键是NOT EXISTS跟关系代数的除法究竟是怎样的对应关系?谁能分析一下?我们要做到“知其然,知其所以然”
FROM AB as C
WHERE NOT EXISTS (SELECT *
FROM AB as D
WHERE D.ANO = 101 (带入下面的循环)
AND NOT EXISTS (SELECT *
FROM AB as E
WHERE C.ANO = E.ANO
AND D.BNO = E.BNO) (真)); 必须满足上面红色字体的条件才会被选出,遍历所有column,然后有一个bno满足所有的( C.ANO = E.ANO AND D.BNO = E.BNO)这里的D是满足条件D.ano = 101的