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

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

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

+------------+-----------+---------+------------+--------------------------------+----------------------------------+------------------+----------------+
| Student ID |   Date    | Book ID | Check Out? | Total Books that can be issued | #Active Books under this student | Recurring Update | Update Status  |
+------------+-----------+---------+------------+--------------------------------+----------------------------------+------------------+----------------+
|        101 | 14-Apr-20 |       2 | N          |                              4 |                                1 |                  |                |
|        101 | 17-Apr-20 |       2 | Y          |                              4 |                                0 |                  |                |
|        101 | 17-Apr-20 |       4 | N          |                              4 |                                1 |                  |                |
|        101 | 24-Apr-20 |       1 | N          |                              4 |                                2 |                  |                |
|        101 | 24-Apr-20 |       3 | N          |                              4 |                                3 |                  |                |
|        101 | 24-Apr-20 |       4 | Y          |                              4 |                                2 |                  |                |
|        101 | 27-Apr-20 |       1 | Y          |                              4 |                                1 |                  |                |
|        101 | 27-Apr-20 |       3 | Y          |                              4 |                                0 |                  |                |
|        101 | 27-Apr-20 |       4 | N          |                              4 |                                1 |                  |                |
|        101 | 1-May-20  |       1 | N          |                              4 |                                2 |                  |                |
|        101 | 1-May-20  |       2 | N          |                              4 |                                3 |                  |                |
|        101 | 1-May-20  |       4 | Y          |                              4 |                                2 |                  |                |
|        101 | 3-May-20  |       3 | N          |                              4 |                                3 |                  |                |
|        101 | 3-May-20  |       4 | N          |                              4 |                                4 |                  |                |
|        101 | 4-May-20  |       2 | Y          |                              4 |                                3 |                  |                |
|        101 | 4-May-20  |       4 | Y          |                              4 |                                2 |                  |                |
|        101 | 8-May-20  |       1 | Y          |                              4 |                                1 |                  |                |
|        101 | 10-May-20 |       2 | N          |                              4 |                                2 |                  |                |
|        101 | 10-May-20 |       3 | Y          |                              4 |                                1 |                  |                |
|        101 | 17-May-20 |       1 | N          |                              4 |                                2 |                  |                |
|        101 | 18-May-20 |       1 | Y          |                              4 |                                1 |                  |                |
|        101 | 18-May-20 |       2 | Y          |                              4 |                                0 |                  |                |
|        101 | 18-May-20 |       4 | N          |                              4 |                                1 |                  |                |
|        101 | 19-May-20 |       3 | N          |                              4 |                                2 |                  |                |
|        101 | 19-May-20 |       4 | Y          |                              4 |                                1 |                  |                |
|        101 | 22-May-20 |       3 | Y          |                              4 |                                0 |                  |                |
|        101 | 22-May-20 |       4 | N          |                              4 |                                1 |                  |                |
|        101 | 27-May-20 |       3 | N          |                              4 |                                2 |                  |                |
|        101 | 27-May-20 |       4 | Y          |                              4 |                                1 |                  |                |
|        101 | 28-May-20 |       2 | N          |                              4 |                                2 |                  |                |
|        101 | 28-May-20 |       3 | Y          |                              4 |                                1 |                  |                |
|        101 | 7-Jun-20  |       1 | N          |                              4 |                                2 |                  |                |
|        101 | 7-Jun-20  |       2 | N          |                              4 |                                3 |                  |                |
|        101 | 9-Jun-20  |       4 | N          |                              4 |                                4 |                  |                |
|        101 | 14-Jun-20 |       1 | N          |                              4 |                                4 |                5 | INVALID UPDATE |
|        101 | 14-Jun-20 |       4 | N          |                              4 |                                4 |                6 | INVALID UPDATE |
|        101 | 15-Jun-20 |       1 | Y          |                              4 |                                3 |                  |                |
|        101 | 15-Jun-20 |       4 | N          |                              4 |                                4 |                  |                |
|        101 | 28-Jun-20 |       3 | N          |                              4 |                                4 |                5 | INVALID UPDATE |
|        101 | 28-Jun-20 |       4 | Y          |                              4 |                                3 |                  |                |
|        101 | 29-Jun-20 |       2 | N          |                              4 |                                4 |                  |                |
|        101 | 29-Jun-20 |       3 | Y          |                              4 |                                3 |                  |                |
|        101 | 2-Jul-20  |       1 | N          |                              4 |                                4 |                  |                |
|        101 | 2-Jul-20  |       2 | N          |                              4 |                                4 |                5 | INVALID UPDATE |
|        101 | 6-Jul-20  |       1 | Y          |                              4 |                                3 |                  |                |
|        101 | 6-Jul-20  |       2 | N          |                              4 |                                4 |                  |                |
|        101 | 23-Jul-20 |       2 | N          |                              4 |                                4 |                5 | INVALID UPDATE |
|        101 | 24-Jul-20 |       1 | N          |                              4 |                                4 |                6 | INVALID UPDATE |
|        101 | 24-Jul-20 |       2 | N          |                              4 |                                4 |                7 | INVALID UPDATE |
|        101 | 28-Jul-20 |       1 | Y          |                              4 |                                3 |                  |                |
|        101 | 28-Jul-20 |       2 | N          |                              4 |                                2 |                  |                |
+------------+-----------+---------+------------+--------------------------------+----------------------------------+------------------+----------------+
sqlcountoracleDistinctIncrement

