Exists如些多焦

来源：互联网发布：js验证码不区分大小写编辑：程序博客网时间：2024/05/17 08:53

看下面的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;

这样是不是清爽了许多