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屋！