mysql - Clarification Question with MAX and COUNT (sql)

Question

I am doing a couple homework problems and am a little confused how to use COUNT and MAX in my queries. I was wondering if someone can explain to me the 'where' part of these, or if I am just writing my coded completely wrong:

The Catalog relation lists the prices charged for parts by Suppliers. Catalog.pid is a foreign key to Parts.pid. Catalog.sid is a foreign key to Suppliers.sid. Write the following queries in SQL. Execute them to make sure you get the correct answers. Copy the SQL queries and include the screenshot of the results as the answers. Do not list other attributes not specified in the return results.

Catalog contains: pid, sid, cost

Parts contains: pid, pname, color

Supplier constains: sid, sname, address

c. (2 points) Return the number of unique parts.

select count (distinct *) from Parts;

--This produces a compilation error on select, does anyone have advice for this?

i. (1 point) Find the pids, pnames, and the maximum cost for that part among all the suppliers.

select p.pid, p.pname, c.cost from Parts p 
  inner join Catalog c on c.sid = p.pid
  inner join Suppliers s on c.pid = s.sid
  where max(c.cost);

I am not entirely sure if I am starting this correctly and I dont know what to consider when doing the 'where' part.. is it always needed?

标签： mysqlsql

Answer 1

对于不同的部件 id，部件由 id 标识

select count(distinct pid) from Parts;

或者如果 pid 是 auto_increment 你可以的行

select count(*) from Parts;

您不应该在 where 条件中使用聚合函数 ..例如：where max(c.cost) 在执行聚合函数之前首先评估 where 条件，因此如果您想要过滤聚合结果，则 where 子句无法知道 max() 的值您应该使用有

例如 having c.cost = max(c.cost) ，但在这种情况下，您不能对 select 中的非聚合列使用没有 group by 的聚合函数，并得到另一种类型的错误。

但如果您还需要其他列，则可以使用子查询

    select p.pid, p.pname, c.cost 
    from Parts p 
    inner join Catalog c on c.sid = p.pid
    inner join Suppliers s on c.pid = s.sid
    inner join  (
        select max(cost) max_cost
        from Catalog
    ) t on t.max_cost = c.cost