MYSQL-da ma'lum bir id uchun maksimal (sum) 2 ta ustunni topish

I have a table which looks likeenter image description here

Muayyan jamoaning maqsadiga erishishim kerak Masalan: Foydalanuvchining Agar Team_id 16 6 va 2-gollarni kiritgan bo'lsa, unda jami sum_goal boshqa yangi ustunda 8 bo'ladi

Keyinroq davom eta olmayapman. Iltimos, yordam bering.

sinab ko'rgan so'rov:

Tanlash (match_id, Sum (Goal) FROM     (     Team1_id identifikatorini tanlang, team1_goal Maqsad match_result_updationdan     Union All haqida     Team2_id identifikatorini tanlang, team2_goal Maqsad match_result_updation-dan     )

as A)

FROM

(Tanlash  c.tournament_id, g. * Komand_trophies dan d Ichki a'zolar tournament_match ga O'N e.tournament_id = d.tournament_id Ichki a'zolar turnir jadvali v O'N c.tournament_id = e.tournament_id Ichki a'zolar f bilan mos keladi ochiq f.match_id = c.id Ichki a'zolar match_result_updation g O'N g.match_id = f.id WHERE f.match_type = "turnir" va d.tournament_id = 1) p Guruhga ko'ra match_id

Ikkinchi tanlovdan id = 1 ga qadar chiqdi, ammo men qo'shimchining rasmida bo'lgani kabi chiqdi, ammo har bir team_id uchun to'plamning summasini topa olmayapman. Shuningdek, agar 16 yoki 17 jamoa yoki jamoa 1 yoki 2 jamoa gollarga qo'shilsa hisoblab chiqiladi.

0

6 javoblar

Mening birinchi fikrimcha, ularni ustunlar qatoriga kiritish va undan keyin jamlash kerak. Barcha SQL vositalarini tahrirlang

Select ID, Sum(Goal) From
    (
    Select Team1_ID ID, Team1_Goal Goal From Table
    Union All
    Select Team2_ID ID, Team2_Goal Goal From Table
    ) A
Group By ID

PS Stol dizayni normallashtirilsa, bu turdagi vaqtinchalik echim zarurat emas.

1
qo'shib qo'ydi
Biz MYSQL dan foydalanmoqdamiz.
qo'shib qo'ydi muallif Rains, manba
Yuqorida keltirilgan javoblarni dastlabki kodimdan foydalanib, muayyan jamoaning gollari miqdorini aniqlashga harakat qildim, ammo to'g'ri natijalarga erishmadim.
qo'shib qo'ydi muallif Rains, manba

buni sinab ko'ring

Select ID, Sum(Goal) From
(
Select Team1_ID ID, Team1_Goal Goal From Table
Union All
Select Team2_ID ID, Team2_Goal Goal From Table
)l,
table_name Group By ID
0
qo'shib qo'ydi

Boshqa versiyasi:

SELECT SUM(nr) 
FROM (
    (SELECT sum(team1_goal) as nr FROM goals WHERE team1_id = 16)
    UNION ALL
    (SELECT sum(team2_goal) as nr FROM goals WHERE team2_id = 16)
)
sum
0
qo'shib qo'ydi

Ushbu so'rovdan foydalaning maqsadning umumiy miqdoriga yordam beradi

select team1_id, sum(team1_goal) as sum_goal from `table` group by `team1_id`;
0
qo'shib qo'ydi
Bu to'g'ri, agar id 16 jamoada 2_id bo'lsa, unda jami golni qanday hisoblash mumkin?
qo'shib qo'ydi muallif Rains, manba

Biz ham shunday qilishimiz mumkin ....

select team1_id, sum(team1_goal) as sum_goal from `table` group by `team1_id`
union all
select team2_id, sum(team2_goal) as sum_goal from `table` group by `team1_id`;

Agar siz yana to'lashni xohlasangiz, unda bunday foydalanishingiz mumkin

select t.team_id, sum(t.sum_goal) as total_goal from (
  select team1_id as team_id, sum(team1_goal) as sum_goal from `table` group by `team1_id`
  union all
  select team2_id as team_id, sum(team2_goal) as sum_goal from `table` group by `team1_id`
) as t group by t.team_id;
0
qo'shib qo'ydi
SELECT team1_id AS Team_ID , sum(team1_goal) as Team_Goal       
FROM 
teams GROUP BY team1_id
UNION
SELECT team2_id AS Team_ID  , sum(team2_goal) as Team_Goal
FROM teams GROUP BY team2_id

Bu har bir jamoaning identifikatori va har bir jamoa identifikatori tomonidan kiritilgan gollarning umumiy soni bilan bitta jadval yaratadi

0
qo'shib qo'ydi