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

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

除了加入之外，

tbl_workers

您还可以加入其不变的变体，其中

position

和

position2

将在同一列中但在不同行中。

这是不变的样子：

SELECt  w.id,  w.name,  CASE x.pos WHEN 1 THEN w.position ELSE w.position2 END AS position,  w.statusFROM 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 placedFROM 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.idGROUP 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 placedFROM 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.idGROUP BY p.id, p.position

这个想法是

4

用

3

或

2

取决于子选择中的状态，这取决于我们当前是要拉

position

还是

position2

作为统一

position

。外部选择继续使用与以前相同的逻辑。