"选出金额最大的订单 SQL WITH m AS ( SELECT max(Amount) mta FROM Orders WHERE OrderDate>='2022-01-01') S .."

Mars 开封
乾学院 6 号会员
148 浏览 • 1 年前

...

2.3 最大最小值所在的记录

计算＆AI

选出金额最大的订单
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(;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