使用SUM对来自多个表的数据进行计数

admin

使用SUM对来自多个表的数据进行计数

sql

我有带位置的表

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

阅读 143

2021-06-07

共1个答案

admin

除了加入之外，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

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

2021-06-07