Comment combiner GROUPE PAR et ROW_NUMBER?

J'espère exemple de code suivant est auto-explicatif:

declare @t1 table (ID int,Price money, Name varchar(10))
declare @t2 table (ID int,Orders int,  Name varchar(10))
declare @relation  table (t1ID int,t2ID int)
insert into @t1 values(1, 200, 'AAA');
insert into @t1 values(2, 150, 'BBB');
insert into @t1 values(3, 100, 'CCC');
insert into @t2 values(1,25,'aaa');
insert into @t2 values(2,35,'bbb');
insert into @relation values(1,1);
insert into @relation values(2,1);
insert into @relation values(3,2);

select T2.ID AS T2ID
,T2.Name as T2Name
,T2.Orders
,T1.ID AS T1ID
,T1.Name As T1Name
,T1Sum.Price
FROM @t2 T2
INNER JOIN (
    SELECT Rel.t2ID
        ,MAX(Rel.t1ID)AS t1ID 
-- the MAX returns an arbitrary ID, what i need is: 
--      ,ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList
        ,SUM(Price)AS Price
        FROM @t1 T1 
        INNER JOIN @relation Rel ON Rel.t1ID=T1.ID
        GROUP BY Rel.t2ID
)AS T1Sum ON  T1Sum.t2ID = T2.ID
INNER JOIN @t1 T1 ON T1Sum.t1ID=T1.ID

Résultat:

T2ID   T2Name   Orders  T1ID    T1Name  Price     
 1      aaa       25     2       BBB    350,00     
 2      bbb       35     3       CCC    100,00

Ce dont j'ai besoin est commenté ci-dessus, un moyen d'obtenir le ROW_NUMBER mais aussi à Group By en premier lieu. J'ai donc besoin de la sum de tous les T1-prix regroupées par T2.ID dans la relation-table et dans la requête externe de la t1ID avec le prix le plus élevé.

En d'autres termes: Comment faire pour modifier MAX(Rel.t1ID)AS t1ID à un peu de retourner l'ID avec le prix le plus élevé?

De sorte que le résultat souhaité est(notez que tout d'abord T1ID changé de 2 à 1, puisqu'il a le prix le plus élevé):

T2ID   T2Name   Orders  T1ID    T1Name  Price     
 1      aaa       25     1       AAA    350,00     
 2      bbb       35     3       CCC    100,00

Note: dans le cas où vous vous demandez pourquoi je ne multipliez pas Orders avec les Prix: ils ne sont pas liée(donc je devrais avoir laissé cette colonne, car il est un peu ambigu, veuillez l'ignorer, j'ai juste ajouté ça à faire tous les moins abstraite). En fait Orders doit rester inchangé, c'est la raison pour laquelle la sous-requête approche à joindre les deux et la raison pour laquelle j'ai besoin de group by dans la première place.

Conclusion: évidemment, le cœur de ma question peut être répondu par la SUR l'article qui peut être appliquée à tout fonction d'agrégation comme SUM(voir Damien réponse) ce qui était nouveau pour moi. Merci à tous pour vos méthodes de travail.

Ne pourrait-il pas être AAA dans le résultat final, au lieu de BBB?

InformationsquelleAutor Tim Schmelter | 2012-03-08

66

Wow, les autres réponses paraître complexe - donc je suis en espérant que je n'ai pas raté quelque chose d'évident.

Vous pouvez utiliser OVER/PARTITION BY contre les agrégats, et ils vont ensuite faire de regroupement/agrégation sans GROUP BY clause. Alors j'ai juste modifié la requête:
```
select T2.ID AS T2ID
    ,T2.Name as T2Name
    ,T2.Orders
    ,T1.ID AS T1ID
    ,T1.Name As T1Name
    ,T1Sum.Price
FROM @t2 T2
INNER JOIN (
    SELECT Rel.t2ID
        ,Rel.t1ID
 --       ,MAX(Rel.t1ID)AS t1ID 
-- the MAX returns an arbitrary ID, what i need is: 
      ,ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList
        ,SUM(Price)OVER(PARTITION BY Rel.t2ID) AS Price
        FROM @t1 T1 
        INNER JOIN @relation Rel ON Rel.t1ID=T1.ID
--        GROUP BY Rel.t2ID
)AS T1Sum ON  T1Sum.t2ID = T2.ID
INNER JOIN @t1 T1 ON T1Sum.t1ID=T1.ID
where t1Sum.PriceList = 1
```
Qui donne le résultat voulu définir.
- Wow, c'est ce que j'ai espéré et les autres réponses de ce que j'ai à craindre. Merci (toutes), je dois regarder de plus près pour voir comment je peux l'installer dans ma requête réelle(mon exemple est très réduit).
- Merci!!!! J'ai été à essayer de comprendre comment faire ce travail pour un peu différentes fonctions de fenêtrage. J'ai continué à mettre la somme dans la partition par la clause de au lieu de au début!
InformationsquelleAutor Damien_The_Unbeliever

