Chaque identifiant fait partie d'un groupe et de ces identifiants ont leur jeu préféré joué.
Structure de sortie:
create table #t1 (id int,[group] varchar(10),game1 varchar(10),game2 varchar(10),game3 varchar(10)) insert into #t1 values (1, 'brazil','wow','clash','dofus'), (1, 'brazil','fifa','clash','dofus'), (1, 'brazil','wow','wakfu','dofus'), (2, 'korea','clash','dofus','clash'), (2, 'korea','clash','dofus','clash'), (3, 'france','wow','fifa','nfl'), (3, 'france','wow','fifa','nfl')
Objectif:
Je dois prendre la première valeur pour jeu1, jeu2, jeu3 par groupe. Le premier serait le jeu qui apparaît le plus souvent dans la colonne.
Le résultat devrait ressembler à ceci:
+--------+--------+-------+-------+ | group | game1 | game2 | game3 | +--------+--------+-------+-------+ | brazil | wow | clash | dofus | | korea | clash | dofus | clash | | france | wow | fifa | nfl | +--------+--------+-------+-------+
Données:
+----+--------+-------+-------+-------+ | id | group | game1 | game2 | game3 | +----+--------+-------+-------+-------+ | 1 | brazil | wow | clash | dofus | | 1 | brazil | fifa | clash| dofus | | 1 | brazil | wow | wakfu | dofus | | 2 | korea | clash | dofus | clash | | 2 | korea | clash | dofus | clash | | 3 | france | wow | fifa | nfl | | 3 | france | wow | fifa | nfl | +----+--------+-------+-------+-------+
3 Réponses :
Avec un CTE qui UNION rassemble les 3 colonnes dans 1 colonne, puis agrège dessus:
> id | group | game1 | game2 | game3 > -: | :----- | :---- | :---- | :---- > 1 | brazil | wow | clash | dofus > 2 | korea | clash | dofus | clash > 3 | france | wow | fifa | nfl
Voir le démo .
Résultats:
with cte as (
select
id, [group], gamecol, game,
row_number() over (partition by [group], gamecol order by count(*) desc) rn
from (
select id, [group], 'game1' gamecol, game1 game from #t1
union all
select id, [group], 'game2', game2 from #t1
union all
select id, [group], 'game3', game3 from #t1
) t
group by id, [group], gamecol, game
)
select
id, [group],
max(case when gamecol = 'game1' then game end) game1,
max(case when gamecol = 'game2' then game end) game2,
max(case when gamecol = 'game3' then game end) game3
from cte
where rn = 1
group by id, [group]
order by id
Je vais suggérer cross apply :
select t.group, g1.game1, g2.game2, g3.game3
from (select distinct group
from #t1 t
) t cross apply
(select top (1) game1
from #t1 t
group by game1
order by count(*) desc
) g1 cross apply
(select top (1) game2
from #t1 t
group by game2
order by count(*) desc
) g2 cross apply
(select top (1) game3
from #t1 t
group by game3
order by count(*) desc
) g3;
J'aime à quel point cela est simplifié, mais l'application croisée ne serait-elle pas lente sur de grandes quantités de données?
@TinyHaitian. . . En fait, cette méthode peut tirer parti d'index séparés sur chaque colonne. Cela signifie qu'il peut être optimisé pour être plus rapide que toute autre méthode à laquelle je peux penser.
Premièrement, si mon opinion veut dire quelque chose, renommez la colonne intitulée groupe . Bien que je suppose que vous l'avez peut-être tapé comme ceci pour l'explication, cela pourrait vous donner une erreur car il est réservé (ou non, si des crochets sont utilisés). Si quoi que ce soit, cela faciliterait la lecture.
Dans d'autres nouvelles, si vous pouviez utiliser CTE, je suggérerais ce qui suit:
;WITH Set1 AS
(
SELECT id, GameGroup, game1,
ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY id ASC, count(game1) DESC) rn
FROM #t1
GROUP BY id, GameGroup, game1
),
Set2 AS
(
SELECT id, GameGroup, game2,
ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY id ASC, count(game2) DESC) rn
FROM #t1
GROUP BY id, GameGroup, game2
),
Set3 AS
(
SELECT id, GameGroup, game3,
ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY id ASC, count(game3) DESC) rn
FROM #t1
GROUP BY id, GameGroup, game3
)
SELECT a.GameGroup, a.game1, b.game2, c.game3
FROM Set1 a
INNER JOIN Set2 b
ON a.id = b. id AND a.rn = b.rn
INNER JOIN Set3 c
ON a.id = c.id AND a.rn = c.rn
WHERE a.rn = 1
Exemple répertorié ici :
Avez-vous essayé quelque chose?
Quelle version de SQL Server utilisez-vous?
Que se passe-t-il s'il y a des jeux du même nombre? Commandez-vous ensuite sur la description du jeu ou rapportez-vous les deux?