sql - 有没有办法显示每周丢失多少本书？

问题描述

我有一个图书馆管理系统的数据库。我必须显示每周有多少书被借、归还和丢失（有任何信息的周）。如果图书在 7 天内未归还，则视为逾期。如果这本书没有返回预期的一周，则认为该周丢失。结果应该是这样的

Week | Borrowed | Returned | Missing
-----+----------+----------+---------
4    | 10       | 8        | 2
5    | 5        | 4        | 3

我已经弄清楚了借用和归还的部分

select date_part('week', dateofborrowing) as week,
date_part('year', dateofborrowing) as year, 
count(dateofborrowing) as borrowed, 
null as returned,
null as missing
from borrowing 
group by year,week 
union 
select date_part('week', returndate) as week,
date_part('year', returndate) as year, 
null as borrowed,
count(returndate) as returned,
null as missing
from borrowing 
group by year, week
order by year, week;

返回看起来像这样

 week | year | borrowed | returned | missing
------+------+----------+----------+---------
   45 | 2018 |        1 |          |
   46 | 2018 |          |        1 |
   51 | 2018 |        1 |          |
   52 | 2018 |          |        1 |
    5 | 2019 |        3 |          |
    6 | 2019 |        4 |          |
    7 | 2019 |        5 |          |
    7 | 2019 |          |        9 |
    8 | 2019 |          |        3 |
   14 | 2019 |        1 |          |
   17 | 2019 |          |        1 |
   36 | 2019 |        1 |          |
   37 | 2019 |        7 |          |
   38 | 2019 |        2 |          |
      |      |          |        0 |

如果有快速修复，最好第 7 周应该在同一行

但是，我正在努力解决的是缺少的列。感谢您的帮助！

这是表借用的创建

CREATE TABLE Borrowing (
BorrowingID int PRIMARY KEY,
ResourceID int REFERENCES Books(ResourceID),
UserID int REFERENCES Usser(UserID),
DateOfBorrowing date,
ReturnDate date,
Delay int );    // total amount of days a book is delayed

一些数据

INSERT INTO borrowing (borrowingid, resourceid, userid, dateofborrowing, returndate, delay)
VALUES (1, 117, 28, '2018-11-11', '2018-11-15', 0),
(2, 118, 30, '2018-12-22', '2018-12-29', 0),
(3, 119, 37, '2019-02-02', '2019-02-12', 3),
(4, 120, 37, '2019-02-02', '2019-02-14', 5),
(5, 121, 11, '2019-04-03', '2019-04-25', 15),
(6, 122, 32, '2019-02-03', '2019-02-11', 1),
(7, 123, 34, '2019-02-05', '2019-02-19', 7),
(8, 124, 39, '2019-02-08', '2019-02-14', 0),
(9, 125, 39, '2019-02-09', '2019-02-14', 0),
(10, 126, 39, '2019-02-10', '2019-02-14', 0),
(11, 127, 15, '2019-02-12', '2019-02-15', 0),
(12, 128, 16, '2019-02-12', '2019-02-16', 0),
(13, 129, 17, '2019-02-13', '2019-02-16', 0),
(14, 130, 18, '2019-02-14', '2019-02-20', 0),
(15, 131, 32, '2019-02-15', '2019-02-20', 0);

标签： sqlpostgresql

sql - 有没有办法显示每周丢失多少本书？

问题描述

解决方案

推荐阅读