Sans doute cela peut être simplifié, mais les résultats correspondent à vos attentes.

L'essentiel de ce qui est à

Calculer le prix maximum dans un autre CTE pour chaque t2ID
Calculer le prix total dans un autre CTE pour chaque t2ID
Combiner les résultats de deux CTE's

Instruction SQL

;WITH MaxPrice AS ( 
    SELECT  t2ID
            , t1ID
    FROM    (       
                SELECT  t2.ID AS t2ID
                        , t1.ID AS t1ID
                        , rn = ROW_NUMBER() OVER (PARTITION BY t2.ID ORDER BY t1.Price DESC)
                FROM    @t1 t1
                        INNER JOIN @relation r ON r.t1ID = t1.ID        
                        INNER JOIN @t2 t2 ON t2.ID = r.t2ID
            ) maxt1
    WHERE   maxt1.rn = 1                            
)
, SumPrice AS (
    SELECT  t2ID = t2.ID
            , Price = SUM(Price)
    FROM    @t1 t1
            INNER JOIN @relation r ON r.t1ID = t1.ID
            INNER JOIN @t2 t2 ON t2.ID = r.t2ID
    GROUP BY
            t2.ID           
)           
SELECT  t2.ID
        , t2.Name
        , t2.Orders
        , mp.t1ID
        , t1.ID
        , t1.Name
        , sp.Price
FROM    @t2 t2
        INNER JOIN MaxPrice mp ON mp.t2ID = t2.ID
        INNER JOIN SumPrice sp ON sp.t2ID = t2.ID
        INNER JOIN @t1 t1 ON t1.ID = mp.t1ID

InformationsquelleAutor Lieven Keersmaekers

;with C as
(
  select Rel.t2ID,
         Rel.t1ID,
         t1.Price,
         row_number() over(partition by Rel.t2ID order by t1.Price desc) as rn
  from @t1 as T1
    inner join @relation as Rel
      on T1.ID = Rel.t1ID
)
select T2.ID as T2ID,
       T2.Name as T2Name,
       T2.Orders,
       T1.ID as T1ID,
       T1.Name as T1Name,
       T1Sum.Price
from @t2 as T2
  inner join (
              select C1.t2ID,
                     sum(C1.Price) as Price,
                     C2.t1ID
              from C as C1
                inner join C as C2 
                  on C1.t2ID = C2.t2ID and
                     C2.rn = 1
              group by C1.t2ID, C2.t1ID
             ) as T1Sum
    on T2.ID = T1Sum.t2ID
  inner join @t1 as T1
    on T1.ID = T1Sum.t1ID

InformationsquelleAutor Mikael Eriksson

La déduplication (pour sélectionner le max T1) et de l'agrégation doivent être faites comme des étapes distinctes. J'ai utilisé un CTE, car je pense que cela le rend plus clair:

;WITH sumCTE
AS
(
    SELECT  Rel.t2ID, SUM(Price) price
    FROM    @t1         AS T1
    JOIN    @relation   AS Rel 
    ON      Rel.t1ID=T1.ID
    GROUP 
    BY      Rel.t2ID
)
,maxCTE
AS
(
    SELECT  Rel.t2ID, Rel.t1ID, 
            ROW_NUMBER()OVER(Partition By Rel.t2ID Order By Price DESC)As PriceList
    FROM    @t1         AS T1
    JOIN    @relation   AS Rel 
    ON      Rel.t1ID=T1.ID
)
SELECT T2.ID AS T2ID
,T2.Name as T2Name
,T2.Orders
,T1.ID AS T1ID
,T1.Name As T1Name
,sumT1.Price
FROM    @t2 AS T2
JOIN    sumCTE AS sumT1
ON      sumT1.t2ID = t2.ID
JOIN    maxCTE AS maxT1
ON      maxT1.t2ID = t2.ID
JOIN    @t1 AS T1
ON      T1.ID = maxT1.t1ID
WHERE   maxT1.PriceList = 1

InformationsquelleAutor Ed Harper

Vous devez vous connecter pour publier un commentaire.