mysql - 从具有多个表的架构中查询

Question

我有一个包含三个表的方案，其结构如下

donuts (name: string, price: integer) 
grocery (no: string, gname: string, minCredit: integer) 
distributor (dname: string, gno: string, deliverydate: date)

distributor.dname和distributor.gno是引用 donut 和杂货的外键，其中的键是名称而不是各自的我在尝试编写的2个查询时遇到了问题:

1. 查询名称为“Jelly”的杂货店中的 donut 中minCredit要求最高的杂货店(grocery，编号:字符串)的数量

        SELECT g.no 
        FROM grocery g, distributor dd, donut d
        WHERE minCredit >= * AND d.name = "JELLY" 
   

这不起作用

查找价格高于 2 的每个 donut 共享的杂货编号(grocery，编号:字符串)的查询

我试过了

SELECT g.no
FROM grocery g, distributor dd, donut d
WHERE g.price >= 2

这也没有做正确的事情。感谢任何帮助

Answer 1

尝试:

1.

SELECT g.no
FROM grocery g, distributor dd, donut d
WHERE g.no = dd.gno
  AND d.name = dd.dname
  AND d.name = 'JELLY'
ORDER BY g.minCredit DESC LIMIT 1

2.

SELECT g.no
FROM grocery g, distributor dd, donut d
WHERE g.no = dd.gno
  AND d.name = dd.dname
  AND d.price >= 2