1条答案

按热度按时间
bhmjp9jg

bhmjp9jg1#

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

create table so_students
(student_id NUMBER,
 dt DATE,
 book_id NUMBER,
 checkout VARCHAR2(1),
 total_books NUMBER,
 active_books NUMBER,
 recurring_update NUMBER,
 update_status  VARCHAR2(100),
 transaction_id NUMBER
);

create sequence so_students_s;

INSERT INTO so_students values (101,TO_DATE('14-Apr-20','DD-Mon-YY'),   2,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('17-Apr-20','DD-Mon-YY'),   2,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('17-Apr-20','DD-Mon-YY'),   4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('24-Apr-20','DD-Mon-YY'),   1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('24-Apr-20','DD-Mon-YY'),   3,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('24-Apr-20','DD-Mon-YY'),   4,'Y', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('27-Apr-20','DD-Mon-YY'),   1,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('27-Apr-20','DD-Mon-YY'),   3,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('27-Apr-20','DD-Mon-YY'),   4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('1-May-20','DD-Mon-YY'),    1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('1-May-20','DD-Mon-YY'),    2,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('1-May-20','DD-Mon-YY'),    4,'Y', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('3-May-20','DD-Mon-YY'),    3,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('3-May-20','DD-Mon-YY'),    4,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('4-May-20','DD-Mon-YY'),    2,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('4-May-20','DD-Mon-YY'),    4,'Y', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('8-May-20','DD-Mon-YY'),    1,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('10-May-20','DD-Mon-YY'),   2,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('10-May-20','DD-Mon-YY'),   3,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('17-May-20','DD-Mon-YY'),   1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('18-May-20','DD-Mon-YY'),   1,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('18-May-20','DD-Mon-YY'),   2,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('18-May-20','DD-Mon-YY'),   4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('19-May-20','DD-Mon-YY'),   3,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('19-May-20','DD-Mon-YY'),   4,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('22-May-20','DD-Mon-YY'),   3,'Y', 4, 0,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('22-May-20','DD-Mon-YY'),   4,'N', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('27-May-20','DD-Mon-YY'),   3,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('27-May-20','DD-Mon-YY'),   4,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('28-May-20','DD-Mon-YY'),   2,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('28-May-20','DD-Mon-YY'),   3,'Y', 4, 1,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('7-Jun-20','DD-Mon-YY'),    1,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('7-Jun-20','DD-Mon-YY'),    2,'N', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('9-Jun-20','DD-Mon-YY'),    4,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
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);
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);
INSERT INTO so_students values (101,TO_DATE('15-Jun-20','DD-Mon-YY'),   1,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('15-Jun-20','DD-Mon-YY'),   4,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
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);
INSERT INTO so_students values (101,TO_DATE('28-Jun-20','DD-Mon-YY'),   4,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('29-Jun-20','DD-Mon-YY'),   2,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('29-Jun-20','DD-Mon-YY'),   3,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('2-Jul-20','DD-Mon-YY'),    1,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
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);
INSERT INTO so_students values (101,TO_DATE('6-Jul-20','DD-Mon-YY'),    1,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('6-Jul-20','DD-Mon-YY'),    2,'N', 4, 4,NULL,NULL,so_students_s.NEXTVAL);
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);
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);
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);
INSERT INTO so_students values (101,TO_DATE('28-Jul-20','DD-Mon-YY'),   1,'Y', 4, 3,NULL,NULL,so_students_s.NEXTVAL);
INSERT INTO so_students values (101,TO_DATE('28-Jul-20','DD-Mon-YY'),   2,'N', 4, 2,NULL,NULL,so_students_s.NEXTVAL);

SELECT SUM(
  CASE update_status
    WHEN 'INVALID UPDATE' THEN
      0
    ELSE
      CASE checkout
        WHEN 'N' THEN
          1
        ELSE
          - 1
      END
  END
) OVER(
 ORDER BY dt,transaction_id) AS running_sum,
       active_books,
       s.*
  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