Oracle - 组操作的一些问题

发布于 2024-12-14 10:38:17 字数 1391 浏览 2 评论 0原文

我需要你的帮助...我尝试了更多时间，但找不到解决方案（我认为这很简单，但我在 oracle :( :( ）中有错误

：一个例子：我有一张桌子球队：球队参加了什么比赛 gameDate: 玩的时候 goalHours: 当 teamA 或 teamB 完成目标时 goalNumber：同一小时内完成了多少目标 endGame：完成游戏后，

teams            gameDate goalHours goalNumber endGame  
team01-VS-team02 20110901 21 2 20110902
team01-VS-team02 20110901 22 1 20110902
team01-VS-team02 20110901 23 3 20110902
team03-VS-team02 20110902 18 3 20110905 
team03-VS-team02 20110902 19 1 20110905
team04-VS-team02 20110904 06 5 20110906
team01-VS-team02 20110101 18 1 20110902

我需要选择所有不同的 teamA-VS-teamB 最后一场比赛日期我需要每个不同的球队 A-VS-球队 B 在他们的最后一场比赛中的不同时间的 goalHours、goalNumber 的总和以及何时停止比赛。

像这样的事情：

teams            lastGameDate distinctGoalHours sumGoalNumber endGame  
team01-VS-team02 20110901 3 6 20110902
team03-VS-team02 20110902 2 4 20110905 
team04-VS-team02 20110904 1 5 20110906

我尝试：

SELECT   teams,
MAX(gameDate),
MAX (endDate),
SUM (goalNumber),
COUNT (DISTINCT goalHours)
       FROM myTable
GROUP BY teams

这个选择效果不好，因为我不想计数 team01-VS-team02 20110101 18 1 20110902，这不是最后一场比赛的日期。通过此选择，我有 4 个不同的GoalHours 和 7 个 sumGoalNumber。

我需要选择

team01-VS-team02 20110901 3 6 20110902
team03-VS-team02 20110902 2 4 20110905
team04-VS-team02 20110904 1 5 20110906

...我希望我向您展示您需要的所有信息，非常感谢您！：D

原文

I need your help ... i tryed more time but can't find a soluction (i think that is simple but I have errors in oracle :( :( )

an example:
i have a table with
teams: what team played
gameDate: when played
goalHours: when teamA or teamB do a goal
goalNumber: how much goal are doing in the same hour
endGame: when finish the game

teams            gameDate goalHours goalNumber endGame  
team01-VS-team02 20110901 21 2 20110902
team01-VS-team02 20110901 22 1 20110902
team01-VS-team02 20110901 23 3 20110902
team03-VS-team02 20110902 18 3 20110905 
team03-VS-team02 20110902 19 1 20110905
team04-VS-team02 20110904 06 5 20110906
team01-VS-team02 20110101 18 1 20110902

I need a select with all distinct teamA-VS-teamB the last gameDate
i need for every distinct teamA-VS-teamB, in their last game, the distinct hours of goalHours, the sum of goalNumber, and when stop the game.

something like this:

teams            lastGameDate distinctGoalHours sumGoalNumber endGame  
team01-VS-team02 20110901 3 6 20110902
team03-VS-team02 20110902 2 4 20110905 
team04-VS-team02 20110904 1 5 20110906

i try with:

SELECT   teams,
MAX(gameDate),
MAX (endDate),
SUM (goalNumber),
COUNT (DISTINCT goalHours)
       FROM myTable
GROUP BY teams

this select doesn't work well because i don't want count
team01-VS-team02 20110101 18 1 20110902, this isn't last game date.
with this select i have 4 distinctGoalHours and 7 sumGoalNumber.

i need a select with

team01-VS-team02 20110901 3 6 20110902
team03-VS-team02 20110902 2 4 20110905
team04-VS-team02 20110904 1 5 20110906

... I hope that i show you all information that you need and thank you very much! :D

分享到QQ

分享到微博

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

发布评论

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

蓝颜夕 2024-12-21 10:38:17

与任何其他 SQL 一样，您需要使用适当的过滤器来限制结果集。在您的情况下，您需要使用子查询来识别每个团队的最新 gameDate。

有几种不同的方法可以实现这一点。这是一个有效的方法：

SQL> SELECT   teams,
            MAX(gameDate),
            MAX (endGame),
            SUM (goalNumber),
            COUNT (DISTINCT goalHours)
FROM  myTable
WHERE (teams, gameDate) IN
    ( select teams, max(gameDate)
      from myTable
      GROUP BY teams )
GROUP BY teams
/
  2    3    4    5    6    7    8    9   10   11   12  
TEAMS                          MAX(GAMED MAX(ENDGA SUM(GOALNUMBER) COUNT(DISTINCTGOALHOURS)
------------------------------ --------- --------- --------------- ------------------------
team03-VS-team02               02-SEP-11 05-SEP-11               4                        2
team01-VS-team02               01-SEP-11 02-SEP-11               6                        3
team04-VS-team02               04-SEP-11 06-SEP-11               5                        1

SQL>

Like any other SQL, you need to restrict the result set with the appropriate filter. In your case, you need to use a sub-query to identify the latest gameDate for each team.

There are several different ways of implementing this. Here is one which works:

SQL> SELECT   teams,
            MAX(gameDate),
            MAX (endGame),
            SUM (goalNumber),
            COUNT (DISTINCT goalHours)
FROM  myTable
WHERE (teams, gameDate) IN
    ( select teams, max(gameDate)
      from myTable
      GROUP BY teams )
GROUP BY teams
/
  2    3    4    5    6    7    8    9   10   11   12  
TEAMS                          MAX(GAMED MAX(ENDGA SUM(GOALNUMBER) COUNT(DISTINCTGOALHOURS)
------------------------------ --------- --------- --------------- ------------------------
team03-VS-team02               02-SEP-11 05-SEP-11               4                        2
team01-VS-team02               01-SEP-11 02-SEP-11               6                        3
team04-VS-team02               04-SEP-11 06-SEP-11               5                        1

SQL>

回复收藏 0 原文

小鸟爱天空丶 2024-12-21 10:38:17

您需要将 gameDate 包含在分组依据中。像这样的东西：

SELECT  teams
    ,   gameDate
    ,   MAX( endDate )
    ,   SUM( goalNumber )
    ,   COUNT( DISTINCT goalHours )
FROM    myTable
GROUP BY teams
    ,    gameDate

You need to include the gameDate in the group by. Something like:

SELECT  teams
    ,   gameDate
    ,   MAX( endDate )
    ,   SUM( goalNumber )
    ,   COUNT( DISTINCT goalHours )
FROM    myTable
GROUP BY teams
    ,    gameDate

回复收藏 0 原文

~没有更多了~