Quelle est la meilleure façon de sélectionner la valeur minimale à partir de plusieurs colonnes?
D'après le tableau suivant dans SQL Server 2005:
ID Col1 Col2 Col3
-- ---- ---- ----
1 3 34 76
2 32 976 24
3 7 235 3
4 245 1 792
Quelle est la meilleure façon de les écrire la requête qui donne le résultat suivant (c'est à dire celui qui donne de la finale de la colonne, une colonne contenant le minium valeurs de Col1, Col2, et le Col 3 pour chaque ligne)?
ID Col1 Col2 Col3 TheMin
-- ---- ---- ---- ------
1 3 34 76 3
2 32 976 24 24
3 7 235 3 3
4 245 1 792 1
Mise à JOUR:
De clarification (comme je l'ai dit dans les commentaires) dans le scénario réel de la base de données est correctement normalisé. Ces "array" les colonnes ne sont pas dans une table réelle, mais sont dans un jeu de résultats qui est requis dans un rapport. Et la nouvelle exigence est que le rapport a aussi besoin de ce MinValue colonne. Je ne peux pas changer le résultat sous-jacent défini et, par conséquent, j'étais à la recherche de T-SQL pour une pratique de "sortir de prison carte".
J'ai essayé le CAS de l'approche mentionnée ci-dessous et il fonctionne, mais il est un peu lourd. Il est aussi plus compliqué qu'indiqué dans les réponses parce que vous avez besoin pour répondre au fait qu'il y a deux min les valeurs de la même ligne.
De toute façon, je pensais que je poste ma solution actuelle qui, compte tenu de mes contraintes, fonctionne assez bien. Il utilise le UNPIVOT opérateur:
with cte (ID, Col1, Col2, Col3)
as
(
select ID, Col1, Col2, Col3
from TestTable
)
select cte.ID, Col1, Col2, Col3, TheMin from cte
join
(
select
ID, min(Amount) as TheMin
from
cte
UNPIVOT (Amount for AmountCol in (Col1, Col2, Col3)) as unpvt
group by ID
) as minValues
on cte.ID = minValues.ID
Je vais le dire d'avance que je ne vous attendez pas à offrir les meilleures performances, mais étant donné les circonstances (je ne peux pas la refonte de toutes les demandes juste pour la nouvelle MinValue colonne exigence), il est un très élégant "sortir de prison carte".
- À mon humble avis l'auteur UNPIVOT solution est supérieure à l'autre des réponses.
- - Je trouver du Nizam solution pour être le plus mince de solution, même si il m'a fallu un certain temps pour commencer à le comprendre. Maigre et très utilisable.
Vous devez vous connecter pour publier un commentaire.
Il y a probablement beaucoup de façons de le faire. Ma suggestion est d'utiliser des Cas/Quand le faire. Avec 3 colonnes, il n'est pas trop mauvais.
À l'aide de
CROSS APPLY
:SQL Violon
cross apply
. Il ajoute de la valeur/performance ? stackoverflow.com/a/14712024/78522where MinValue > 10
, que je ne pouvais pas faire sansCROSS APPLY
CROSS APPLY
vous pouvez référencer le résultat à l'intérieur de la clause where et par la suite (GROUP BY, HAVING, de FENÊTRE, de SÉLECTIONNER et de COMMANDER PAR...vous pouvez même faire référence à l'intérieur de la REJOINDRE). Si utilisé à l'intérieur deSELECT
vous pouvez accéder à l'intérieur de la COMMANDE PAR.Vous pouvez utiliser la "force brute" approche avec une torsion:
Lors de la première lorsque la condition d'échec, il garantit que Col1 n'est pas la plus petite valeur, par conséquent, vous pouvez l'éliminer d'autres conditions. De même pour les conditions ultérieures. Pour les cinq colonnes de votre requête devient:
Noter que s'il y a égalité entre deux ou plusieurs colonnes, alors
<=
nous permet de sortir de laCASE
déclaration le plus tôt possible.<=
au lieu de cela, sinon, la dernière correspondant min valeur sera utilisée au lieu de la première.La meilleure façon de le faire est probablement pas de le faire - il est étrange que les gens insistent sur le stockage de leurs données d'une manière qui nécessite SQL "gymnastique" pour extraire des informations utiles, quand il y a beaucoup plus de facilité pour obtenir le résultat souhaité si vous venez de la structure de votre schéma un peu mieux 🙂
La droit façon de le faire, à mon avis, est d'avoir le tableau suivant:
avec
ID/Col
comme clé primaire (et, éventuellement,Col
comme une clé supplémentaire, en fonction de vos besoins). Alors votre requête devient un simpleselect min(val) from tbl
et vous pouvez toujours traiter l'individu " vieux colonnes séparément à l'aide dewhere col = 2
dans vos autres requêtes. Cela permet aussi facile d'expansion doit être le nombre de "vieux colonnes de" grandir.Cela rend vos requêtes donc beaucoup plus facile. La directive générale j'ai tendance à utiliser est, si vous jamais avoir quelque chose qui ressemble à un tableau à une ligne de base de données, vous êtes probablement fait quelque chose de mal et devrait réfléchir à la restructuration des données.
Toutefois, si pour quelque raison vous ne peut pas modifier ces colonnes, je vous suggère de l'aide d'insérer et mettre à jour les déclencheurs et les ajouter un autre colonne qui ces déclencheurs au minimum sur
Col1/2/3
. Cela vous permettra de déplacer le "coût" de l'opération, loin de le sélectionner pour la mise à jour/insérer où il appartient - la plupart des tables de base de données dans mon expérience, sont lire beaucoup plus souvent qu'à l'écrit afin d'engager le coût sur l'écriture tend à être plus efficace au fil du temps.En d'autres termes, le minimum pour une ligne ne change que lorsque l'une de l'autre les colonnes de changement, de sorte c'est quand vous devriez être en calcul, pas chaque fois que vous sélectionnez (ce qui est inutile si les données ne changent pas). Vous pourriez alors vous retrouver avec une table:
Toute autre option qui est de prendre des décisions à
select
temps est généralement une mauvaise idée en terme de performance, puisque les données ne change sur insertion/mise à jour - l'ajout d'une autre colonne prend plus de place dans la DB et sera un peu plus lent pour les insertions et mises à jour, mais peut être beaucoup plus rapide pour les sélectionne - le préfère en fonction de vos priorités là, mais, comme indiqué, la plupart des tables sont en lecture loin plus souvent qu'ils sont écrits.Si les colonnes étaient des nombres entiers comme dans votre exemple, je voudrais créer une fonction:
puis quand j'ai besoin de l'utiliser, je le ferais :
si vous avez 5 colonnes puis le ci-dessus devient
Utiliser ceci:
Vous pourriez aussi le faire avec une requête union. Comme le nombre de colonnes d'augmenter, il faut modifier la requête, mais au moins il serait tout droit vers l'avant modification.
Ce est la force brute, mais fonctionne
... parce que min() ne fonctionne que sur une seule colonne et non pas à travers les colonnes.
Les deux cette question
Et cette question essayer de répondre à cette question.
La recap est que l'Oracle a une fonction intégrée pour cela, avec Sql Server, vous êtes coincé, soit la définition d'un définis par l'utilisateur fonction ou à l'aide de cas annuels.
Si vous êtes en mesure de faire une procédure stockée, il pourrait prendre un tableau de valeurs, et vous pouvez simplement appeler qui.
Un petit twist sur la requête union:
Si vous utilisez SQL 2005, vous pouvez faire quelque chose de soigné comme ceci:
De cette façon, vous ne soyez pas perdu dans de nombreux opérateurs 🙂
Toutefois, cela pourrait être plus lent que l'autre choix.
C'est votre choix...
Ci-dessous-je utiliser une table temporaire pour obtenir le minimum de plusieurs dates. La première table temporaire des requêtes sur plusieurs tables jointes pour obtenir diverses dates (ainsi que d'autres valeurs pour la requête), le deuxième de la table temporaire obtient alors les différentes colonnes et la date minimale à l'aide de plus de passes qu'il y a des colonnes de date.
C'est essentiellement de la requête union, le même nombre de passes sont nécessaires, mais peuvent être plus efficaces (basé sur l'expérience, mais aurait besoin de test). L'efficacité n'était pas un problème dans ce cas (de 8 000 dossiers). On pourrait index etc.
Pour plusieurs colonnes de son mieux pour utiliser une instruction de CAS, toutefois, pour les deux colonnes numériques i et j, vous pouvez utiliser un simple calcul:
min(i,j) = (i+j)/2 - abs(i-j)/2
Cette formule peut être utilisée pour obtenir la valeur minimale de plusieurs colonnes, mais son vraiment salissant passé, 2, min(i,j,k) serait min(i,min(j,k))
Si vous savez quelles sont les valeurs que vous recherchez, généralement un code d'état, les éléments suivants peuvent être utiles:
Je sais que la question est vieux, mais j'étais encore dans le besoin de la réponse et n'a pas été heureux avec les autres réponses, donc j'ai eu à concevoir mon propre qui est une torsion sur @paxdiablos réponse.
Je suis venu de la terre de SAP ASE 16.0, et j'ai seulement besoin d'un coup d'oeil sur les statistiques de certaines données qui sont à mon humble avis valablement stockées dans différentes colonnes d'une même ligne (ils représentent des moments différents - lors de l'arrivée de quelque chose qui était prévu, ce qu'il était prévu lorsque l'action a commencé et, enfin, quel a été le temps réel). J'ai donc eu transposée colonnes dans les lignes de la table temporaire et préformée ma requête sur ce, comme d'habitude.
N. B. Pas le one-size-fits-all solution avance!
Cela a pris environ 30 secondes sur l'ensemble source de 630000 lignes et utilisé uniquement à l'index des données, afin de ne pas les chose que de courir dans le processus critique, mais pour des choses comme un temps d'inspection des données ou en fin de journée, le rapport que vous avez peut-être bien (mais vérifier avec vos collègues ou les supérieurs, s'il vous plaît!).
Principal bonus de ce style pour moi était que je pouvais utiliser facilement plus de/moins de colonnes et de changement de regroupement, de filtrage, etc., surtout, une fois que des données ont été copiés sur.
Les données supplémentaires (
columnName
,max
es, ...) ont été pour m'aider dans ma recherche, de sorte que vous ne pourriez pas besoin d'eux; je les ai laissés ici pour peut-être donner des idées :-).