我在SQL Server 2008 R2数据库中具有以下表:
Customers
=========
CustID CustName
====== ========
0 NULL
1 A
2 B
3 C
InterestingCustomers
====================
CustID
======
0
1
3
Orders
======
CustID OrderID InvoiceTotal Discount
====== ======= ============ ========
0 NULL 2000 NULL
0 100 NULL 500
1 1 100 NULL
1 2 90 15
2 3 300 25
2 4 50 0
3 5 100 10
3 6 200 25
3 7 150 NULL
3 8 120 20
我想要一个查询,该查询显示所有订单中每个CustID和CustName的InvoiceTotal和Discount列的最大值,但前提是客户出现在InterestingCutomers中且ID> 0:
CustID CustName MaxInvoiceTotal MaxDiscount
1 A 100 15
3 C 200 25
我遇到的一种麻烦是,我的应用程序是使用Jet 4.0和ADO的旧版VB6。这缺乏对托管数据库的SQL Server 2008的许多高级功能的支持。此处列出了我可用的SQL函数:https : //support.microsoft.com/zh-cn/kb/294698。
到目前为止,我已经能够获得最大的InvoiceTotal,但是我无法扩展相同的查询来返回其他任何列的最大值。
以下正确返回每个有效CustID的最大InvoiceTotal:
SELECT Customers.CustID, CustName, InvoiceTotal, Discount
FROM ((Customers INNER JOIN Interesting ON Customers.CustID = Interesting.CustID) INNER JOIN Orders ON Customers.CustID = Orders.CustID)
WHERE ((CustID > 0)
AND (InvoiceTotal IN
(SELECT MAX(InvoiceTotal) AS MaxInvoiceTotal
FROM Orders
WHERE Orders.CustID = Customers.CustID)))
返回值:
CustID CustName MaxInvoiceTotal
1 A 100
3 C 200
但是,扩展WHERE子句以将每位客户的Discount限制为最大值只能返回InvoiceTotal和Discount都恰好是最大值并且属于同一订单的记录:
WHERE ((CustID > 0) AND (InvoiceTotal IN
(SELECT MAX(InvoiceTotal) AS MaxInvoiceTotal
FROM Orders
WHERE Orders.CustID = Customers.CustID)) AND
Discount IN
(SELECT MAX(Discount) AS MaxDiscount
FROM Orders
WHERE Orders.CustID = Customers.CustID))
返回值:
CustID CustName MaxInvoiceTotal MaxDiscount
3 C 200 25
未选择CustID 1,因为其最大InvoiceTotal值与其最大折扣值属于不同的顺序。我假设这两个条件都是针对符合条件的每个订单进行测试的,但是我想以某种方式删除OrderID与InvoiceTotal和Discount之间的关联。OR-ing附加子句不起作用。
我怀疑我可能在UNION
这里,但鉴于我所概述的限制,我想检查是否有一种更清洁或更有效的方法。
SELECT Customers.CustID, CustName, MaxInvoiceTotal, MaxDiscount
FROM Customers
INNER JOIN Interesting ON Customers.CustID = Interesting.CustID
INNER JOIN (SELECT CustID, MAX(Discount) AS MaxDiscount
FROM Orders
Group by CustID) MaxDiscountOrders ON Customers.CustID =
MaxDiscountOrders.CustID
INNER JOIN (SELECT CustID, MAX(InvoiceTotal) AS MaxInvoiceTotal
FROM Orders
Group by CustID) MaxInvoiceTotalOrders ON Customers.CustID
= MaxInvoiceTotalOrders.CustID
WHERE CustID > 0
尝试一下。。我还没有检查语法错误。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句