-1
votes

en utilisant Trier par et jointure gauche ou droite dans CTE

Veuillez considérer ce tableau:

Between 20 and 24           0           0           0          0

et ce CTE :

with p as
(
   SELECT top 100 percent [min],
                          [max],
                          [Desc]
    FROM   [MyBoundaries]
    ORDER BY rownumber
)
select 
    p.[Desc],
    COUNT(CASE when (tbl.col_2 >= 0 AND tbl.col_2 < 5) THEN 1 END),
    COUNT(CASE when (tbl.col_2 >= 5 AND tbl.col_2 < 10) THEN 1 END),
    COUNT(CASE when (tbl.col_2 >= 10 AND tbl.col_2 < 15) THEN 1 END),
    COUNT(CASE when (tbl.col_2 >= 15 AND tbl.col_2 < 20) THEN 1 END)
from [dbo].[MyTable] AS tbl
   inner join p on tbl.col_1 >= p.min and tbl.col_1 <= p.max
where tbl.[YEAR] = 2020
group by p.[Desc]

J'ai plusieurs problèmes avec ce CTE :

L'ordre des enregistrements dans [MyBoundaries] n'est pas conservé et je souhaite que les résultats soient exactement dans l'ordre de la table [MyBoundaries] .

Si je n'ai pas enregistré dans la limite (20, 24) cette limite n'est pas incluse dans la sortie, mais je veux:

Min            Max            Desc
------------------------------------------------
10             24             Total
10             14             Between 10 and 14
15             19             Between 15 and 19
20             24             Between 20 and 24

J'ai changé inner join left ou «droite» mais la sortie n'a pas été modifiée.

Comment puis-je résoudre ces problèmes?

Merci

sql sql-server common-table-expression join sql-server-2014

2 commentaires

Il n'y a pas d'ordre des lignes dans une table. Les opérateurs de table n'ont aucun sens de l'ordre; il n'est donc pas clair pour quoi vous pensez entrer la commande si vous l'aviez. Les ensembles de résultats ont un ordre (partiel). Commander sans limite / haut qui n'est pas au niveau le plus extérieur est un no-op. Expliquez ce que vous attendez et pourquoi vous l'attendez - avec une justification faisant référence à une documentation faisant autorité. Sinon, nous ne pouvons pas résoudre vos idées fausses et ne pouvons que réécrire la documentation - sans savoir ce que vous interprétez mal. Aussi: un code incorrect et aucune attente ne nous dit pas ce que vous voulez. exemple minimal reproductible

Notez que toutes les solutions proposées incluent une clause order by dans la requête externe. Ceci est directement lié au commentaire de phil et le prolonge. Les lignes du jeu de résultats n'ont pas non plus d'ordre particulier, sauf si la requête qui le génère a également une clause order by. Une clause GROUP by tend à conduire à l'apparition de l'ordre en raison du plan de requête - mais il n'y a aucune garantie sans cette clause ORDER BY.

4 Réponses :

1
votes

Vous pouvez utiliser:

select 
    p.[Desc],
    COUNT(CASE when (tbl.col_2 >= 0 AND tbl.col_2 < 5) THEN 1 END),
    COUNT(CASE when (tbl.col_2 >= 5 AND tbl.col_2 < 10) THEN 1 END),
    COUNT(CASE when (tbl.col_2 >= 10 AND tbl.col_2 < 15) THEN 1 END),
    COUNT(CASE when (tbl.col_2 >= 15 AND tbl.col_2 < 20) THEN 1 END),
from (SELECT * FROM [dbo].[MyTable] WHERE [YEAR] = 2020) AS tbl -- filter on source
right join [MyBoundaries] p on tbl.col_1 >= p.min and tbl.col_1 <= p.max -- right join
group by p.[Desc]
order by MIN(p.[rownumber]);

0 commentaires

1
votes

Cela ressemble à un bon endroit pour une jointure latérale:

select b.[Desc], t.*
from [MyBoundaries] b
outer apply (
    select 
        sum(case when t.col_2 >=  0 and t.col_2 <  5 then 1 else 0 end),
        sum(case when t.col_2 >=  5 and t.col_2 < 10 then 1 else 0 end),
        sum(case when t.col_2 >= 10 and t.col_2 < 15 then 1 else 0 end),
        sum(case when t.col_2 >= 15 and t.col_2 < 20 then 1 else 0 end)
    from [dbo].[MyTable] t
    where t.col_1 >= b.min and t.col_1 <= b.max and t.year = 2020
) t
order by b.rownumber

0 commentaires

1
votes

Il y a plusieurs changements pour faire ce que vous voulez:

Vous pouvez utiliser la left join , mais la table dans laquelle vous souhaitez conserver toutes les lignes doit être la première.
Le CTE doit renvoyer rownumber , vous pouvez donc l'inclure dans l' order by .
Filtrage sur les autres besoins de table à la on l' article.

Donc:

with b as (
      SELECT b.*
      FROM [MyBoundaries] b
      ORDER BY rownumber
     )
select b.[Desc],
       COUNT(CASE when tbl.col_2 >= 0 AND tbl.col_2 < 5 THEN 1 END),
       COUNT(CASE when tbl.col_2 >= 5 AND tbl.col_2 < 10 THEN 1 END),
       COUNT(CASE when tbl.col_2 >= 10 AND tbl.col_2 < 15 THEN 1 END),
       COUNT(CASE when tbl.col_2 >= 15 AND tbl.col_2 < 20 THEN 1 END)
from b left join
     [dbo].[MyTable]  tbl
     on tbl.col_1 >= b.min and tbl.col_1 <= b.max and
        tbl.[YEAR] = 2020
group by b.[Desc] 
order by b.rownumber;

J'ai changé le nom CTE en b parce que cela a plus de sens pour les «limites». De plus, je vous recommande d'éviter d'utiliser des noms tels que desc , min et max qui sont des mots réservés SQL comme noms de colonne - ainsi que year qui est un mot-clé et pourrait devenir réservé à l'avenir.

0 commentaires

1
votes

Peut-être que la requête pourrait ressembler à ceci

with p_cte as (
    select top 100 percent [min], [max], [Desc]
    from MyBoundaries)
select pc.[Desc]
       sum(case when (t.col_2 >= 0 and t.col_2 < 5) then 1 end) ZeroToFive,
       sum(case when (t.col_2 >= 5 and t.col_2 < 10) then 1 end) FiveToTen,
       sum(case when (t.col_2 >= 10 and t.col_2 < 15) then 1 end) TenToFifteen,
       sum(case when (t.col_2 >= 15 and t.col_2 < 20) then 1 end) FiftennToTwenty
from p_cte pc
     left join [dbo].[MyTable] t on pc.[min] <= t.col_1 
                                    and pc.[max] >= t.col_1
                                    and t.[YEAR] = 2020
group by pc.[Desc]
order by pc.[min], pc.[Desc] desc;

0 commentaires