时间戳

首页 > 解决方案 > Query for two tables with similar information

sql - Query for two tables with similar information

问题描述

I am working on some baseball stats to improve my database abilities I have data for hitters from 2017 and 2018 MLB seasons. what I hope to accomplish is to average the number of games played, at bats and hit over the two seasons. Eventually I want to do it over five seasons but I figure I need to start somewhere. The problem is that both tables have exactly the same names for the columns. I think I need to do a UNION but I am not sure.

I am using SSMS, the Express version. I have tried a simple query, thinking it would do some thing but obviously, I should have known better. I tried:

SELECT PLAYER, g, ab, hit
FROM mlb_2017, mlb_2018
WHERE mlb_2017.PLAYER = mlb_2018.PLAYER
ORDER BY PLAYER;

this is the Excel file example. Both 2017 and 2018 have these fields exactly as shown, well, with about ten more fields that I am not using yet.

Player  Team    pos g   ab  hit 2b
abreu,jose  whit    1B  128 499 132 36
acuna,ron   brav    OF  111 433 127 26
adames,will rays    SS  85  288 80  7
adams,lane  brav    OF  26  25  6   1

So what I want, is to combine both of the seasons into one two year total and then average them.

标签： sqlsql-server

解决方案

尝试联合查询：

SELECT PLAYER, AVG(hit) AS avg_hits
FROM
(
    SELECT PLAYER, hit
    FROM mlb_2017
    UNION ALL
    SELECT PLAYER, hit
    FROM mlb_2018
) t
GROUP BY PLAYER;

联合查询背后的想法是，它将 2017 年和 2018 年每个球员的命中数据放入一个中间表中，然后我们可以将其聚合以找到平均值。

推荐阅读