mysql - 简化MySQL查询以删除Where语句中的多选

Question

我有以下查询来计算工作许可证的数量，并想知道是否可以简化它以删除大量的选择来查找许可证状态。这是在网页上运行的，因此我需要将发送和接收的数据量保持在最低限度

SELECT SUM(IF(tbl_permit_status.status_id = (SELECT tbl_status.status_id FROM tbl_status WHERE tbl_status.status = 'Requested'), 1, 0)) AS requested,
   SUM(IF(tbl_permit_status.status_id = (SELECT tbl_status.status_id FROM tbl_status WHERE tbl_status.status = 'Approved'), 1, 0)) AS approved,
   SUM(IF(tbl_permit_status.status_id = (SELECT tbl_status.status_id FROM tbl_status WHERE tbl_status.status = 'Issued'), 1, 0)) AS issued,
   SUM(IF(tbl_permit_status.status_id = (SELECT tbl_status.status_id FROM tbl_status WHERE tbl_status.status = 'Signed On'), 1, 0)) AS signed_on,
   SUM(IF(tbl_permit_status.status_id = (SELECT tbl_status.status_id FROM tbl_status WHERE tbl_status.status = 'Suspended'), 1, 0)) AS suspended,
   SUM(IF(tbl_permit_status.status_id = (SELECT tbl_status.status_id FROM tbl_status WHERE tbl_status.status = 'Cleared'), 1, 0)) AS cleared,
   SUM(IF(tbl_permit_status.status_id = (SELECT tbl_status.status_id FROM tbl_status WHERE tbl_status.status = 'Cancelled'), 1, 0)) AS cancelled,
   COUNT(*) AS total
FROM tbl_permit_status
  INNER JOIN tbl_permit_number
ON tbl_permit_status.permit_id = tbl_permit_number.permit_id
  INNER JOIN tbl_permit_client
ON tbl_permit_status.permit_id = tbl_permit_client.permit_id 

Answer 1

只需加入状态表即可:

SELECT 
  SUM(s.status = 'Requested') AS requested,
  SUM(s.status = 'Approved') AS approved,
  SUM(s.status = 'Issued') AS issued,
  SUM(s.status = 'Signed On') AS signed_on,
  SUM(s.status = 'Suspended') AS suspended,
  SUM(s.status = 'Cleared') AS cleared,
  SUM(s.status = 'Cancelled') AS cancelled,
  COUNT(*) AS total
FROM tbl_permit_status ps
INNER JOIN tbl_permit_number pm ON ps.permit_id = pm.permit_id
INNER JOIN tbl_permit_client pc ON ps.permit_id = pc.permit_id 
INNER JOIN tbl_status s ON ps.status_id = s.status_id

正如您所看到的，您甚至可以简化 SUM 表达式(即不需要 IF)，因为在 MySQL 中 true 为 1，false 为 0。

(如果tbl_permit_status.status_id可以为空，则左连接状态表而不是内连接以获得正确的总计数。)