множество водещи n заявки

Трудно ми е да получа следната заявка:

Да кажем, че имам маса с много отбори и всеки отбор има 15 играчи (един към много към маса с играчи).

Тези играчи са подредени в рамките на отбор, чрез колона за ред, от 0 до 14 и имат два атрибута:

  • Спечелени точки (цяло число)
  • wasSick (булева стойност или цяло число със стойности 0 за false или 1 за true)

Бих искал заявка, която връща ред за всеки отбор, където първата колона е ID на отбора (първичен ключ), а втората колона е сумата от точките, отбелязани от първите 11 играчи на всеки отбор, който не е бил болен, използвайки реда, определен от колоната Поръчка.

Ако повече от 3 играчи са болни за даден отбор, той ще използва всички останали.

Това е срещу база данни на Oracle. Опитах да използвам ROWNUM, но имам нужда от подобен rownum, който се нулира за всяко агрегиране. Заявката може да съдържа подзаявки или да използва клаузата WITH.

Благодаря!

PD: Ако сте познали, те са футболни/футболни отбори.

РЕДАКТИРАНЕ

Атрибутът, използван за познаване на реда на играчите в отбора, е колоната за ред. Нека я наречем колона Позиция и има цяло число от 0 до 14. Така че, ако в един отбор играчи 2 и 4 са болни, ще ми трябва сумата от точките, направени от играчи 0,1,3,5,6, 7,8,9,10,11 и 12.

Моят опит беше нещо като:

SELECT t.id, sum(p.points) FROM team t, points p WHERE p.t_id = t.id AND p.wasSick = 0 AND ROWNUM < 12 GROUP BY p.t_id

Това, което не е наред тук е, че ми липсва ORDER BY p.position и че ROWNUM не се нулира между отборите.


sql oracle
person Luciano    schedule 04.01.2011    source източник
comment
Когато казвате отбелязани точки от първите 11 играчи на всеки отбор, има ли атрибут/колона, която може да се използва за подреждането им?   -  person Chandu    schedule 05.01.2011
comment
можеш ли да публикуваш опита си?   -  person Mitch Wheat    schedule 05.01.2011

Отговори (2)


arrow_upward
3
arrow_downward

Тъй като няма препратка към поле, което може да се използва за идентифициране на първите 11 играчи (ако никой от играчите в отбора не е болен), използвах rowid, за да ги подредя. Ако можете да актуализирате въпроса с такова поле, мога да променя заявката. Дотогава :

SELECT team_id,
       SUM(points)
  FROM (
        SELECT t.team_id, 
               p.points
               ROW_NUMBER() OVER(PARTITION BY team_id ORDER BY player_id, p.rowid) rn
          FROM teams t, players p
              WHERE t.team_id = p.team_id
                AND p.wasSick = 0
        )
 WHERE rn < 12
 GROUP BY team_id
person Chandu    schedule 04.01.2011
comment
Намерих ROW_NUMBER, докато търсех решение, но не го разбрах. Вече е по-ясно с твоя пример. Ще опитам този подход, когато се върна на работа утре. - person Luciano; 05.01.2011
comment
Да, row_number беше това, което търсех. - person Luciano; 06.01.2011

arrow_upward
0
arrow_downward

Какво ще кажете за това:

select teamid, 
       sum(pointscored)
  from (select teamid, 
               pointscored, 
               row_number() over (partition by teamid order by orderid) rn
          from (select 1 teamid, 1 playerid, 0 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 2 playerid, 1 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 3 playerid, 2 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 4 playerid, 3 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 5 playerid, 4 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 6 playerid, 5 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 7 playerid, 6 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 8 playerid, 7 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 9 playerid, 8 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 10 playerid, 9 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 11 playerid, 10 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 12 playerid, 11 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 13 playerid, 12 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 14 playerid, 13 orderid, 1 pointscored, 0 wassick from dual union all
                select 1 teamid, 15 playerid, 14 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 1 playerid, 0 orderid, 1 pointscored, 1 wassick from dual union all
                select 2 teamid, 2 playerid, 1 orderid, 1 pointscored, 1 wassick from dual union all
                select 2 teamid, 3 playerid, 2 orderid, 1 pointscored, 1 wassick from dual union all
                select 2 teamid, 4 playerid, 3 orderid, 1 pointscored, 1 wassick from dual union all
                select 2 teamid, 5 playerid, 4 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 6 playerid, 5 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 7 playerid, 6 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 8 playerid, 7 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 9 playerid, 8 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 10 playerid, 9 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 11 playerid, 10 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 12 playerid, 11 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 13 playerid, 12 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 14 playerid, 13 orderid, 1 pointscored, 0 wassick from dual union all
                select 2 teamid, 15 playerid, 14 orderid, 1 pointscored, 0 wassick from dual
               ) teams
         where wassick = 0
       )
 where rn <= 11
group by teamid;

който връща:

    TEAMID SUM(POINTSCORED)
---------- ----------------
         1               11
         2               11

2 rows selected.
person Doug Porter    schedule 04.01.2011
comment
почти същото като @Cybernate, но подреждане по orderid в клаузата за прозорци. Великите умове мислят еднакво. - person Doug Porter; 05.01.2011
comment
Добре, разбрахте какъв е проблемът ми, сега трябва да разбера вашето решение! Заявка в заявка в заявка звучи дълбоко, да не говорим, че заявката, от която се нуждая, вече ще влезе в по-голяма заявка. - person Luciano; 05.01.2011
comment
@Luciano: Не трябва да се страхувате от вложените заявки. По-голямата част от запитването ми са примерни данни. Това, което прави е: 1) Изключване на болни хора 2) Използване на аналитична функция (row_number) за всеки отбор, за да даде възходящ номер на всеки ред за този отбор, подреден по orderid 3) Изхвърляне на всички освен числата 1-11 от резултати от нашата аналитична функция 4) Групиране по отбори за сумиране на точките. - person Doug Porter; 05.01.2011