我是一个初学者,正在尝试学习SQL。在我正在做的练习中,我输入了以下查询并收到错误"FROM 子句中的语法错误"
select orders.customerid, customers.customername, count(orders.customerid)
from orders
join customers on customers.customerid = orders.customerid
group by orders.customerid
order by count(orders.customerid)
在W3Schools SQL中,需要指定与join
关键字一起使用的连接类型(inner,left,right)。
使用 join customers on customers.customerid = orders.customerid
会给出您在 FROM 子句中得到语法错误的错误。
将联接更改为inner join
可以修复该错误,但还有另一个问题,即 group by 语句中缺少列: 您尝试执行的查询不包含指定的表达式"customername"作为聚合函数的一部分。
修复后,查询将如下所示:
select orders.customerid, customers.customername, count(orders.customerid)
from orders
inner join customers on customers.customerid = orders.customerid
group by orders.customerid, customers.customername
order by count(orders.customerid);
但是,如果要返回所有客户,而不管是否有任何订单,则应将查询更改为使用左联接:
select orders.customerid, customers.customername, count(orders.customerid)
from customers
left join orders on customers.customerid = orders.customerid
group by orders.customerid, customers.customername
order by count(orders.customerid);
附带说明一下,这可能是使用别名的好时机:
select o.customerid, c.customername, count(o.customerid) as count_of_orders
from customers as c
left join orders as o on c.customerid = o.customerid
group by o.customerid, c.customername
order by count(o.customerid);
看看查询缩短了多少?:)