mysql - 计算 MYSQL 中每个用户的子表中的不同记录
问题描述
我有一个比赛,计算每个用户收集了多少物种。这由 3 个表管理:
- 一个名为“sub”的父表带有集合,每个集合都是唯一的,有一个 id 并与一个用户 id 相关联。
+----+---------+ | id | user_id | +----+---------+ | 1 | 1 | | 2 | 10 | | 3 | 1 | | 4 | 3 | | 5 | 1 | | 6 | 10 | +----+---------+
- 名为“sub_items”的子表包含规范的多个唯一记录,并通过子ID到ID与父表相关。(每个子可以有多个规范记录)
+----+--------+---------+--+
| id | sub_id | spec_id | |
+----+--------+---------+--+
| 1 | 1 | 1000 | |
| 2 | 1 | 1003 | |
| 3 | 1 | 2520 | |
| 4 | 2 | 7600 | |
| 5 | 2 | 1000 | |
| 6 | 3 | 15 | |
+----+--------+---------+--+
- 具有关联 user_id 的用户表
+--------+-------+--+
| usename | name |
+---------+-------+--+
| 1 | David |
| 10 | Ruth |
| 3 | Rick |
+--------+-------+--+
我需要按降序列出具有最独特规格的用户。预期输出:David 共有 2 个独特的规格。Ruth 共有 2 个独特的规格。
+--------+---------+
| id | total |
+----+-------------+
| David | 2 |
| Ruth | 2 |
| Rick | 2 |
+----+-------------+
到目前为止,我有这个,它产生了一个结果。但它不准确,它计算总记录。我可能在子查询中的某处遗漏了一个 DISTINCT。
SELECT s.id, s.user_id,u.name, sum(t.count) as total
FROM sub s
LEFT JOIN (
SELECT id, sub_id, count(id) as count FROM sub_items GROUP BY sub_id
) t ON t.sub_id = s.id
LEFT JOIN user u ON u.username = s.user_id
GROUP BY user_id
ORDER BY total DESC
我看过这个解决方案,但它没有考虑独特的方面
解决方案
您首先必须获得所有用户的最大“分数”,例如:
SELECT count(DISTINCT si.id) as total
FROM sub INNER JOIN sub_items si ON sub.id = su.sub_id
GROUP BY sub.user_id
ORDER BY total DESC
LIMIT 1
然后,您可以使用它将查询限制为共享该最高分数的用户:
SELECT u.name, count(DISTINCT si.id) as total
FROM
user u
INNER JOIN sub ON u.usename = sub.user_id
INNER JOIN sub_items si ON sub.id = su.sub_id
GROUP BY u.name
HAVING total =
(
SELECT count(DISTINCT si.id) as total
FROM sub INNER JOIN sub_items si ON sub.id = su.sub_id
GROUP BY sub.user_id
ORDER BY total DESC
LIMIT 1
)
推荐阅读
- javascript - NodeJs 对象合并基于键值 Id
- django - 在 DetailView 上使用表单,将数据提交到 FormView - 包括来自 DetailView 上下文的数据
- c# - c#中的Windows按钮圆角
- maven - JUnit5 通过标签运行测试
- django - Django:捕获完整性错误与 qs.exists() 哪个更有效?
- php - 当 csrf 保护为真时,从 codeigniter 3 中的 ajax 发送数据显示 403 禁止
- java - 在java中输入退出选择之前,我如何继续在switch case中进行选择
- java - 我想从下拉列表中添加国家代码
- html - 将文本颜色应用于 HTML 列
- android-layout - 无法在其他文本视图的底部设置文本视图