使用oraclesql对图书借阅场景进行基于不同计数的数据递增/递减

ars1skjm  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(305)

我有一个需要在oraclesql中处理的场景-这是一个要求。
这是我正在尝试创建的图书借阅场景。
每一个学生只能得到这么多基于学分制的书。在此示例中，学号为“100”的学生有权购买4本书。
需要生成学生到目前为止已签入的图书数量。
列标题“#此学生下的活动书籍”下的数据是我试图通过sql实现的
在某些情况下，由于条形码扫描数据无效，记录可能无效-在这种情况下，我们需要保持学生有权获得的相同计数。这在列标题“定期更新”和“更新状态”中可用
有谁能帮我建立一个oraclesql来实现列标题“#activebooks under this student”下的数据吗？
谢谢lakshminarasu chenduri

  1. +------------+-----------+---------+------------+--------------------------------+----------------------------------+------------------+----------------+
  2. | Student ID |   Date    | Book ID | Check Out? | Total Books that can be issued | #Active Books under this student | Recurring Update | Update Status  |
  3. +------------+-----------+---------+------------+--------------------------------+----------------------------------+------------------+----------------+
  4. |        101 | 14-Apr-20 |       2 | N          |                              4 |                                1 |                  |                |
  5. |        101 | 17-Apr-20 |       2 | Y          |                              4 |                                0 |                  |                |
  6. |        101 | 17-Apr-20 |       4 | N          |                              4 |                                1 |                  |                |
  7. |        101 | 24-Apr-20 |       1 | N          |                              4 |                                2 |                  |                |
  8. |        101 | 24-Apr-20 |       3 | N          |                              4 |                                3 |                  |                |
  9. |        101 | 24-Apr-20 |       4 | Y          |                              4 |                                2 |                  |                |
  10. |        101 | 27-Apr-20 |       1 | Y          |                              4 |                                1 |                  |                |
  11. |        101 | 27-Apr-20 |       3 | Y          |                              4 |                                0 |                  |                |
  12. |        101 | 27-Apr-20 |       4 | N          |                              4 |                                1 |                  |                |
  13. |        101 | 1-May-20  |       1 | N          |                              4 |                                2 |                  |                |
  14. |        101 | 1-May-20  |       2 | N          |                              4 |                                3 |                  |                |
  15. |        101 | 1-May-20  |       4 | Y          |                              4 |                                2 |                  |                |
  16. |        101 | 3-May-20  |       3 | N          |                              4 |                                3 |                  |                |
  17. |        101 | 3-May-20  |       4 | N          |                              4 |                                4 |                  |                |
  18. |        101 | 4-May-20  |       2 | Y          |                              4 |                                3 |                  |                |
  19. |        101 | 4-May-20  |       4 | Y          |                              4 |                                2 |                  |                |
  20. |        101 | 8-May-20  |       1 | Y          |                              4 |                                1 |                  |                |
  21. |        101 | 10-May-20 |       2 | N          |                              4 |                                2 |                  |                |
  22. |        101 | 10-May-20 |       3 | Y          |                              4 |                                1 |                  |                |
  23. |        101 | 17-May-20 |       1 | N          |                              4 |                                2 |                  |                |
  24. |        101 | 18-May-20 |       1 | Y          |                              4 |                                1 |                  |                |
  25. |        101 | 18-May-20 |       2 | Y          |                              4 |                                0 |                  |                |
  26. |        101 | 18-May-20 |       4 | N          |                              4 |                                1 |                  |                |
  27. |        101 | 19-May-20 |       3 | N          |                              4 |                                2 |                  |                |
  28. |        101 | 19-May-20 |       4 | Y          |                              4 |                                1 |                  |                |
  29. |        101 | 22-May-20 |       3 | Y          |                              4 |                                0 |                  |                |
  30. |        101 | 22-May-20 |       4 | N          |                              4 |                                1 |                  |                |
  31. |        101 | 27-May-20 |       3 | N          |                              4 |                                2 |                  |                |
  32. |        101 | 27-May-20 |       4 | Y          |                              4 |                                1 |                  |                |
  33. |        101 | 28-May-20 |       2 | N          |                              4 |                                2 |                  |                |
  34. |        101 | 28-May-20 |       3 | Y          |                              4 |                                1 |                  |                |
  35. |        101 | 7-Jun-20  |       1 | N          |                              4 |                                2 |                  |                |
  36. |        101 | 7-Jun-20  |       2 | N          |                              4 |                                3 |                  |                |
  37. |        101 | 9-Jun-20  |       4 | N          |                              4 |                                4 |                  |                |
  38. |        101 | 14-Jun-20 |       1 | N          |                              4 |                                4 |                5 | INVALID UPDATE |
  39. |        101 | 14-Jun-20 |       4 | N          |                              4 |                                4 |                6 | INVALID UPDATE |
  40. |        101 | 15-Jun-20 |       1 | Y          |                              4 |                                3 |                  |                |
  41. |        101 | 15-Jun-20 |       4 | N          |                              4 |                                4 |                  |                |
  42. |        101 | 28-Jun-20 |       3 | N          |                              4 |                                4 |                5 | INVALID UPDATE |
  43. |        101 | 28-Jun-20 |       4 | Y          |                              4 |                                3 |                  |                |
  44. |        101 | 29-Jun-20 |       2 | N          |                              4 |                                4 |                  |                |
  45. |        101 | 29-Jun-20 |       3 | Y          |                              4 |                                3 |                  |                |
  46. |        101 | 2-Jul-20  |       1 | N          |                              4 |                                4 |                  |                |
  47. |        101 | 2-Jul-20  |       2 | N          |                              4 |                                4 |                5 | INVALID UPDATE |
  48. |        101 | 6-Jul-20  |       1 | Y          |                              4 |                                3 |                  |                |
  49. |        101 | 6-Jul-20  |       2 | N          |                              4 |                                4 |                  |                |
  50. |        101 | 23-Jul-20 |       2 | N          |                              4 |                                4 |                5 | INVALID UPDATE |
  51. |        101 | 24-Jul-20 |       1 | N          |                              4 |                                4 |                6 | INVALID UPDATE |
  52. |        101 | 24-Jul-20 |       2 | N          |                              4 |                                4 |                7 | INVALID UPDATE |
  53. |        101 | 28-Jul-20 |       1 | Y          |                              4 |                                3 |                  |                |
  54. |        101 | 28-Jul-20 |       2 | N          |                              4 |                                2 |                  |                |
  55. +------------+-----------+---------+------------+--------------------------------+----------------------------------+------------------+----------------+
