时间戳

首页 > 解决方案 > 多个语句的 SQL 多次计数赢得不同的列

mysql - 多个语句的 SQL 多次计数赢得不同的列

问题描述

我正在寻找一种使用一个查询从表中获取多个计数的方法。我想获取使用特定终端以及授予状态的每个 USER_level（只有 3 个）的总数

桌子

CARD    TERMINAL        TRANSIT_DATE        STATUS  STR_DIRECTION   USER_Level
12      D1      2019-12-02 07:12:30.000     Granted Exit            level1
10      D1      2019-12-02 13:22:29.000     Granted Exit            level3
11      D2      2019-12-02 17:09:05.000     Granted Exit            level1
17      D2      2019-12-03 09:58:22.000     deny    Entry           level2
19      D3      2019-12-03 12:17:29.000     Granted Entry           level2
32      D3      2019-12-03 13:48:33.000     Granted Exit            level1
20      D2      2019-12-04 10:36:22.000     Granted Exit            level3
11      D3      2019-12-07 22:01:41.000     Granted Exit            level1
15      D2      2019-12-09 11:12:16.000     Granted Exit            level2
14      D3      2019-12-10 06:32:22.000     Granted Entry           level2
12      D2      2019-12-10 10:31:26.000     Granted Exit            level1
39      D2      2019-12-11 06:42:08.000     Granted Exit            level3
12      D2      2019-12-11 09:32:13.000     Granted Exit            level1
11      D3      2019-12-11 12:44:36.000     Granted Exit            level2
56      D2      2019-12-12 06:48:33.000     Granted Exit            level2
78      D2      2019-12-12 12:03:23.000     Granted Entry           level1
66      D3      2019-12-10 12:16:54.000     Granted Exit            level3
22      D2      2019-12-09 16:02:02.000     Granted Exit            level1

示例看起来像这样的结果

TERMINAL    level 1  level 2 level 3
D1          1               1 
D2          6        2      2
D3          2        3      1
...

标签： mysqlsqlsql-server

解决方案

您可以尝试这样的事情，请注意，如果存在更多终端，则必须将它们添加到查询中。

select terminal, 
       sum(case when user_level = 'level1' then 1 else 0 end) as level1, 
       sum(case when user_level = 'level2' then 1 else 0 end) as level2,
       sum(case when user_level = 'level3' then 1 else 0 end) as level3
from yourTable
    where status = 'Granted' 
      and transit_date > your_initial_date
group by terminal

希望能帮助到你！

推荐阅读