帮忙sql查询连接？

发布于 2024-11-11 16:23:45 字数 496 浏览 0 评论 0原文

我有两个表，类型为（类型，玩家ID）和玩家（玩家ID，日期）我必须查明每年有多少场比赛最佳球员是由哪种类型的球员获得的？

例如，表格应该是这样的：

year type      noofawards
2011 batsmen       3
2011 bowler        5
2010 batsmen       2

我可以获得每年获得的奖项总数，但无法按类型将它们分开，所以我得到的是

year noofawards
2011     3 


select year , count(year) as "Number of awards"
from
(
select to_char(p.date,'YYYY') as year 
from player p, type t
where p.playerid = t.playerid
)
group by year
order by year;

我应该做什么？

原文

i have two tables with type(type,playerid) and player(playerid,date)
i have to find out for each year how many man of the match awards were won by which type of player ?

eg the table should be like

year type      noofawards
2011 batsmen       3
2011 bowler        5
2010 batsmen       2

i can get the total number of awards won each year but cannot segregate them on type so what i get is

year noofawards
2011     3 


select year , count(year) as "Number of awards"
from
(
select to_char(p.date,'YYYY') as year 
from player p, type t
where p.playerid = t.playerid
)
group by year
order by year;

what should i do?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

爱她像谁 2024-11-18 16:23:45

您是否尝试过以下操作：

select to_char(p.date,'YYYY') as year
     , type
     , count(*)
from player p, type t
where p.playerid = t.playerid
group by 1,2;

Did you try the following:

select to_char(p.date,'YYYY') as year
     , type
     , count(*)
from player p, type t
where p.playerid = t.playerid
group by 1,2;

回复收藏 0 原文

桃扇骨 2024-11-18 16:23:45

由于这是家庭作业的尖叫声，我只会给出提示。

您不需要使用子查询，并且需要在查询中的某个时刻实际选择“类型”列。

回复收藏 0 原文

桜花祭 2024-11-18 16:23:45

只需使用：

GROUP BY year
       , type

大多数数据库系统都有一个函数（YEAR() 或类似名称）从日期中提取年份。
使用保留的字段或表名称（例如日期）并不是一个好主意。
您还应该尝试学习 JOIN 语法，而不是使用 WHERE 的隐式连接。

像这样的东西：

SELECT YEAR(p.date) AS awardYear
     , t.type
     , COUNT(*) AS "Number of awards"
FROM player p
  JOIN type t
    ON p.playerid = t.playerid
GROUP BY awardYear
       , t.type
ORDER BY awardYear
       , t.type ;

Just use:

GROUP BY year
       , type

Most database systems have a function (YEAR() or similar name) to extract year from a date.
Having field or table names which are reserved, like date is not a good idea.
You should also try learning the JOIN syntax rather the implicit join with WHERE.

Something like:

SELECT YEAR(p.date) AS awardYear
     , t.type
     , COUNT(*) AS "Number of awards"
FROM player p
  JOIN type t
    ON p.playerid = t.playerid
GROUP BY awardYear
       , t.type
ORDER BY awardYear
       , t.type ;

回复收藏 0 原文

來不及說愛妳 2024-11-18 16:23:45

这应该有效：

SELECT year,
       type,
       COUNT(*) as number
FROM (
       SELECT type,
              to_char(player.date,'YYYY') as year
       FROM player
       NATURAL JOIN type
     ) AS T
GROUP BY year,
         type

This should work:

SELECT year,
       type,
       COUNT(*) as number
FROM (
       SELECT type,
              to_char(player.date,'YYYY') as year
       FROM player
       NATURAL JOIN type
     ) AS T
GROUP BY year,
         type

回复收藏 0 原文

~没有更多了~