I have a table in MySQL as below.
CREATE TABLE `myTable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`employeeNumber` int(11) DEFAULT NULL,
`approveDate` date DEFAULT NULL,
`documentNumber` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `INDEX_1` (`documentNumber`)
) ENGINE=InnoDB;
documentNumber
employeeNumber
employeeNumber
employeeNumber
SELECT T1.documentNumber,
(CASE WHEN T2.currentNum = '0' THEN '1' WHEN T2.currentNum < T2.totalNum THEN '2' ELSE '3' END) AS approveStatusNumber
FROM myTable AS T1 LEFT JOIN
(SELECT documentNumber, COUNT(*) AS totalNum, SUM(CASE WHEN approveDate IS NOT NULL THEN '1' ELSE '0' END) AS currentNum
FROM myTable GROUP by documentNumber) AS T2 ON T1.documentNumber = T2.documentNumber
GROUP BY T1.documentNumber;
explain
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| 1 | PRIMARY | T1 | range | INDEX_1 | INDEX_1 | 153 | NULL | 27 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5517 | |
| 2 | DERIVED | myTable | index | NULL | INDEX_1 | 153 | NULL | 5948 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
The expression to return currentNum
could be more succinctly expressed (in MySQL) as
SUM(approveDate IS NOT NULL)
And there's no need for an inline view. This will return an equivalent result:
SELECT t.documentNumber
, CASE
WHEN SUM(t.approveDate IS NOT NULL) = 0
THEN '1'
WHEN SUM(t.approveDate IS NOT NULL) < COUNT(*)
THEN '2'
ELSE '3'
END AS approveStatusNumber
FROM myTable t
GROUP BY t.documentNumber
ORDER BY t.documentNumber