按顺序排序，选择与联合排序困难

Question

在SQL请求中使用关键字UNION有困难，需要按列对结果进行排序，该列没有由SELECT显示，

    DECLARE @search0 varchar(30);

SET @search0 = 'joll'


SELECT c.CdCnd AS n, FORMAT(c.ModifieLe, 'd', 'fr-FR') AS Modifié, c.Nom+' '+c.prenom AS 'Nom Complet', c.TelMobile AS 'Tel. Mob', c.SuiviPar AS 'Suivi par', a.LibAction AS 'à faire', c.Remuneration AS Rémunération, p.LibPrio AS Priorité, c.disponibilite AS Disponibilité, c.MotCleTech AS MCTech, c.MotCleFct AS MCFonc, c.MotCleEnt AS MCEnt, c.Details AS 'Détails', c.DateDispo AS DTDispo, c.Mobilite AS Mobilité
FROM Candidat c
LEFT JOIN TypAction a 
    ON c.CdAction = a.CdAction
LEFT JOIN TypPriorite p 
    ON c.CdPrio = p.CdPrio  
WHERE  (  ( Nom LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR prenom LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleTech LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleFct LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleEnt LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  )  ) 

    UNION 

SELECT c.CdCnd AS n, FORMAT(c.ModifieLe, 'd', 'fr-FR') AS Modifié, c.Nom+' '+c.prenom AS 'Nom Complet', c.TelMobile AS 'Tel. Mob', c.SuiviPar AS 'Suivi par', a.LibAction AS 'à faire', c.Remuneration AS Rémunération, p.LibPrio AS Priorité, c.disponibilite AS Disponibilité, c.MotCleTech AS MCTech, c.MotCleFct AS MCFonc, c.MotCleEnt AS MCEnt, c.Details AS 'Détails', c.DateDispo AS DTDispo, c.Mobilite AS Mobilité
FROM Candidat c
LEFT JOIN TypAction a 
    ON c.CdAction = a.CdAction
LEFT JOIN TypPriorite p 
    ON c.CdPrio = p.CdPrio  
WHERE  ( 1 = 2  OR  c.CdCnd = '3' OR  c.CdCnd = '48' OR  c.CdCnd = '16' )

ORDER BY Modifié DESC

在这里，我需要用DateTime和c.ModifieLe列对我的所有结果进行排序，但是在UNION限制下，我可以使用这个列而不显示它，并且我需要用'fr-FR‘格式化我的日期，

所以，我用"Modifié“来排序我的结果，但是这种类型像varchar类型.

我需要格式化我的DateTime在法语cultur和保持它的日期类型。

谢谢你的帮助。

Answer 1

首先应用UNION，然后对它们排序：

;WITH T AS
(
    SELECT 
        c.CdCnd AS n, 
        c.ModifieLe, 
        c.Nom+' '+c.prenom AS 'Nom Complet', 
        c.TelMobile AS 'Tel. Mob', 
        c.SuiviPar AS 'Suivi par', 
        a.LibAction AS 'à faire', 
        c.Remuneration AS Rémunération, 
        p.LibPrio AS Priorité, 
        c.disponibilite AS Disponibilité, 
        c.MotCleTech AS MCTech, 
        c.MotCleFct AS MCFonc, 
        c.MotCleEnt AS MCEnt, 
        c.Details AS 'Détails', 
        c.DateDispo AS DTDispo, 
        c.Mobilite AS Mobilité
    FROM Candidat c
    LEFT JOIN TypAction a 
        ON c.CdAction = a.CdAction
    LEFT JOIN TypPriorite p 
        ON c.CdPrio = p.CdPrio  
    WHERE  (  ( Nom LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR prenom LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleTech LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleFct LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleEnt LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  )  ) 

    UNION 

    SELECT 
        c.CdCnd AS n, 
        c.ModifieLe, 
        c.Nom+' '+c.prenom AS 'Nom Complet', 
        c.TelMobile AS 'Tel. Mob', 
        c.SuiviPar AS 'Suivi par', 
        a.LibAction AS 'à faire', 
        c.Remuneration AS Rémunération, 
        p.LibPrio AS Priorité, 
        c.disponibilite AS Disponibilité, 
        c.MotCleTech AS MCTech, 
        c.MotCleFct AS MCFonc, 
        c.MotCleEnt AS MCEnt, 
        c.Details AS 'Détails', 
        c.DateDispo AS DTDispo, 
        c.Mobilite AS Mobilité
    FROM Candidat c
    LEFT JOIN TypAction a 
        ON c.CdAction = a.CdAction
    LEFT JOIN TypPriorite p 
        ON c.CdPrio = p.CdPrio  
    WHERE  ( 1 = 2  OR  c.CdCnd = '3' OR  c.CdCnd = '48' OR  c.CdCnd = '16' )
)
SELECT
    [n],    
    FORMAT([ModifieLe], 'd', 'fr-FR') AS Modifié,  
    [Nom Complet], 
    [Tel. Mob], 
    [Suivi par], 
    [à faire], 
    [Rémunération], 
    [Priorité], 
    [Disponibilité], 
    [MCTech], 
    [MCFonc], 
    [MCEnt], 
    [Détails], 
    [DTDispo], 
    [Mobilité]
FROM T ORDER BY CAST(ModifieLe AS DATETIME) DESC

Answer 2

你的命令应该是

ORDER BY CONVERT(DATETIME,Modifié,103) DESC

Answer 3

你可以试着

select * from(
SELECT c.CdCnd AS n, FORMAT(c.ModifieLe, 'd', 'fr-FR') AS Modifié, c.Nom+' '+c.prenom AS 'Nom Complet', c.TelMobile AS 'Tel. Mob', c.SuiviPar AS 'Suivi par', a.LibAction AS 'à faire', c.Remuneration AS Rémunération, p.LibPrio AS Priorité, c.disponibilite AS Disponibilité, c.MotCleTech AS MCTech, c.MotCleFct AS MCFonc, c.MotCleEnt AS MCEnt, c.Details AS 'Détails', c.DateDispo AS DTDispo, c.Mobilite AS Mobilité
FROM Candidat c
LEFT JOIN TypAction a 
    ON c.CdAction = a.CdAction
LEFT JOIN TypPriorite p 
    ON c.CdPrio = p.CdPrio  
WHERE  (  ( Nom LIKE '%%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR prenom LIKE '%%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleTech LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleFct LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  OR MotCleEnt LIKE '%' + @search0 + '%' COLLATE SQL_Latin1_General_Cp437_CI_AI  )  ) 

    UNION 

SELECT c.CdCnd AS n, FORMAT(c.ModifieLe, 'd', 'fr-FR') AS Modifié, c.Nom+' '+c.prenom AS 'Nom Complet', c.TelMobile AS 'Tel. Mob', c.SuiviPar AS 'Suivi par', a.LibAction AS 'à faire', c.Remuneration AS Rémunération, p.LibPrio AS Priorité, c.disponibilite AS Disponibilité, c.MotCleTech AS MCTech, c.MotCleFct AS MCFonc, c.MotCleEnt AS MCEnt, c.Details AS 'Détails', c.DateDispo AS DTDispo, c.Mobilite AS Mobilité
FROM Candidat c
LEFT JOIN TypAction a 
    ON c.CdAction = a.CdAction
LEFT JOIN TypPriorite p 
    ON c.CdPrio = p.CdPrio  
WHERE  ( 1 = 2  OR  c.CdCnd = '3' OR  c.CdCnd = '48' OR  c.CdCnd = '16' )
)
ORDER BY Modifié DESC