php - MySQL:当一个表上存在多个连接时，计算 NULL 或 0 值

Question

我有一个查询，它根据名为 users 的表中的记录 ID 查找人们的全名。全名与其在另一个表 (table1) 中的角色相关联。这需要多次连接到 users 表:

SELECT table1.id, users.full_name AS "Requester", 
users.full_name AS "Approver," 
users.full_name AS "Ordered By", 
users.full_name AS "Received By" 
FROM table1
JOIN users AS users
ON table1.requester_id = users.id
JOIN users AS users2
ON table1.approver_id = users2.id
JOIN users AS users3 
ON table1.ordered_by = users3.id
JOIN users AS users4
ON table1.received_by = users4.id
WHERE table1.deleted_record !=1;

我遇到的问题是 ordered_by 和 received_by。通常，它们还不存在，因为订单既没有被订购也没有被接收，所以它们的 ID 可以为 0，这在 users 表中没有对应的值。当我运行这个查询时，我应该取回所有存在的 475 条记录，但由于那些 0 值，我只取回 365 条记录。如何修改此查询以确保返回所有行，即使 ordered_by 和/或 received_by = 0？

Answer 1

首先，驱动查询的主表应该是 table1。然后，您将使用 JOIN 而不是 LEFT JOIN。如果没有链接，LEFT JOIN 会给你一个空结果，但不会失败。在这种情况下，您可能必须为字段值使用 IF

SELECT table1.id, req.full_name AS "Requester", 
   app.full_name AS "Approver", 
   ordr.full_name AS "Ordered By", 
   rec.full_name AS "Received By" 
FROM table1
LEFT JOIN users AS req
   ON table1.requester_id = req.id
LEFT JOIN users AS app
   ON table1.approver_id = app.id
LEFT JOIN users AS ordr
   ON table1.ordered_by = ordr.id
LEFT JOIN users AS rec
   ON table1.received_by = rec.id
WHERE table1.deleted_record !=1;

应该这样做