Mysql 随机显示 4 个用户超过 5 篇文章 [英] Mysql display random 4 users with more than 5 articles

问题描述

我有用户表和文章表.文章表包含用户提交的文章.我正在处理一个 sql 查询，以显示超过 5 篇文章的随机 4 个用户.user_id 存储在文章表中.我已经在 stackoverflow 和 google 中搜索了一些类似的问题，但我找不到任何特定于我的问题.

I have users table and also articles tables. Article table contains articles submitted by users. I am working on a sql query to display random 4 users with more than 5 articles. user_id is stored in articles table. I have searched around in stackoverflow and google even though there are some similar questions, i couldn't find anything specific to mine.

谁能告诉我这个问题以前是否有人回答过，如果回答过，请给我一个链接，否则我有以下查询:

Can anyone let me know if this question has been answered before and give me a link if yes otherwise I have the following query:

SELECT * 
  FROM users WHERE type = 3 
  INNER JOIN articles ON 
    users.user_id = articles.user_id HAVING COUNT(user_id) > 5

这似乎不起作用.对于改进此查询的任何帮助，我将不胜感激.

This doesn't seem to work. I will appreciate any help to improve this query.

数据库表如下:

USERS:
  user_id
  username
  email
  type

ARTICLES:
 id
 user_id
 title

例如，总用户数为100.user_id 49的用户有10篇文章，另一个user_id 50的用户有20篇文章，其余用户的文章少于5篇.所以查询应该只返回用户 49 和 50.

For example, total user count is 100. User with user_id 49 has 10 articles, and another user with user_id 50 has 20 articles and the rest of the users have less than 5 articles. So the query should return only the user 49 and 50.

希望这是有道理的.

问候

推荐答案

我模拟了一些表数据来测试我的查询.WHERE 子句必须位于 JOIN 之后.你对 COUNT AND 5 的比较也有点含糊不清——如果你想要超过 5 则 >5，如果你想要5 个或更多然后 >=5.

I've mocked up some table data to test my query. WHERE clauses must be positioned after JOINs. You are also a little ambiguous about the comparison of COUNT AND 5 -- if you want more than 5 then >5, if you want 5 or more then >=5.

SQL:(SQLFiddle 演示)

SELECT a.user_id,a.username,COUNT(b.user_id)
FROM users a
INNER JOIN articles b ON a.user_id=b.user_id
WHERE a.type=3
GROUP BY a.user_id 
HAVING COUNT(b.user_id)>5
ORDER BY RAND()
LIMIT 4

这篇关于Mysql 随机显示 4 个用户超过 5 篇文章的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！