mysql - 如何限制 SQL 查询特定部分的结果数？

Question

你能在下面的 SQL 调用中看到一种方法来限制这个特定部分的结果数量吗:

OR (u1 != '2' AND u2 != '2' AND u3 != '2' AND m.membership_id = 1 AND m.aff IN (SELECT id FROM members WHERE id != 2 AND membership_id = 1 OR jv > 0)))

我喜欢将匹配的数量限制为一个确切的数字，例如150

完整的 SQL 是:

SELECT DISTINCT mid, did FROM product_access, members AS m 
WHERE mid = m.id AND m.active = 1 
      AND m.suspended = 0 
      AND (u1 = '2' OR u2 = '2' OR u3 = '2' OR (u1 != '2' AND u2 != '2' AND u3 != '2' AND m.membership_id = 1 AND m.aff IN (SELECT id FROM members WHERE id != 2 AND membership_id = 1 OR jv > 0))) GROUP BY mid

因此，结果应该包括所有 u1 = '2'，所有 u2 = '2'，所有 u3 = '2'，但只有 150 个，其中:

(u1 != '2' AND u2 != '2' AND u3 != '2' AND m.membership_id = 1 AND m.aff IN (SELECT id FROM members WHERE id != 2 AND membership_id = 1 OR jv > 0))

Answer 1

您始终可以从查询的每个部分创建子查询，并将其限制为 n 个 TOPmost 条目。

据我了解，这样的事情就足够了:

SELECT mid, did FROM(
SELECT DISTINCT mid, did FROM product_access, members AS m 
WHERE mid = m.id AND m.active = 1 
      AND m.suspended = 0 
      AND (u1 = '2' OR u2 = '2' OR u3 = '2')

UNION SELECT TOP 150 DISTINCT mid, did FROM product_access, members AS m 
WHERE mid = m.id AND m.active = 1 
     AND m.suspended = 0 
     AND (u1 != '2' AND u2 != '2' AND u3 != '2' AND m.membership_id = 1 AND m.aff IN (SELECT id FROM members WHERE id != 2 AND membership_id = 1 OR jv > 0))

) GROUP BY mid