SQL错误分组-“不是GROUP BY表达式"; [英] SQL Group by error - "not a GROUP BY expression"
问题描述
我的SQL查询似乎有问题,这使我发疯了.我似乎无法让小组开始工作.我不断收到以下错误:
I seem to be having a problem with my SQL query that's driving me mad. I just can't seem to get the group by to work. I keep getting the following error:
00979. 00000 - "not a GROUP BY expression"
我的查询:
SELECT customers.customer_first_name, customers.customer_last_name, orders.customer_numb, books.author_name, books.title
FROM customers
LEFT OUTER JOIN orders ON (orders.customer_numb = customers.customer_numb)
LEFT OUTER JOIN order_lines ON (order_lines.order_numb = orders.order_numb)
LEFT OUTER JOIN books ON (books.isbn = order_lines.isbn)
WHERE (customers.customer_numb = 6)
GROUP BY (books.title)
数据库架构:
客户:
订单行和订单:
我要实现的目标: 我正在尝试按书名分组,以免显示重复的书名.
What I'm trying to achieve: I'm trying to group by book titles so that it doesn't show duplicate titles.
如果您有任何遗漏,我深表歉意.
I apologise if I've missed anything, thanks.
推荐答案
您不明白什么?您的select语句包含许多不在group by
子句中的列.试试这个:
What don't you understand? Your select statement contains many columns that are not in the group by
clause. Try this:
SELECT customers.customer_first_name, customers.customer_last_name, orders.customer_numb, books.author_name, books.title
FROM customers
LEFT OUTER JOIN orders ON (orders.customer_numb = customers.customer_numb)
LEFT OUTER JOIN order_lines ON (order_lines.order_numb = orders.order_numb)
LEFT OUTER JOIN books ON (books.isbn = order_lines.isbn)
WHERE (customers.customer_numb = 6)
GROUP BY customers.customer_first_name, customers.customer_last_name, orders.customer_numb, books.author_name, books.title
由于您不进行任何汇总,因此可以省去group by
,而只需在select
子句中使用distinct
:
Since you are not aggregating anything, you can dispense with the group by
, and just use distinct
in the select
clause:
select distinct customers.customer_first_name, customers.customer_last_name, orders.customer_numb, books.author_name, books.title
这篇关于SQL错误分组-“不是GROUP BY表达式";的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!