MySQL COUNT UNION ALL语句的结果 [英] MySQL COUNT results of UNION ALL statement

问题描述

我确定必须有一种方法可以执行此操作，但是我的MySQL知识使我退缩.

I'm sure there must be a way to do this but my MySQL knowledge is holding me back.

我有一个用于存储页面标签的表

I have a single table that stores page tags

page_id          tag
51               New Zealand
51               Trekking
58               UK
77               New Zealand
77               Trekking
89               City Break
101              Shopping
...

我想搜索具有两个标签的页面，例如新西兰"和徒步旅行".我看过UNIONS，INTERSECT(对等)，JOINS，但我想不出什么是最好的方法.我想出的最好办法是:

I want to do a search for pages that have two tags, e.g. "New Zealand" and "Trekking". I've looked at UNIONS, INTERSECT (equiv), JOINS and I can't work out what is the best way to do it. The best I have come up with is to do:

SELECT page_id FROM tags
WHERE tag = "New Zealand"
UNION ALL
SELECT page_id FROM tags
WHERE tag = "Trekking"
... and then some kind of COUNT on those pages that feature twice??

我本质上是想将两个搜索联合在一起，并保留"重复项并丢弃其余重复项.是否可以通过简单的方式实现?还是我需要开始变得复杂起来?

I essentially want to UNION the two searches together and 'keep' the duplicates and discard the rest. Is this possible in a simple way or do I need to start getting complex with it?

推荐答案

如果我对您的理解正确，则应该这样做:

If I understood you correctly, this should do it:

SELECT page_id, count(*)
FROM tags
WHERE tag IN ('New Zealand', 'Trekking')
GROUP BY page_id
HAVING count(*) > 1

如果您从同一张表中进行选择，则无需使用UNION.

You don't need to use a UNION if you select from the same table.

这篇关于MySQL COUNT UNION ALL语句的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！