sqlite - 使用 SQLite 进行透视查询

Question

我有一个数据库，其中有两个需要与之交互的表。第一个表是成员表 (login)，其中包含 ID (id)、名字 (given_name) 和姓氏 ( family_name)。第二个表保存人员的登录名或成员(member) ID (mid)、他们的上类时间 (dateTimeIn) 和下类时间 (dateTimeOut) 日期时间。 ISO 8601 字符串，以及它们可以执行的事件类型 (type)。 dateTimeIn 和 dateTimeOut 都是 ISO 8601 日期时间字段，type、id、mid、given_name 和 family_name 都是字符串。绑定(bind)这两个表的远程外键是 hours.mid，它将目录映射到 login.id 列。

登录表:

创建表登录(id TEXT PRIMARY KEY NOT NULL，given_name TEXT NOT NULL，family_name TEXT NOT NULL，lastseen INTEGER NOT NULL)

id                   |given_name|family_name
-----------------------------------------------
103673125444466670779|Mark      |Tomlin

小时表:

创建表小时(中文本，类型文本，dateTimeIn INTEGER，dateTimeOut INTEGER)

mid                  |type    |dateTimeIn      |dateTimeOut
-----------------------------------------------------------------
103673125444466670779|Meeting |2013-06-04T20:00|2013-06-04T21:00
103673125444466670779|Meeting |2013-06-10T20:00|2013-06-10T21:00
103673125444466670779|Crew    |2013-06-21T22:00|2013-06-22T13:00
103673125444466670779|Crew    |2013-06-22T23:00|2013-06-23T07:00
103673125444466670779|Training|2013-06-23T20:00|2013-06-23T21:00
103673125444466670779|Crew    |2013-06-29T23:00|2013-06-30T07:00
103673125444466670779|Detail  |2013-06-23T17:00|2013-06-23T19:00
103673125444466670779|Meeting |2013-06-25T14:00|2013-06-25T18:00
103673125444466670779|Detail  |2013-06-28T01:00|2013-06-28T08:00
103673125444466670779|Crew    |2013-06-28T19:00|2013-06-28T23:00

我正在寻找的是一个查询，它将为我提供下表，其中包含我给出的任何日期范围。例如，对于 2013 年 6 月，它应该生成类似这样的内容。如果我有更多人登录，那么它也会将他们添加到下面，按姓氏、名字排序。

given_name|family_name|Crew|Meeting|Detail|Training
Mark      |Tomlin     |35  |6      |9     |1        

另一个很棒的查询是，我是否可以获得年度 View ，添加每个成员后，它们就会成为一个新行。

given_name|family_name|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec
Mark      |Tomlin     |35 |62 |27 |12 |88 |35 | - | - | - | - | - | -  

Answer 1

我确实需要一个查询，它可以为我提供当月内每个人的所有当前时间的列表。我通过以下查询做到了这一点。

SELECT
    *
FROM
    hours
WHERE
    dateTimeIn BETWEEN date('now', 'start of month') AND date('now', 'start of month','+1 month','-1 day');

我首先发现了一个查询，它向我展示了如何给出枢轴的总体外观。从那里开始，我不断地研究它，直到找到一个适合我想做的事情的选项。

此查询为我提供了数据库中每个成员的总秒数。

SELECT
    m.id,
    m.given_name,
    m.family_name,
    SUM(CASE WHEN h.type = 'Crew' THEN strftime('%s', h.dateTimeOut) - strftime('%s', h.dateTimeIn) END) Crew,
    SUM(CASE WHEN h.type = 'Detail' THEN strftime('%s', h.dateTimeOut) - strftime('%s', h.dateTimeIn) END) Detail,
    SUM(CASE WHEN h.type = 'Meeting' THEN strftime('%s', h.dateTimeOut) - strftime('%s', h.dateTimeIn) END) Meeting,
    SUM(CASE WHEN h.type = 'Training' THEN strftime('%s', h.dateTimeOut) - strftime('%s', h.dateTimeIn) END) Training
FROM login m JOIN hours h ON m.id = h.mid
GROUP BY m.id;

然后我可以对此进行扩充，只提供当月的运行总计。

SELECT
    m.id,
    m.given_name,
    m.family_name,
    SUM(CASE WHEN h.type = 'Crew' THEN strftime('%s', h.dateTimeOut) - strftime('%s', h.dateTimeIn) END) Crew,
    SUM(CASE WHEN h.type = 'Detail' THEN strftime('%s', h.dateTimeOut) - strftime('%s', h.dateTimeIn) END) Detail,
    SUM(CASE WHEN h.type = 'Meeting' THEN strftime('%s', h.dateTimeOut) - strftime('%s', h.dateTimeIn) END) Meeting,
    SUM(CASE WHEN h.type = 'Training' THEN strftime('%s', h.dateTimeOut) - strftime('%s', h.dateTimeIn) END) Training
FROM login m JOIN hours h ON m.id = h.mid
WHERE
    dateTimeIn BETWEEN date('now', 'start of month') AND date('now', 'start of month', '+1 month', '-1 day')
GROUP BY m.id;

现在我只需要执行最后一个即可显示今年的运行总计。