mysql - 将 COUNT(和限定符)添加到 SQL 语句

Question

我的数据库有 3 行(未列出所有列):

**uFirst** | **uApp** 
  Dave     | approved
  John     | declined
  Jane     | no

此 SQL 正在运行，并显示所有行:

SELECT u.uID, u.uManT, u.uFirst AS 'NomFN', m.uFirst AS 'ManFN', u.uLast AS 'NomLN', m.uLast AS 'ManLN', u.uNomd, u.uApp, u.uApp, u.uAppd, u.uRep, u.uRepd, u.uManT, u.uStatus, u.uManR 
          FROM users AS u LEFT OUTER JOIN users AS m
          ON u.uManT = m.uID 
          WHERE u.uManR = " . $luID . " 
          AND u.uStatus = 'Physician' 
          ORDER BY u.uLast ASC";

当我尝试添加 COUNT 时，它只显示 1 行(已批准的行):

SELECT COUNT(CASE WHEN u.uApp = 'approved' THEN 1 END) AS Approved, 
       COUNT(CASE WHEN u.uApp = 'declined' THEN 1 END) AS Declined, 
       u.uID, u.uManT, u.uFirst AS 'NomFN', m.uFirst AS 'ManFN', u.uLast AS 'NomLN', m.uLast AS 'ManLN', u.uNomd, u.uApp, u.uApp, u.uAppd, u.uRep, u.uRepd, u.uManT, u.uStatus, u.uManR 
          FROM users AS u LEFT OUTER JOIN users AS m
          ON u.uManT = m.uID 
          WHERE u.uManR = " . $luID . " 
          AND u.uStatus = 'Physician' 
          ORDER BY u.uLast ASC";

我正在尝试显示所有行，以及已批准的用户数。我做错了什么？

Answer 1

在这种情况下只返回 1 行，因为您没有使用 GROUP BY ，这个查询应该能得到你想要的:

SELECT COUNT(uFirst) AS Total, u.uID, u.uManT, 
     u.uFirst AS 'NomFN', m.uFirst AS 'ManFN', u.uLast AS 'NomLN',
     m.uLast AS 'ManLN', u.uNomd, u.uApp, u.uApp, u.uAppd, u.uRep,
     u.uRepd, u.uManT, u.uStatus, u.uManR 
FROM users AS u 
LEFT OUTER JOIN users AS m ON u.uManT = m.uID 
WHERE u.uManR = " . $luID . " AND u.uStatus = 'Physician' 
GROUP BY uApp
ORDER BY u.uLast ASC";