我试图在SQL Server上执行一个简单的左连接,但它一直给我相同的消息。
Select * from customers left join orders on customers.id = orders.customer_id group by customers.id order by amount;
Msg 8120, 16层,状态1,39行列的客户。first_name"是在选择列表中无效,因为它不包含在
我不知道还能做什么。如果有帮助的话,下面是我的表格:
CREATE TABLE customers(id INT IDENTITY(1,1) PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100), email VARCHAR(100));
CREATE TABLE orders(id INT IDENTITY(1,1) PRIMARY KEY, order_date DATE, amount DECIMAL(8,2), customer_id INT, FOREIGN KEY(customer_id) REFERENCES customers(id));
一旦您使用GROUP BY
创建聚合,您只能SELECT
您分组的内容和聚合函数(例如,MAX
,MIN
,SUM
,COUNT
等)。查询:
SELECT *
FROM customers
LEFT JOIN orders on customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY amount;
因为你的GROUP BY customers.id
,唯一的东西可以出现在你的SELECT
列表是customers.id
和聚合函数。您会得到错误,因为*
表示所有列,但您只允许使用customers.id
和聚合函数。例如,可以这样做:
SELECT customers.id
FROM customers
LEFT JOIN orders on customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY amount;
至于你想看到什么,我无法从这个查询中得知。如果您想查看每个客户id的最小订单,您可以使用:
SELECT customers.id, MIN(orders.amount) AS [SmallestOrder]
FROM customers
LEFT JOIN orders on customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY amount;
所以错误的原因现在很清楚了,但是你想找到的数据还不清楚。