如何在选择的另一列上使用 Max 函数

发布于 2024-09-17 16:50:54 字数 974 浏览 6 评论 0原文

有一个查询，询问每个顾客购买的最喜欢的产品。我必须选择，在第一个选择中，我选择了每个客户购买的产品数量。在另一个选择中，我想选择为每个客户购买的最大值。但是当我想选择最大值（前一个选择列）时，它会出现错误并说它未定义，任何人都可以帮助我如何解决此问题。我非常有动力通过这种方式解决问题，并且我不愿意使用其他方法，例如创建视图或类似的方法。任何人都可以帮助我解决这个问题：

SELECT INN.Maximum,INN.Name, customer.ProductName from
(SELECT ContactName, ProductName, COUNT([Order Details].Quantity) AS NumOftimeCustomer
FROM Orders, [Order Details], Products, Customers
WHERE [Order Details].OrderID = Orders.OrderID
AND [Order Details].ProductID = Products.ProductID
AND Orders.CustomerID = Customers.CustomerID
GROUP BY ContactName, ProductName)customer
INNER JOIN
(SELECT Customers.ContactName AS Name, **MAX(customer.numOftimecustomer)** AS Maximum 
from    Customers, customer 
GROUP BY Customers.ContactName) INN
ON INN.Name = customer.ContactName AND INN.Maximum = customer.NumOftimeCustomer

MAX(customer.numOftimecustomer) ** 提到的部分是给出错误的部分，它表示对象 customer 未定义。有没有办法解决没有view的问题？为什么会这样呢？因为我定义的客户不是一张桌子？

原文

There is query which is asking for favourite products which is bought by each coustomer. i have to select and in the first select i have selected the count of products that each customer bought. in the other select i want to select the maximum of that boughts for each customer.but when i want to select max(previous select column) it gets and error and says it is not defined can any one helps me how to fix this problem. i am very motivated to solve the problem from this way, and i am not willing to use other methods like creating view or something like that. can any one help me on this:

SELECT INN.Maximum,INN.Name, customer.ProductName from
(SELECT ContactName, ProductName, COUNT([Order Details].Quantity) AS NumOftimeCustomer
FROM Orders, [Order Details], Products, Customers
WHERE [Order Details].OrderID = Orders.OrderID
AND [Order Details].ProductID = Products.ProductID
AND Orders.CustomerID = Customers.CustomerID
GROUP BY ContactName, ProductName)customer
INNER JOIN
(SELECT Customers.ContactName AS Name, **MAX(customer.numOftimecustomer)** AS Maximum 
from    Customers, customer 
GROUP BY Customers.ContactName) INN
ON INN.Name = customer.ContactName AND INN.Maximum = customer.NumOftimeCustomer

that part which is mentioned with MAX(customer.numOftimecustomer) ** is the part which gives error and it says the object customer is not defined. is there a way to solve it without view? why is it in this way? since the customer that i defined is not a table?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

戈亓 2024-09-24 16:50:54

这就是你想要的：

select 
    *
 from  (SELECT ContactName, ProductName, COUNT([Order Details].Quantity) AS NumOftimeCustomer
    FROM Orders, [Order Details], Products, Customers
    WHERE [Order Details].OrderID = Orders.OrderID
    AND [Order Details].ProductID = Products.ProductID
    AND Orders.CustomerID = Customers.CustomerID
    GROUP BY ContactName, ProductName)customer
where customer.num = (select max(num) from

(SELECT ContactName, ProductName, COUNT([Order Details].Quantity) AS NumOftimeCustomer
    FROM Orders, [Order Details], Products, Customers
    WHERE [Order Details].OrderID = Orders.OrderID
    AND [Order Details].ProductID = Products.ProductID
    AND Orders.CustomerID = Customers.CustomerID
    GROUP BY ContactName, ProductName)customer2
 where customer2.name = customer.name)

here is what you want:

select 
    *
 from  (SELECT ContactName, ProductName, COUNT([Order Details].Quantity) AS NumOftimeCustomer
    FROM Orders, [Order Details], Products, Customers
    WHERE [Order Details].OrderID = Orders.OrderID
    AND [Order Details].ProductID = Products.ProductID
    AND Orders.CustomerID = Customers.CustomerID
    GROUP BY ContactName, ProductName)customer
where customer.num = (select max(num) from

(SELECT ContactName, ProductName, COUNT([Order Details].Quantity) AS NumOftimeCustomer
    FROM Orders, [Order Details], Products, Customers
    WHERE [Order Details].OrderID = Orders.OrderID
    AND [Order Details].ProductID = Products.ProductID
    AND Orders.CustomerID = Customers.CustomerID
    GROUP BY ContactName, ProductName)customer2
 where customer2.name = customer.name)

回复收藏 0 原文

~没有更多了~