2.3 最大最小值所在的记录
选出金额最大的订单
SQL
WITH m AS (
SELECT max(Amount) mta
FROM Orders
WHERE OrderDate>='2022-01-01')
SELECT Orders.CustomerID,OrderDate,Amount,
Quantity, EmployeeID,EmployeeName,ShipVia
FROM m,Orders
WHERE m.mta=Orders.Amount and OrderDate>='2022-01-01'
SPL
| A | |
|---|---|
| 1 | >st=date(“2022-01-01”), start=days@o(st) |
| 2 | =file(“Orders.ctx”).open().cursor@x(;OrderDate>=start) |
| 3 | =A2.total(maxp@a(Amount)) |
A3 maxp@a(Amount) 表示获得 Amount 值最大的所有记录, 最大金额的订单可能有多个，要用 @a 全取出返回
选出每个销售员的最后签订的订单 SQL
WITH m AS (
SELECT EmployeeID,max(OrderDate) md
FROM Orders
WHERE Amount>1000
GROUP BY EmployeeID)
SELECT Orders.CustomerID,OrderDate,Amount,
Quantity, Orders.EmployeeID,EmployeeName,ShipVia
FROM m, Orders
WHERE m.EmployeeID =Orders.EmployeeID and m.md=Orders.OrderDate and Amount>1000
SPL
| A | |
|---|---|
| 1 | =file(“Orders.ctx”).open().cursor@x(;Amount>1000) |
| 2 | =A1.groups(EmployeeID; maxp(OrderDate)).(#2) |
| 3 | =A2.run(OrderDate=date@o(OrderDate)) |
A2 假定每个销售员最后签订的订单是唯一的，可以不用 @a 选项，直接 maxp 返回取值最大值的单条记录即可
还可以用 top 函数来写
| A | |
|---|---|
| 2 | =A1.groups(EmployeeID; top@1(1;-OrderDate)).(#2) |
A2 top@1(1;-OrderDate) 表示取 OrderDate 值最大的一条记录，如果不加负号则表示取值最小的记录
