除了加入之外，
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。外部选择继续使用与以前相同的逻辑。
