31 svar · 1 208 visningar · startad av budg1e
Vet inte om det går att lösa med en SQL-fråga, jag använder MSSQL 2005.
Jag har två tabeller
Tabell Laginfo som innehåller fälten ID, LagNamn, Lagledare.
Tabell Resultat som innehåller fälten LagID, Score1, Time1, Score2, Time2
Om det går önskar jag summera ett lags reultat efter följande regel
Det kan vara 3-4 spelare i varje lag. Det 4:e resultatet ska räknas bort.
Sorteringen ska vara:
Order By LagID, score1, time1, score2, time2
Lag || Namn || score1 || time1 || score2 || time2
1 || spelare4 || 0 || 14,7 || 0 || 10,1
1 || spelare3 || 0 || 12,6 || 0 || 10,9
1 || spelare1 || 0 || 12,4 || 2 || 10,8
1 || spelare2 || 3 || 10,7
Min fråga är nu... Hur gör man för att summera score1, time1, score2, time2 utan att täkna in det sämsta resultatet.
Resultat för Lag 1 ska bli
score1 0
time1 39,7
score2 2
time2 31,8
Helst vill jag presentera följande utseende
Lag 1 || Lagnamn || 0 || 39,7 || 2 || 31,8
spelare4 || 0 || 14,7 || 0 || 10,1
spelare3 || 0 || 12,6 || 0 || 10,9
spelare1 || 0 || 12,4 || 2 || 10,8
spelare2 || 3 || 10,7
Hur beräknar man det fjärde resultatet?
Det finns ju både score1, time1, score2 och time2.
Vilket är bäst / vilket är sämst?
Varför har spelare 2 ingen tid/poäng på score2/time2? Hur skall det hanteras? Om vi tänker oss att spelare x har en bättre score1/time1 än spelare y, men att spelare y ändå har både score1/time1/score2/time2. Vem vinner då?
Det bästa är att ha 0 i både score 1 och score2 och så lite tid som möjligt. Om en spelare har mer än 0 i score1 for han inte fortsätta tävla, därför kan score2 och time2 vara NULL.
ok, hur rankas score mot time?
Om vi exempelvis har två användare
spelare 1
score1 - 0
time1 - 50
score2 - 0
time2 - 100
spelare 2
score1 - 0
time1 - 40
score2 - 1
time2 - 10
Så spelare 2 är mycket snabbare i både time1 och time2, men score2 så fick han 1 poäng. Vem vinner?
Är det alltid totaltiden och totalpoängen som avgör sorteringen? Eller är time1 viktigare än time2?
Score rankas högre än time. Se min tabell nedan, spelare 4 vinner före spelare 3 osv. För att delta i andra rundan måste man ha 0 fel i Score1 (runda1)
spelare4 || 0 || 14,7 || 0 || 10,1
spelare3 || 0 || 12,6 || 0 || 10,9
spelare1 || 0 || 12,4 || 2 || 10,8
spelare2 || 3 || 10,7
Spelare 1 hamnar efter spelare 3 eftersom han har 2 fel i score2 medan spelare 3 har 0 fel...
Om samma värde i Score2 så vinner snabbaste tiden (time2)
Om samma värde både i Score2 & Time2 så avgör Time1 vem som kommer först
Skulle det va samma värde i alla hamnar man på samma placering
Alla 4 spelare tillhör samma lag där resultaten av deras fel och tider ska summeras förutom för den spelare med sämst resultat.
Kan detta fungera?
--summering av lag och dess poäng..
select
resultat.lagid, LagNamn, SUM(score1) as score1, SUM(time1) as time1, SUM(score2) as score2, SUM(time2) as time2
from
(select *, rank() over (partition by lagid order by score1+coalesce(score2,100), time1+coalesce(time2,0) desc) as 'rank' from #resultat) as resultat
inner join laginfo on resultat.LagId = laginfo.LagId
where
[rank] < 4
group by
resultat.LagId, laginfo.LagNamn
--de tre bästa från varje lag...
select
*
from
(select rank() over (partition by lagid order by score1+score2, time1+time2 desc) as 'rank', lagid, namn, score1, time1, score2, time2 from resultat where score2 is not null and time2 is not null) t
where
[rank] < 4
order by
lagid, score1+score2 asc, time1+time2 desc
Hej & tack för besväret.
Redigerat
tog bort # framför resultat och ändrade
ON resultat.LagId = laginfo.LagId till ON resultat.LagId = laginfo.Id
SELECT
[INDENT]resultat.lagid,
LagNamn,
SUM(score1) AS score1,
SUM(time1) AS time1,
SUM(score2) AS score2,
SUM(time2) AS time2[/INDENT]
FROM
(SELECT *, rank() OVER (partition BY lagid
ORDER BY
[INDENT]score1 + COALESCE (score2, 100),
time1 + COALESCE (time2, 0) DESC) AS 'rank'[/INDENT]
FROM #resultat) AS resultat
INNER JOIN
laginfo ON resultat.LagId = laginfo.LagId
WHERE [rank] < 4
GROUP BY resultat.LagId, laginfo.LagNamn
SELECT * FROM
(SELECT rank() OVER (partition BY lagid
ORDER BY score1 + score2, time1 + time2 DESC) AS 'rank', lagid, namn, score1, time1, score2, time2
FROM resultat WHERE score2 IS NOT NULL AND time2 IS NOT NULL) t
WHERE [rank] < 4
ORDER BY lagid, score1 + score2 ASC, time1 + time2 DESC
Stort tack, helt otroligt att lyckas på första försöket...
Allt fungerar perfekt efter min beskrivning, tyvärr var den felaktig :(
Jag skulle vilja ha summeringen av de 3 bästa tiderna i time1 oavsett hur tiderna ser ut i time2. Nu summeras de 3 tiderna i time1 efter de 3 snabbaste tiderna i time2
Ledsen för besväret...
Redigerat
Den verkar räkna fel på time2 också... ett exempel nedan
spelare1
score1 = 0
time1 = 60,00
score2 = 4
time2 = 46,20
spelare2
score1 = 0
time1 = 53,90
score2 = 4
time2 = 29,10
spelare3
score1 = 0
time1 = 58,20
score2 = 0
time2 = 34,60
spelare4
score1 = 0
time1= 52,80
score2 = 0
time2 = 35,20
Här borde resultatet efter summeringen bli
score1 = 0
time1 = 164,9
score2 = 4
time2 = 98,9
Men det blir
score1 = 0
time1 = 171
score2 = 4
time2 = 116
en annan vy av resultaten där det fetstilta borde vara det som summeras
0 || 60,00 || 4 || 46,20
0 || 53,90 || 4 || 29,10
0 || 58,20 || 0 || 34,60
0 || 52,80 || 0 || 35,20
Ok, då kan du ju ändra så att man inte summerar time1+time2 utan istället sorterar time1 först och om de är lika så går den efter time2.
Prova detta.
SELECTresultat.lagid,
LagNamn,
SUM(score1) AS score1,
SUM(time1) AS time1,
SUM(score2) AS score2,
SUM(time2) AS time2FROM
(SELECT *, rank() OVER (partition BY lagid
ORDER BY score1 + COALESCE (score2, 100),
time1 DESC, COALESCE (time2, 0) DESC) AS 'rank'FROM #resultat) AS resultat
INNER JOIN
laginfo ON resultat.LagId = laginfo.LagId
WHERE [rank] < 4
GROUP BY resultat.LagId, laginfo.LagNamn
SELECT * FROM
(SELECT rank() OVER (partition BY lagid
ORDER BY score1 + score2, time1 DESC, time2 DESC) AS 'rank', lagid, namn, score1, time1, score2, time2
FROM resultat WHERE score2 IS NOT NULL AND time2 IS NOT NULL) t
WHERE [rank] < 4
ORDER BY lagid, score1 + score2 ASC, time1 DESC, time2 DESC
Får felmeddelande nu
Felaktig syntax nära nyckelordet 'AS'
verkar vara vid första Order By
Hittade felet men den summerar fortfarande fel... Koden jag använder är nu
SELECT resultat.lagid, LagNamn, SUM(score1) AS score1, SUM(time1) AS time1, SUM(score2) AS score2, SUM(time2) AS time2
FROM (SELECT *, rank() OVER (partition BY lagid
ORDER BY score1 + COALESCE (score2, 100), time1 DESC, COALESCE (time2, 0) DESC) AS 'rank'
FROM resultat) AS resultat INNER JOIN
laginfo ON resultat.LagId = laginfo.Id
WHERE [rank] < 4
GROUP BY resultat.LagId, laginfo.LagNamn
SELECT *
FROM (SELECT rank() OVER (partition BY lagid
ORDER BY score1 + score2, time1 DESC, time2 DESC) AS 'rank', lagid, namn, score1, time1, score2, time2
FROM resultat
WHERE score2 IS NOT NULL AND time2 IS NOT NULL) t
WHERE [rank] < 4
ORDER BY lagid, score1 + score2 ASC, time1 DESC, time2 DESC
Ok, jag trodde högre tid var bättre med tanke på ditt första inlägg
spelare4 || 0 || 14,7 || 0 || 10,1
spelare3 || 0 || 12,6 || 0 || 10,9
spelare1 || 0 || 12,4 || 2 || 10,8
spelare2 || 3 || 10,7
Prova denna koden då?
select
resultat.lagid, LagNamn, sum(score1) as score1, SUM(time1) as time1, SUM(score2) as score2, SUM(time2) as time2
from
(select *, rank() over (partition by lagid order by score1+coalesce(score2,100), time1, coalesce(time2,0)) as 'rank' from resultat) as resultat
inner join laginfo on resultat.LagId = laginfo.id
where
[rank] < 4
group by
resultat.LagId, laginfo.LagNamn
select
*
from
(select rank() over (partition by lagid order by score1+score2, time1, time2) as 'rank', lagid, namn, score1, time1, score2, time2 from resultat where score2 is not null and time2 is not null) t
where
[rank] < 4
order by
lagid, score1+score2, time1, time2
Nu är det nära... Av 13 lag räknar den rätt på 12! Det blir bara ett fel
Här summerar den de tre första i time1 när den borde summera 1,3,4 (inte andra raden)
Summeringen i score1, score2, time2 är rätt, det är endast time1 som är fel
0 58,50 0 34,70
0 59,00 4 46,60
0 57,90 4 40,60
0 57,30 8 38,50
felaktigt
0 175,4 8 121,9
rätt
0 173,7 8 121,9
ok.. ytterligare feltolkningar. Jag tolkade det som att score1+score2 gick före tiden?
I ditt exempel får ju fjärde raden 8 i score vilket borde göra att den rankas sist?
Om vi rankar om så att time1 är viktigare än score2 så kommer ju inte totalsummeringen på 121.9 i time att stämma, och dessutom kommer det bli 12 (8+4) i score2.
Hur tänker du kring det?
Ursäkta röran...
Om det går att lösa vore rätt uträkning denna.
# steg1
Summera score1 och time1 där de tre bästa resultaten är score1, time1 (om det finns 2 eller fler spelare med samma score1 ska time1 särskilja där snabbast tid är bättre)
#steg2
samma som ovan fast med score2, time2
ok. Betyder det att följande ranking är rätt?
lagid namn score1 time1 score2 time2
2 spelare 4 0 57,3 8 38,5
2 spelare 3 0 57,9 4 40,6
2 spelare 1 0 58,5 0 34,7
2 spelare 2 0 59 4 46,6 (denna räknas inte i totalen)
Spelare 4 vinner ju på score1 och time1. Men han förlorar ju totalt sett eftersom han har 8 i score2. Hur
Det är fel
Vem vinnaren är i slutändan spelar ingen roll. Jag är bara intresserad av de 3 bästa reultaten i grundomgången (score1 & time1). Dessa personer behöver inte vara samma i den andra rundan (score2 & time2) I ditt exempel ovan så stämmer resultaten för score1 & time1. Där ska inte spelare2´s resultat räknas in. I Omgång 2 borde spelare 3,1,2 räknas eftersom seplare 4 har 8 i score2 och därmed sämst i andra rundan...
Hoppas du förstår...
2 spelare 4 0 57,3 8 38,5
2 spelare 3 0 57,9 4 40,6
2 spelare 1 0 58,5 0 34,7
2 spelare 2 0 59 4 46,6 (denna räknas inte i totalen)
ok, detta kan såklart lösas för summeringen.
Men när man faktiskt visar raderna så måste ju de sorteras på något sätt. Jag pratar om fråga nummer två som visar flera rader med de 3 bästa spelarna. Hur skall det fungera?
Det bästa vore om de sorterades efter startnumret, fältet heter (T_pos) i tabellen Resultat.
ok, prova detta?
select
resultat.lagid, LagNamn, SUM(CASE WHEN rank1 BETWEEN 1 AND 3 THEN score1 ELSE 0 END) as score1, SUM(CASE WHEN rank1 BETWEEN 1 AND 3 THEN time1 ELSE 0 END) as time1, SUM(CASE WHEN rank2 BETWEEN 1 AND 3 THEN score2 ELSE 0 END) as score2, SUM(CASE WHEN rank2 BETWEEN 1 AND 3 THEN time2 ELSE 0 END) as time2
from
(select *,
rank() over (partition by lagid order by score1, time1) as 'rank1',
rank() over (partition by lagid order by score2, time2) as 'rank2' from resultat) as resultat
inner join laginfo on resultat.LagId = laginfo.id
where
rank1 < 4 or rank2 < 4
group by
resultat.LagId, laginfo.LagNamn
select
*
from
(select rank() over (partition by lagid order by score1, time1, score2, time2) as 'rank', lagid, namn, score1, time1, score2, time2, t_pos from resultat where score2 is not null and time2 is not null) t
where
[rank] < 4
order by
lagid, score1, time1, t_pos