mysql计算不重叠的UNION结果

Question

好的，我有疑问

(SELECT * FROM jokes WHERE flags < 5 AND (title LIKE ? OR joke LIKE ?) ORDER BY ups DESC,downs ASC)
UNION
(SELECT * FROM jokes WHERE flags < 5 AND (title LIKE ? OR joke LIKE ?) ORDER BY ups DESC,downs ASC)
UNION
(SELECT * FROM jokes WHERE flags < 5 AND (title LIKE ? OR joke LIKE ?) ORDER BY ups DESC,downs ASC)
 LIMIT 0, 30

我将使用 COUNT(*) 无限制地运行相同的操作

(SELECT COUNT(*) FROM jokes WHERE flags < 5 AND (title LIKE ? OR joke LIKE ?) ORDER BY ups DESC,downs ASC)
UNION
(SELECT COUNT(*) FROM jokes WHERE flags < 5 AND (title LIKE ? OR joke LIKE ?) ORDER BY ups DESC,downs ASC)
UNION
(SELECT COUNT(*) FROM jokes WHERE flags < 5 AND (title LIKE ? OR joke LIKE ?) ORDER BY ups DESC,downs ASC)

本质上我想做的是获取总结果数(无限制)，并获取有限列表。

我遇到的问题是，当我运行计数查询时，我得到的数字比返回的结果更大。我猜测这样做的原因是当我得到结果时，它会过滤掉所有重叠的行，因为计数只计算每个行，因为我必须手动添加它们。

关于如何清理整个过程有什么想法吗？

另外，请注意 select 语句的数量会动态变化，但我希望有一种方法可以获得重复项的总体计数。

谢谢。

Answer 1

伯爵这个怎么样:

SELECT COUNT(DISTINCT Id)
FROM (SELECT Id From jokes WHERE flags < 5 AND (title LIKE ? OR joke LIKE ?) 
      UNION
      SELECT Id From jokes WHERE flags < 5 AND (title LIKE ? OR joke LIKE ?) 
      UNION
      SELECT Id From jokes WHERE flags < 5 AND (title LIKE ? OR joke LIKE ?)) t

这应该为您提供唯一的计数，并且还请注意，我删除了 order by，因为它不值得计算计数查询。