sqlcountoracleDistinctIncrement

1条答案

按热度按时间
bhmjp9jg

bhmjp9jg1#

您可以使用一个运行的总和来实现这一点。缺少一条信息。您假设数据是按您提供的顺序存储的，但不会在任何列中捕获。因此，在我的示例中，我添加了一个列事务\u id。现在我可以保证select中的顺序相同。如果date列将包含一个时间部分（它可能包含，但它不包括在示例数据中），那么 ORDER BY dt) 就够了。

  1. create table so_students
  2. (student_id NUMBER,
  3.  dt DATE,
  4.  book_id NUMBER,
  5.  checkout VARCHAR2(1),
  6.  total_books NUMBER,
  7.  active_books NUMBER,
  8.  recurring_update NUMBER,
  9.  update_status  VARCHAR2(100),
  10.  transaction_id NUMBER
  11. );
  13. create sequence so_students_s;
  15. INSERT INTO so_students values (101,TO_DATE('14-Apr-20','DD-Mon-YY'),   2,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  16. INSERT INTO so_students values (101,TO_DATE('17-Apr-20','DD-Mon-YY'),   2,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
  17. INSERT INTO so_students values (101,TO_DATE('17-Apr-20','DD-Mon-YY'),   4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  18. INSERT INTO so_students values (101,TO_DATE('24-Apr-20','DD-Mon-YY'),   1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  19. INSERT INTO so_students values (101,TO_DATE('24-Apr-20','DD-Mon-YY'),   3,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
  20. INSERT INTO so_students values (101,TO_DATE('24-Apr-20','DD-Mon-YY'),   4,'Y', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  21. INSERT INTO so_students values (101,TO_DATE('27-Apr-20','DD-Mon-YY'),   1,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  22. INSERT INTO so_students values (101,TO_DATE('27-Apr-20','DD-Mon-YY'),   3,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
  23. INSERT INTO so_students values (101,TO_DATE('27-Apr-20','DD-Mon-YY'),   4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  24. INSERT INTO so_students values (101,TO_DATE('1-May-20','DD-Mon-YY'),    1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  25. INSERT INTO so_students values (101,TO_DATE('1-May-20','DD-Mon-YY'),    2,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
  26. INSERT INTO so_students values (101,TO_DATE('1-May-20','DD-Mon-YY'),    4,'Y', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  27. INSERT INTO so_students values (101,TO_DATE('3-May-20','DD-Mon-YY'),    3,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
  28. INSERT INTO so_students values (101,TO_DATE('3-May-20','DD-Mon-YY'),    4,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
  29. INSERT INTO so_students values (101,TO_DATE('4-May-20','DD-Mon-YY'),    2,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
  30. INSERT INTO so_students values (101,TO_DATE('4-May-20','DD-Mon-YY'),    4,'Y', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  31. INSERT INTO so_students values (101,TO_DATE('8-May-20','DD-Mon-YY'),    1,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  32. INSERT INTO so_students values (101,TO_DATE('10-May-20','DD-Mon-YY'),   2,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  33. INSERT INTO so_students values (101,TO_DATE('10-May-20','DD-Mon-YY'),   3,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  34. INSERT INTO so_students values (101,TO_DATE('17-May-20','DD-Mon-YY'),   1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  35. INSERT INTO so_students values (101,TO_DATE('18-May-20','DD-Mon-YY'),   1,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  36. INSERT INTO so_students values (101,TO_DATE('18-May-20','DD-Mon-YY'),   2,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
  37. INSERT INTO so_students values (101,TO_DATE('18-May-20','DD-Mon-YY'),   4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  38. INSERT INTO so_students values (101,TO_DATE('19-May-20','DD-Mon-YY'),   3,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  39. INSERT INTO so_students values (101,TO_DATE('19-May-20','DD-Mon-YY'),   4,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  40. INSERT INTO so_students values (101,TO_DATE('22-May-20','DD-Mon-YY'),   3,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
  41. INSERT INTO so_students values (101,TO_DATE('22-May-20','DD-Mon-YY'),   4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  42. INSERT INTO so_students values (101,TO_DATE('27-May-20','DD-Mon-YY'),   3,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  43. INSERT INTO so_students values (101,TO_DATE('27-May-20','DD-Mon-YY'),   4,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  44. INSERT INTO so_students values (101,TO_DATE('28-May-20','DD-Mon-YY'),   2,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  45. INSERT INTO so_students values (101,TO_DATE('28-May-20','DD-Mon-YY'),   3,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
  46. INSERT INTO so_students values (101,TO_DATE('7-Jun-20','DD-Mon-YY'),    1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  47. INSERT INTO so_students values (101,TO_DATE('7-Jun-20','DD-Mon-YY'),    2,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
  48. INSERT INTO so_students values (101,TO_DATE('9-Jun-20','DD-Mon-YY'),    4,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
  49. INSERT INTO so_students values (101,TO_DATE('14-Jun-20','DD-Mon-YY'),   1,'N', 4, 4,5,'INVALID UPDATE',so_students_s.NEXTVAL);
  50. INSERT INTO so_students values (101,TO_DATE('14-Jun-20','DD-Mon-YY'),   4,'N', 4, 4,6,'INVALID UPDATE',so_students_s.NEXTVAL);
  51. INSERT INTO so_students values (101,TO_DATE('15-Jun-20','DD-Mon-YY'),   1,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
  52. INSERT INTO so_students values (101,TO_DATE('15-Jun-20','DD-Mon-YY'),   4,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
  53. INSERT INTO so_students values (101,TO_DATE('28-Jun-20','DD-Mon-YY'),   3,'N', 4, 4,5,'INVALID UPDATE',so_students_s.NEXTVAL);
  54. INSERT INTO so_students values (101,TO_DATE('28-Jun-20','DD-Mon-YY'),   4,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
  55. INSERT INTO so_students values (101,TO_DATE('29-Jun-20','DD-Mon-YY'),   2,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
  56. INSERT INTO so_students values (101,TO_DATE('29-Jun-20','DD-Mon-YY'),   3,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
  57. INSERT INTO so_students values (101,TO_DATE('2-Jul-20','DD-Mon-YY'),    1,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
  58. INSERT INTO so_students values (101,TO_DATE('2-Jul-20','DD-Mon-YY'),    2,'N', 4, 4,5,'INVALID UPDATE',so_students_s.NEXTVAL);
  59. INSERT INTO so_students values (101,TO_DATE('6-Jul-20','DD-Mon-YY'),    1,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
  60. INSERT INTO so_students values (101,TO_DATE('6-Jul-20','DD-Mon-YY'),    2,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
  61. INSERT INTO so_students values (101,TO_DATE('23-Jul-20','DD-Mon-YY'),   2,'N', 4, 4,5,'INVALID UPDATE',so_students_s.NEXTVAL);
  62. INSERT INTO so_students values (101,TO_DATE('24-Jul-20','DD-Mon-YY'),   1,'N', 4, 4,6,'INVALID UPDATE',so_students_s.NEXTVAL);
  63. INSERT INTO so_students values (101,TO_DATE('24-Jul-20','DD-Mon-YY'),   2,'N', 4, 4,7,'INVALID UPDATE',so_students_s.NEXTVAL);
  64. INSERT INTO so_students values (101,TO_DATE('28-Jul-20','DD-Mon-YY'),   1,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
  65. INSERT INTO so_students values (101,TO_DATE('28-Jul-20','DD-Mon-YY'),   2,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
  67. SELECT SUM(
  68.   CASE update_status
  69.     WHEN 'INVALID UPDATE' THEN
  70.       0
  71.     ELSE
  72.       CASE checkout
  73.         WHEN 'N' THEN
  74.           1
  75.         ELSE
  76.           - 1
  77.       END
  78.   END
  79. ) OVER(
  80.  ORDER BY dt,transaction_id) AS running_sum,
  81.        active_books,
  82.        s.*
  83.   FROM so_students s;
展开查看全部
赞(0）分享  回复(0）举报  2021-07-26
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com