mysql - 使用 SUM 计算多个表中的数据

Question

我有位置表

tbl_positions
   id position
    1 Driver
    2 Lobby
    3 Support
    4 Constructor 

在其他表中我有用户

编辑:

   tbl_workers
      id   name   position position2 status
       1   John   2        3         4
       2   Mike   3        2         2
       3   Kate   2        0         3
       4   Andy   1        0         0

我确实要求职位

SELECT p.id, 
    p.position, 
    SUM(CASE w.Status WHEN 2 THEN 1 ELSE 0 END)  AS booked,
    SUM(CASE w.Status WHEN 3 THEN 1 ELSE 0 END)  AS placed
  FROM  tbl_positions AS p LEFT JOIN tbl_workers AS w 
      ON w.position=p.id
GROUP BY p.id, p.position

我在单个查询中需要这样的输出。

Position    booked placed
Driver        0       0
Lobby         1       2
Support       0       2
Constructor   0       0

我需要评估字段 positon1 和 position2 而不是一个。我认为修改它很容易，但我找不到合适的解决方案，请帮忙。

编辑:添加状态 4

Answer 1

除了加入 tbl_workers，您还可以加入它的非透视变体，其中 position 和 position2 将在同一列但在不同的行中.

这是逆透视的样子:

SELECT
  w.id,
  w.name,
  CASE x.pos WHEN 1 THEN w.position ELSE w.position2 END AS position,
  w.status
FROM tbl_workers AS w
  CROSS JOIN (SELECT 1 AS pos UNION ALL SELECT 2) AS x

这是整个查询，基本上是用上面的查询替换 tbl_workers 表的原始查询:

SELECT p.id, 
  p.position, 
  SUM(CASE w.Status WHEN 2 THEN 1 ELSE 0 END)  AS booked,
  SUM(CASE w.Status WHEN 3 THEN 1 ELSE 0 END)  AS placed
FROM tbl_positions AS p
  LEFT JOIN (
    SELECT
      w.id,
      w.name,
      CASE x.pos WHEN 1 THEN w.position ELSE w.position2 END AS position,
      w.status
    FROM tbl_workers AS w
      CROSS JOIN (SELECT 1 AS pos UNION ALL SELECT 2) AS x
  ) AS w 
  ON w.position=p.id
GROUP BY p.id, p.position

---

更新

这是根据评论中的额外要求修改的脚本:

SELECT p.id, 
  p.position, 
  SUM(CASE w.Status WHEN 2 THEN 1 ELSE 0 END)  AS booked,
  SUM(CASE w.Status WHEN 3 THEN 1 ELSE 0 END)  AS placed
FROM tbl_positions AS p
  LEFT JOIN (
    SELECT
      w.id,
      w.name,
      CASE x.pos WHEN 1 THEN w.position ELSE w.position2 END AS position,
      CASE w.status
        WHEN 4 THEN CASE x.pos WHEN 1 THEN 3 ELSE 2 END
        ELSE w.status
      END AS status
    FROM tbl_workers AS w
      CROSS JOIN (SELECT 1 AS pos UNION ALL SELECT 2) AS x
  ) AS w 
  ON w.position=p.id
GROUP BY p.id, p.position

想法是用 3 或 2 替换子选择中的 4 状态，具体取决于我们当前是否要拉取 position 或 position2 作为统一的 position。外部选择继续使用与以前相同的逻辑。