SQL:帮助与嵌套查询
问题描述:
这里是我的架构:SQL:帮助与嵌套查询
供应商(SID:整数,SNAME:字符串,地址字符串)
零件(PID:整数, PNAME:字符串,颜色:字符串)
目录(SID:整数,PID:整数,成本:真正的)
大胆表示主键。
我想写一个查询来查找供应每个零件的所有供应商。这里有两个疑问我已经:
-- get all parts for a given supplier
SELECT Parts.pid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Suppliers.sid = 4;
-- gets all parts that exist
SELECT Parts.pid
FROM Parts
我想做的事,在必要的条件,是这样的:
Define result set
Foreach Supplier:
If the list of parts produced by a supplier
is equal to the total list of parts, add this supplier to the result set
Return result set
我怎样才能转化为MySQL的呢?
答
用途:
SELECT s.*
FROM SUPPLIER s
JOIN (SELECT c.sid,
COUNT(c.pid) AS num_parts
FROM CATALOG c
GROUP BY c.sid) x ON x.sid = s.sid
JOIN (SELECT COUNT(*) AS total_parts
FROM PARTS) y ON y.total_parts = x.num_parts
答
不知道如何转换到MySQL,但事情沿着这些路线:
select s.sname, PartCount from (
select s.SID, s.sname, PartCount = sum(p.PID)
inner join Catalog c on c.SID = s.SID
inner join Parts p on p.PID = c.PID
where s.SID = 4
group by s.SID
) a
where PartCount = MAX(PartCount)
注标签的子查询“A”是一个任意名称为MSSQL需要在其子查询出于某种原因名称。不知道它在MySQL中如何工作。
答
尝试(未经测试):
SELECT s.*
FROM (
SELECT sid, count(pid) as c
FROM Catalog
GROUP BY sid) q1
JOIN Suppliers s ON s.sid = q1.sid
WHERE q1.c = (SELECT COUNT(*) FROM Parts)
答
尝试:
SELECT Suppliers.sid
FROM Suppliers
INNER JOIN
(SELECT sid, COUNT(pid) as num
FROM Catalog
GROUP BY sid)as t1
ON Suppliers.sid = t1.sid
WHERE t1.num =
(SELECT COUNT(pid) FROM Parts)
我会建议寻找到UNION和减...然后我想起MySQL不支持MINUS,所以你必须找到一种方法来伪造它(并且*有*方法)。但通常情况下,我会说,如果您从“ALL PARTS”列表中减去供应商的零件清单,并且遗留了某些零件,那么该供应商不提供所有零件。 – FrustratedWithFormsDesigner 2010-02-21 04:33:29