Trier par valeur minimale de deux colonnes
j'utilise SQL Server 2008 R2
.
je dois Trier un tableau par la valeur minimale de deux colonnes.
le tableau ressemble à ceci:
ID: integer;
Date1: datetime;
Date2: datetime.
je veux que mes données soient triées par minimum deux dates.
Quelle est la façon la plus simple de trier ce tableau de cette façon?
13 réponses
NOT NULL columns . Vous devez ajouter CASE déclaration dans commande par clause dans le texte suivant:
SELECT Id, Date1, Date2
FROM YourTable
ORDER BY CASE
WHEN Date1 < Date2 THEN Date1
ELSE Date2
END
colonnes annulables . Comme Zohar Peled écrit dans les commentaires si les colonnes sont nulles, vous pouvez utiliser ISNULL
(mais mieux utiliser COALESCE
au lieu de ISNULL
, parce que c'est ANSI SQL standard
) dans le suivant:
SELECT Id, Date1, Date2
FROM YourTable
ORDER BY CASE
WHEN COALESCE(Date1, '1753-01-01') < COALESCE(Date2, '1753-01-01') THEN Date1
ELSE Date2
END
vous pouvez lire à propos de ANSI standard dateformat 1753-01-01
ici .
utiliser un CASE
dans le ORDER BY
:
ORDER BY case when date1 < date2 then date1 else date2 end
la manière la plus simple est d'utiliser le VALUES
mot-clé, comme le suivant:
SELECT ID, Date1, Date2
FROM YourTable
ORDER BY (SELECT MIN(v) FROM (VALUES (Date1), (Date2)) AS value(v))
ce code fonctionnera pour tous les cas, même avec les colonnes nullable .
Edit :
la solution avec le mot-clé COALESCE
n'est pas universelle. Il a les restrictions importantes:
- Cela ne fonctionnera pas si les colonnes sont de la
Date
type (si vous utilisez les dates avant de01/01/1753
) - ça ne marchera pas si l'une des colonnes est
NULL
. Il interprète leNULL
comme valeur minimaledatetime
. Mais est-il réellement vrai? Ce n'est même pasdatetime
, ce n'est rien. - l'expression
IF
sera beaucoup plus compliquée si nous utilisons plus de deux de colonnes.
selon la question:
Quelle est la façon la plus simple de trier ce tableau de cette façon?
la solution la plus courte et la plus simple est celle qui est décrite ci-dessus, parce que:
- il ne faut pas beaucoup de codage pour le mettre en œuvre - il suffit d'ajouter une ligne de plus.
- Vous n'ont pas besoin de pour se soucier si les colonnes sont nulles ou non. Tu utilises juste le code et ça marche.
- vous peut étendre le nombre de colonnes dans votre requête simplement en ajoutant celui après une virgule.
- Il œuvres avec la
Date
des colonnes et que vous n'avez pas besoin de modifier le code.
Edit 2:
Zohar Peled proposa la manière suivante d'Ordonner:
j'ordonnerais les lignes par cette règle: premièrement, quand les deux sont nulles, deuxièmement, quand date1 est null, Troisièmement, quand date 2 est null, quatrièmement, min (date1, date2)
ainsi, pour ce cas, la solution peut être atteinte en utilisant la même approche, comme suit:
SELECT ID, Date1, Date2
FROM YourTable
ORDER BY
CASE WHEN Date1 IS NULL AND Date2 IS NULL THEN 0
WHEN Date1 IS NULL THEN 1
WHEN Date2 IS NULL THEN 2
ELSE 3 END,
(SELECT MIN(v) FROM (VALUES ([Date1]), ([Date2])) AS value(v))
la sortie pour ce code est ci-dessous:
le COALESCE
solution ne sera pas trier la table de cette façon. It mess up les lignes où au moins une cellule de la valeur NULL
. La sortie est la suivante:
Espérons que cette aide et en attendant que les critiques.
il peut s'agir d'une solution alternative qui ne nécessite pas de ramification comme CASE WHEN
. Ceci est basé sur la formule max(a,b)=1/2(a+b+|a−b|)
comme décrit ici . Nous obtenons les valeurs absolues de a et b en utilisant DATEDIFF
avec une date de référence ( '1773-01-01'
).
ORDER BY (DATEDIFF(d,'17730101' ,isnull(Startdate,enddate)) + DATEDIFF(d,'17730101' ,isnull(EndDate,Startdate))
- ABS(DATEDIFF(d,isnull(Startdate,enddate),isnull(EndDate,Startdate))))
Données De Test
Create Table #DateData(ID int Identity, Name varchar(15),Startdate datetime,EndDate DateTime)
Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-17 18:48:27','2015-04-18 18:48:27')
Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-19 18:48:27','2015-04-18 18:48:27')
Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-20 18:48:27','2015-04-18 18:48:27')
Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-11 18:48:27','2015-04-22 18:48:27')
Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-05-09 18:48:27','2015-04-18 18:48:27')
Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-17 19:07:38','2015-04-17 18:55:38')
Insert Into #DateData(Name,Startdate,EndDate) values ('myName','2015-04-17 19:07:38','2015-05-12 18:56:29')
Requête Complète
select *
from #DateData order by (DATEDIFF(d,'17730101' ,isnull(Startdate,enddate)) + DATEDIFF(d,'17730101' ,isnull(EndDate,Startdate))
- ABS(DATEDIFF(d,isnull(Startdate,enddate),isnull(EndDate,Startdate))))
si vous ne voulez pas utiliser Case statement
dans le Order By
, alors c'est une autre approche, il suffit de déplacer le Case statement
à Select
SELECT Id, Date1, Date2 FROM
(SELECT Id, Date1, Date2
,CASE WHEN Date1 < Date2 THEN Date1 ELSE Date2 END as MinDate
FROM YourTable) as T
ORDER BY MinDate
je préfère cette façon de traiter les colonnes nulles:
SELECT Id, Date1, Date2
FROM YourTable
ORDER BY
CASE
WHEN Date1 < Date2 OR Date1 IS NULL THEN Date1
ELSE Date2
END
j'utilise CROSS APPLY
, Je ne suis pas sûr de la performance, mais CROSS APPLY
a souvent une meilleure performance dans mon expérience.
CREATE TABLE #Test (ID INT, Date1 DATETIME, Date2 DATETIME)
INSERT INTO #Test SELECT 1, NULL, '1/1/1';INSERT INTO #Test SELECT 2, NULL, NULL;INSERT INTO #Test SELECT 3, '2/2/2', '3/3/1';INSERT INTO #Test SELECT 4, '3/3/3', '11/1/1'
SELECT t.ID, Date1, Date2, MinDate
FROM #TEST t
CROSS APPLY (SELECT MIN(d) MinDate FROM (VALUES (Date1), (Date2)) AS a(d)) md
ORDER BY MinDate
DROP TABLE #Test
je changerais la mise au point de comment pour faire ceci à pourquoi vous avez besoin de ceci - et proposez de changer le schéma à la place. La règle de base est la suivante: si vous avez besoin de tirer des cascades pour accéder à vos données, il ya une mauvaise décision de conception.
comme vous l'avez vu, cette tâche est très atypique pour SQL donc, bien que ce soit possible, toutes les méthodes proposées sont douloureusement lentes par rapport à un ORDER BY
ordinaire .
- si vous devez le faire souvent, alors le minimum des deux dates doit avoir une signification physique indépendante pour votre application.
- qui justifie une colonne séparée (ou peut - être une colonne remplaçant l'une des deux) - maintenue par un déclencheur ou même manuellement si le sens est suffisamment indépendant pour que la colonne ne soit éventuellement ni l'un ni l'autre dans certains cas.
je pense que quand vous voulez trier sur les deux champs de date1
et date2
, vous devriez avoir les deux dans la partie ORDER BY
, comme ceci:
SELECT *
FROM aTable
ORDER BY
CASE WHEN date1 < date2 THEN date1
ELSE date2 END,
CASE WHEN date1 < date2 THEN date2
ELSE date1 END
résultat peut être comme ceci:
date1 | date2
-----------+------------
2015-04-25 | 2015-04-21
2015-04-26 | 2015-04-21
2015-04-25 | 2015-04-22
2015-04-22 | 2015-04-26
D'avoir un préfet résultat avec Null
valeurs d'usage:
SELECT *
FROM aTable
ORDER BY
CASE
WHEN date1 IS NULL THEN NULL
WHEN date1 < date2 THEN date1
ELSE date2 END
,CASE
WHEN date2 IS NULL THEN date1
WHEN date1 IS NULL THEN date2
WHEN date1 < date2 THEN date2
ELSE date1 END
résultats seront comme ceci:
date1 | date2
-----------+------------
NULL | NULL
NULL | 2015-04-22
2015-04-26 | NULL
2015-04-25 | 2015-04-21
2015-04-26 | 2015-04-21
2015-04-25 | 2015-04-22
il y a une autre option. Vous pouvez calculer la colonne de résultat par la logique nécessaire et couvrir le select par externe un avec la commande par votre colonne. Dans ce cas, le code sera le suivant:
select ID, x.Date1, x.Date2
from
(
select
ID,
Date1,
Date2,
SortColumn = case when Date1 < Date2 then Date1 else Date2 end
from YourTable
) x
order by x.SortColumn
l'avantage de cette solution est que vous pouvez ajouter les requêtes de filtrage nécessaires (dans la sélection intérieure) et les index seront quand même utiles.
j'ordonnerais les lignes par cette règle:
- quand les deux null
- quand date1 est null
- quand la date 2 est nulle
- min (date1, date2)
pour ce cas emboîté sera simple et efficace (à moins que la table est très grande) selon ce post .
SELECT ID, Date1, Date2
FROM YourTable
ORDER BY
CASE
WHEN Date1 IS NULL AND Date2 IS NULL THEN 0
WHEN Date1 IS NULL THEN 1
WHEN Date2 IS NULL THEN 2
ELSE 3 END,
CASE
WHEN Date1 < Date2 THEN Date1
ELSE Date2
END
vous pouvez utiliser min
fonction dans order by
clause:
select *
from [table] d
order by ( select min(q.t) from (
select d.date1 t union select d.date2) q
)
vous pouvez aussi utiliser case
dans la clause order by
mais comme vous le savez le résultat de la comparaison ( >
et <
) toute valeur (nulle ou nulle nulle nulle) avec null n'est pas true
même si vous avez placé ansi_nulls
à off
. donc, pour garantir le type que vous vouliez, vous avez besoin de gérer null
s, comme vous le savez dans case
clause si le résultat d'un when
est true
puis plus loin when
les déclarations ne sont pas évaluées de sorte que vous pouvez dire:
select * from [table]
order by case
when date1 is null then date2
when date2 is null then date1
when date1<date2 then date1 -- surely date1 and date2 are not null here
else date2
end
aussi voici quelques autres solutions si votre scénario est différent peut-être que vous évaluez le résultat de la comparaison de plusieurs colonnes(ou un calcul) à l'intérieur d'un champ séparé et finalement commander par ce champ calculé sans utiliser aucune condition à l'intérieur de votre commande par clause.
SELECT ID, Date1, Date2
FROM YourTable
ORDER BY (SELECT TOP(1) v FROM (VALUES (Date1), (Date2)) AS value(v) ORDER BY v)
très similaire à la réponse de @dyatchenko, mais sans problème