Guest User

Untitled

a guest
Jun 17th, 2018
75
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.99 KB | None | 0 0
  1. Student ID Student Name Student Money
  2. --------- ----------- --------------
  3. 1 John 190
  4. 2 Jenny 290
  5. 3 Ben 200
  6. 4 Andy 120
  7. 5 Lynna 300
  8.  
  9. SELECT MAX(N) FROM INTS
  10. WHERE (SELECT SUM(money) FROM student ORDER BY xxx LIMIT N) < 1000
  11.  
  12. SELECT Student.*, SUM(StudentBefore.Money) AS AccumulatedMoney
  13. FROM (
  14. SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber
  15. FROM Students
  16. ) AS Student
  17. INNER JOIN
  18. (
  19. SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber
  20. FROM Students
  21. ) AS StudentBefore
  22. ON StudentBefore.RowNumber <= Student.RowNumber
  23. GROUP BY Student.RowNumber, Student.Id, Student.Name, Student.Money
  24. HAVING SUM(StudentBefore.Money) < 1000
  25.  
  26. SELECT s1.ID, s1.name, s1.money, sum(s2.money)
  27. FROM student s1
  28. INNER JOIN student s2 ON s1.id >= s2.id
  29. GROUP BY s1.id HAVING SUM(s2.money) <= 500;
Add Comment
Please, Sign In to add comment