select count只显示1个结果和错误的结果

t3irkdon 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(469)

我要搜索表1并计算 number_id 拥有最多的5分 experience 列。
表1

+-------------+------------+
| number_id   | experience |
+-------------+------------+
|  20         |  5         |
|  20         |  5         |
|  19         |  1         |
|  18         |  2         |
|  15         |  3         |
|  13         |  1         |
|  10         |  5         |
+-------------+------------+

所以在这种情况下 number_id=20 然后在表2中做一个内部连接并Map number 符合 number_id 在表1中。
表2

+-------------+------------+
| id          | number     |
+-------------+------------+
|  20         |  000000000 |
|  29         |  012345678 |
|  19         |  123456789 |
|  18         |  223456789 |
|  15         |  345678910 |
|  13         |  123457898 |
|  10         |  545678910 |
+-------------+------------+

所以结果是：

000000000 (2 results of 5)
545678910 (1 result of 5)

到目前为止，我已经：

SELECT number, experience, number_id, COUNT(*) AS SUM FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.number_id = TABLE2.id
WHERE experience = '5' order by SUM LIMIT 10

但它回来了

545678910

如何让它返回结果，并按经验列中5的示例数的顺序返回？
谢谢

mysql

来源：https://stackoverflow.com/questions/52060938/select-count-only-showing-1-result-and-the-wrong-one

2条答案

按热度按时间

pgky5nke1#

添加group by子句：

SELECT number, experience, number_id, COUNT(*) AS SUM
FROM TABLE1
JOIN TABLE2 ON TABLE1.number_id = TABLE2.id
WHERE experience = '5'
GROUP BY 1, 2, 3 -- <<< Added this clause
ORDER BY SUM
LIMIT 10

赞(0）回复(0）举报 2021-06-21

rks48beu2#

此查询将为您提供所需的结果。子查询获取所有 number_id 那些有 experience 值为5。这个 SUM(experience=5) 因为mysql使用的值 1 为了 true 以及 0 为了 false . 然后将子查询的结果连接到表2，以给出 number 现场。最后，结果是按 experience=5 :

SELECT t2.number, t1.num_fives
FROM (SELECT number_id, SUM(experience = 5) AS num_fives
      FROM table1
      WHERE experience = 5
      GROUP BY number_id) t1
JOIN table2 t2
ON t2.id = t1.number_id
ORDER BY num_fives DESC

输出：

number      num_fives
000000000   2
545678910   1

sqlfiddle演示

赞(0）回复(0）举报 2021-06-21

我来回答

select count只显示1个结果和错误的结果

2条答案

相关问题

热门标签

最新问答