看下面的sql,头晕不。居然有如此多的exists,而且是关联的同一个表。
SELECT COUNT(1) num
FROM (SELECT t1.*
FROM t t1
WHERE 1 = 1
AND t1.type = 0
AND (t1.status IN (1, 10, 11, 12, 100) OR
(EXISTS (SELECT b.id
FROM tf b
WHERE t1.id = b.a_code
AND t1.status IN (3, 4, 8)
AND b.status IN (1, 10, 11, 12))))
AND (EXISTS (SELECT 1
FROM tf a1
WHERE t1.id = a1.a_code
AND (a1.c_status = 1 OR a1.c_status = 4)) OR
NOT EXISTS (SELECT 1
FROM tf a1
WHERE t1.id = a1.a_code))
ORDER BY t1.c_time DESC, t1.id) a;
经询问 a_code不是唯一列,于是更改如下
SELECT COUNT(1) num
FROM (SELECT t1.*
FROM t t1
LEFT JOIN (SELECT a_code,
MAX(CASE
WHEN status IN (1, 10, 11, 12) THEN
1
END) AS status,
MAX(CASE
WHEN (c_status = 1 OR c_status = 4) THEN
1
END) AS c_status
FROM tf
GROUP BY a_code) a1
ON (a1.a_code = t1.id)
WHERE 1 = 1
AND t1.type = 0
AND (t1.status IN (1, 10, 11, 12, 100) OR
(t1.status IN (3, 4, 8) AND a1.status = 1))
AND (a1.c_status = 1 OR a1.a_code IS NULL)
ORDER BY t1.c_time DESC, t1.id) a;
这样是不是清爽了许多