使用sql根据小时数求和、计数和显示小时数

gab6jxml 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(494)

我有两张table，分别是教师桌和活动桌。

CREATE TABLE teacher (
TeacherId INT, BranchId VARCHAR(5));
INSERT INTO teacher VALUES
("1121","A"),
("1132","A"),
("1141","A"),
("2120","B"), 
("2122","B");

CREATE TABLE activities (
ID INT, TeacherID INT,    Hours   INT);

INSERT INTO activities VALUES
(1,1121,2),
(2,1121,1),
(3,1132,1),
(4,1141,NULL),
(5,2120,NULL),
(6,2122,NULL);

null表示没有活动，将在输出表上转换为0。我想生成一个查询来计算总课时数，并计算基于教师课时数的活动数，如下表所示：

+-----------+------------+------------+
   |   Hours   |     A      |     B      |
   +-----------+------------+------------+
   |     0     |     1      |     2      |
   |     1     |     1      |     0      |
   |     2     |     0      |     0      |
   |     3     |     1      |     0      |
   +-----------+------------+------------+

编辑：对不起，我不知道如何准确地阐述，但这是我从其他成员收到的小提琴https://www.db-fiddle.com/f/mmtuzqukyuqdhpvtfn9qaf/1
编辑：最后，需要修改，以分支id和教师id为输出，对学时和学时计数。
此处的预期输出（红色文本）：https://drive.google.com/file/d/1wyz_ax5hz_7i1ncf5sxlpstyk6ft8pmg/view?usp=sharing

sql mysql

来源：https://stackoverflow.com/questions/62528821/sum-hours-value-count-and-display-based-on-hours-using-sql

2条答案

按热度按时间

wn9m85ua1#

我们可以通过使用连接到聚合子查询的小时日历表来处理此问题：

SELECT
    t1.Hours,
    SUM(CASE WHEN t2.BranchId = 'A' THEN t2.cnt ELSE 0 END) AS A,
    SUM(CASE WHEN t2.BranchId = 'B' THEN t2.cnt ELSE 0 END) AS B
FROM (SELECT 0 AS Hours UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) t1
LEFT JOIN
(
    SELECT t.BranchId, COALESCE(a.Hours, 0) AS Hours, COUNT(*) AS cnt
    FROM Teacher t
    LEFT JOIN Activities a ON a.TeacherId = t.TeacherId
    GROUP BY t.BranchId, COALESCE(a.Hours, 0)
) t2
    ON t1.Hours = t2.Hours
GROUP BY
    t1.Hours
ORDER BY
    t1.Hours

演示

赞(0）回复(0）举报 2021-07-24

eyh26e7m2#

这基本上是一个 JOIN 和聚合。但你需要从你想要的所有时间开始：

SELECT h.Hours,
       COALESCE(SUM(t.BranchId = 'A'), 0) AS A,
       COALESCE(SUM(t.BranchId = 'B'), 0) AS B
FROM (SELECT 0 AS Hours UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
     ) h LEFT JOIN
     activities a
     ON h.hours = COALESCE(a.hours, 0) LEFT JOIN
     teacher t
     ON t.TeacherId = a.TeacherId
GROUP BY h.Hours
ORDER BY h.Hours;

这是一把小提琴。

赞(0）回复(0）举报 2021-07-24

我来回答

使用sql根据小时数求和、计数和显示小时数

2条答案

演示

相关问题

热门标签

最新问答