请考虑以下产品数据库模式:
- 零件 ,pname)
- 供应商(sid,sname)
- 目录(sid,pid)
sid 目录是外键,引用供应商和 pid 目录是外键,引用 Parts 表。 在中显示供应商(s1,p1)产生 p1 。
如果我想查找 >那些不是由一些供应商提供的,我们怎么能这样做?我读了一些部分如下:
SELECT * FROM parts P WHERE ..... ..(SELECT S.sid FROM suppliers WHERE ..... (SELECT * FROM catalog C WHERE s.sid = C.sid AND P.pid = C.pid))我的问题是通过 - 为什么?
解决方案
如果我想找到零件的细节不是由某些供应商提供,我们如何做?
如果您想要存在不提供该零件的供应商的零件的详细信息,则使用 EXISTS ,然后 NOT EXISTS ,是正确的。
我的问题是通过 - 我们使用存在和第二...我们使用不存在?
我将解释为什么不提供该部分可以提供您的查询。 ( EXISTS ,然后 NOT EXISTS 。)
A表格保留了使谓词(语句模板)成为真实命题(语句)的行:
- 零件零件P.pid名为P.pname
- / code>供应商S.sid命名为S.sname
- 目录 .pid
您需要为结果表达谓词。您想要从中创建真实语句的 P.pid - P.pname 行:
部分P.pid命名为P.pname AND EXISTS S.sid [supplier S.sid命名为 AND NOTsupplier S.sid supplies part P.pid]但我们必须表达该谓词根据给定的谓词(加上条件和逻辑运算符),以便DBMS可以计算满足它的行:
部分P.pid命名为P.pname和EXISTS S.sid [EXISTS S.sname供应商S.sid命名为S.sname AND NOT EXISTS C.sid,C.pid [supplier C.sid supplies part P.pidAND C.sid = S.sid AND C.pid = P.pid]]现在转换为SQL:
- em> 的谓词变成 FROM T b $ b
- AND 的谓词变为 JOIN T / em> c> c $> code> 或 ON 条件
- 最外面的EXISTS 已删除的列变为 SELECT 的所有列 [ 的谓词变成 EXISTS / code> c> / ul>
您的问题的原始部分供应商不提供的零件详细资料 /暧昧。这可能意味着 P.id - P.pname 对其中:
- 零件不是由所有供应商提供(解释同意您的SQL)
- 不提供零件任何供应商)(解释同意 Shnugo的回答中的两个SQL版本)
-
零件没有多个供应商
SELECT * FROM零件P WHERE NOT EXISTS(SELECT * FROM目录C1 JOIN目录C2 ON C1.sid<> C2.sid AND C1.pid = P.pid AND C2.pid = P.pid) - Parts (pid, pname)
- Suppliers (sid, sname)
- Catalog (sid, pid)
- Parts "part P.pid is named P.pname"
- Suppliers "supplier S.sid is named S.sname"
- Catalog "supplier C.sid supplies part C.pid"
- initial predicate of T becomes FROMT
- AND predicate of T becomes JOINT
- AND condition becomes WHEREcondition or ONcondition
- outermost EXISTS dropped columns becomes SELECTkept columns
- other EXISTS all columns of T [predicate of T] becomes EXISTS (T)
- "the parts are not supplied by all the suppliers" (the interpretation agreeing with your SQL)
- "the parts are not not supplied (by any of the suppliers)" (the interpretation agreeing with the two SQL versions in Shnugo's answer
"the parts don't have multiple suppliers"
SELECT * FROM Parts P WHERE NOT EXISTS (SELECT * FROM Catalog C1 JOIN Catalog C2 ON C1.sid <> C2.sid AND C1.pid = P.pid AND C2.pid = P.pid)
如果您有 code> 1-4和供应商 1-3,然后上面给出三个不同的答案:
INSERT INTO目录VALUES(1,1),(2,1),(2,2),(3,1),(3,2),(3,3) b $ bPPS 上述逻辑公式为:
- 1.零件不是由所有供应商提供 P(P.pid,P.pname)和现有的S.sid,S.sname [S(S.sid,S.sname)AND NOT C(S.sid,P.pid)] - 2零件不提供供应商) P(P.pid,P.pname)AND NOT EXISTS C.sid C(C.sid,P.pid) - 3.零件没有多个供应商 P(P.pid,P.pname) AND NOT EXISTS C1.sid,C2.sid [C(C1.sid,P.pid)AND C sid,P.pid)和C1.sid C2.sid]
Consider the following schema of a product database:
sid in Catalog is a foreign key and references Suppliers and pid in Catalog is a foreign key and references the Parts table. (s1, p1) in Catalog table shows supplier s1 produces p1.
If I want find details of Parts that are not supplied by some suppliers, how we can do this? I read some parts as follows:
SELECT * FROM parts P WHERE ....... (SELECT S.sid FROM suppliers WHERE ..... (SELECT * FROM catalog C WHERE s.sid = C.sid AND P.pid = C.pid))My problem is via - why at first? We use exist and in second ... we use not exist?
解决方案If I want find details of Parts that are not supplied by some suppliers, how we can do this?
If you want "details for parts where there exists a supplier that doesn't supply that part" then your query, with EXISTS then NOT EXISTS, is correct.
My problem is via - why at first? We use exist and in second ... we use not exist?
I will explain why "details for parts where there exists a supplier that doesn't supply that part" can give your query. (With EXISTS then NOT EXISTS.)
A table holds that rows that make some predicate (statement template) into a true proposition (statement):
You need to express the predicate for your result. You want the P.pid-P.pname rows that make a true statement from this:
"part P.pid is named P.pname" AND EXISTS S.sid ["supplier S.sid is named something" AND NOT "supplier S.sid supplies part P.pid"]But we must express this predicate in terms of the given predicates (plus conditions and logic operators) so that the DBMS can calculate the rows that satisfy it:
"part P.pid is named P.pname" AND EXISTS S.sid [EXISTS S.sname "supplier S.sid is named S.sname" AND NOT EXISTS C.sid, C.pid [ "supplier C.sid supplies part P.pid" AND C.sid = S.sid AND C.pid = P.pid]]Now convert to SQL:
PS Your question's original "details of parts that are not supplied by some suppliers" is unclear/ambiguous. It could mean P.id-P.pname pairs where:
If you have Parts 1-4 and Suppliers 1-3 then the above give three different answers after:
INSERT INTO Catalog VALUES (1,1),(2,1),(2,2),(3,1),(3,2),(3,3);PPS Logic formulations for the above are:
-- 1. "the parts are not supplied by all the suppliers" P(P.pid, P.pname) AND EXISTS S.sid, S.sname [S(S.sid, S.sname) AND NOT C(S.sid, P.pid)] -- 2 "the parts are not supplied (by some of the suppliers)" P(P.pid, P.pname) AND NOT EXISTS C.sid C(C.sid, P.pid) -- 3. "the parts don't have multiple suppliers" P(P.pid, P.pname) AND NOT EXISTS C1.sid, C2.sid [C(C1.sid, P.pid) AND C(C2.sid, P.pid) AND C1.sid <> C2.sid]
更多推荐
存在和不存在查询误解?
发布评论