多个表上的 SQL Count(*) [英] SQL Count(*) on multiple tables
问题描述
我正在编写一个赛马网络应用程序,我有一个统计页面.我想要做的是展示每年有多少获奖者.
I am writing a horse racing web app and i have a stats page. What i am trying to do is show how many winners there are from each year.
目前我有这个:
SELECT `Horse Number`, Count(*) AS `Total Winners`
FROM `races`.`2009`
WHERE `Win $`>0
GROUP BY `Horse Number`
ORDER BY Count(*) DESC;
它就像一个魅力并返回以下内容
It works like a charm and returns the following
Horse Number | Total Winners
1|48
2|49
3|39
4|31
5|26
6|31
7|21
8|25
9|31
10|16
11|16
12|20
13|9
14|8
15|6
16|3
17|3
18|2
19|2
我现在已经为 2010 年创建了一个表,我希望 SQL 返回类似的内容,但我希望它同时搜索 2009 年和 2010 年.我认为这样的事情可能会奏效.
I have now created a table for 2010 and i am wanting SQL to return something similar but i want it to search over 2009 and 2010 at the same time. I thought something like this might do the trick.
SELECT `Horse Number`, Count(*) AS `Total Winners`
FROM `races`.`2009`
WHERE `Win $`>0
GROUP BY `Horse Number`
UNION
SELECT `Horse Number`, Count(*) AS `Total Winners`
FROM `races`.`2010`
WHERE `Win $`>0
GROUP BY `Horse Number`
ORDER BY Count(*) DESC;
但它只是在表格底部返回额外的结果,所以我现在每匹马都有 2 行这样
But it is just returning extra results at the bottom of the table so i now have 2 rows for each horse like this
Horse Number | Total Winners
1|48
2|49
3|39
4|31
5|26
6|31
7|21
8|25
9|31
10|16
11|16
12|20
13|9
14|8
15|6
16|3
17|3
18|2
19|2
1|0
2|0
3|0
4|0
5|0
6|0
7|0
8|0
9|0
10|0
11|0
12|0
13|0
14|0
15|0
16|0
17|0
18|0
19|0
有人可以帮我吗
谢谢
推荐答案
首先,我建议在一年内使用一张带有额外列的表.
First off, I would suggest having ONE table with an extra column for a year.
其次,有了当前的表结构,就可以了
Second, with the current table structure, you can do
SELECT `Horse Number`, Count(*) AS `Total Winners`
FROM (
SELECT * FROM `races`.`2009`
UNION ALL
SELECT * FROM `races`.`2010`
) all_races
WHERE `Win $`>0
GROUP BY `Horse Number`
ORDER BY Count(*) DESC;
这篇关于多个表上的 SQL Count(*)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!