如何获得SQLIN子句中出现的次数？
如何获得SQLIN子句中出现的次数？
提问于 2018-08-02 06:45:15
比方说，我有四个表：PAGE，USER，TAG，和PAGE-TAG：
Table | Fields
------------------------------------------
PAGE | ID, CONTENT
TAG | ID, NAME
USER | ID, NAME
PAGE-TAG | ID, PAGE-ID, TAG-ID, USER-ID
让我们说我有四页：
PAGE#1 'Content page 1' tagged with tag#1 by user1, tagged with tag#1 by user2
PAGE#2 'Content page 2' tagged with tag#3 by user2, tagged by tag#1 by user2, tagged by tag#8 by user1
PAGE#3 'Content page 3' tagged with tag#7 by user#1
PAGE#4 'Content page 4' tagged with tag#1 by user1, tagged with tag#8 by user1
我希望我的查询看起来像这样：
select page.content ?
from page, page-tag
where
page.id = page-tag.pag-id
and page-tag.tag-id in (1, 3, 8)
order by ? desc
我想获得这样的输出：
Content page 2, 3
Content page 4, 2
Content page 1, 1
下面我展示，我期望的输出：
page 1 | in (1,2) -> 1
page 1 | in (1,2,3) -> 1
page 1 | in (1) -> 1
page 1 | in (1,3,8) -> 1
page 2 | in (1,2) -> 1
page 2 | in (1,2,3) -> 2
page 2 | in (1) -> 1
page 2 | in (1,3,8) -> 3
page 4 | in (1,2,3) -> 1
page 4 | in (1,2,3) -> 1
page 4 | in (1) -> 1
page 4 | in (1,3,8) -> 2
这将是我之前提到的页面标记表的内容：
id page-id tag-id user-id
1 1 1 1
2 1 1 2
3 2 3 2
4 2 1 2
5 2 8 1
6 3 7 1
7 4 1 1
8 4 8 1
还有什么想法吗？
回答 2
Stack Overflow用户
发布于 2018-08-02 15:37:12
我建议这样：
SELECT page.ID, page.content, count(*) AS uniquetags
FROM
( SELECT DISTINCT page.content, page.ID, page-tag.tag-id
FROM page INNER JOIN page-tag ON page.ID=page-tag.page-ID
WHERE page-tag.tag-id IN (1, 3, 8)
)
GROUP BY page.ID
这可能不会按标签数量的降序给出输出，但请尝试添加：
ORDER BY uniquetags DESC
Stack Overflow用户
发布于 2018-08-02 15:54:28
这可能有用：
select page.content, count(page-tag.tag-id) as tagcount
from page inner join page-tag on page-tag.page-id = page.id
group by page.content
having page-tag.tag-id in (1, 3, 8)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100000487
复制相关文章
相似问题