"SQL SELECT floor((year(curdate())-year(Employees.Birthday))/10) Age, sum(Orders.Amount) Amount F .."

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

...

4.2 维表上的计算

计算＆AI

SQL

SELECT floor((year(curdate())-year(Employees.Birthday))/10) Age,
    sum(Orders.Amount) Amount
FROM Orders
LEFT JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID
WHERE OrderDate>='2021-01-01' and OrderDate<='2021-12-31'
GROUP BY floor((year(curdate())-year(Employees.Birthday))/10)

SPL

	A
1	>st=date(“2021-01-01”), et=date(“2021-12-31”), start=days@o(st), end=days@o(et)
2	>Employees=file(“Employees.btx”).import@b().derive(age(Birthday)\10:Age).keys@i(EmployeeID)
3	=file(“Orders_Time.ctx”).open().cursor@m(EmployeeID,Amount;OrderDate>=start && OrderDate<=end)
4	=A3.switch(EmployeeID,Employees:EmployeeID)
5	=A4.groups(EmployeeID.Age;sum(Amount):Amount)