时间戳

首页 > 解决方案 > 查询显示不正确的排序

sql - 查询显示不正确的排序

问题描述

所需的输出作为图像附加。我目前拥有的代码正确显示了到达日期和离开日期，但显示的客人姓名与图像不完全相同。图像以随机顺序显示名字，而姓氏的顺序正确。

SELECT UPPER(substring(GLASTNAME,1,1)) + LOWER(SUBSTRING(GLASTNAME,2,len(GLASTNAME)-1)) + ', '+ 
        UPPER(substring(GFIRSTNAME,1,1)) + LOWER(SUBSTRING(GFIRSTNAME,2,len(GFIRSTNAME)-1)) AS Guest,

        FORMAT(ARRIVAL_DATE, 'dddd MM/dd/yy') AS [Arrival Date],
        FORMAT(DateADD(d,NUMDAYS, ARRIVAL_DATE),'dddd MM/dd/yy') AS [Departure Date]
FROM guestWH g INNER JOIN reservationWH r ON g.RNUM = r.RNUM
    INNER JOIN packageWH p ON r.P_ID = p.P_ID 
WHERE NUMDAYS = 5 OR NUMDAYS = 6 OR NUMDAYS = 7
ORDER BY [Arrival Date] ASC

正确答案

我当前的输出如下所示：https ://imgur.com/i3wqvQM

标签： sqlsql-server

解决方案

您需要按客人订购，然后是 [Arrival Date] - 所以您可以在下面尝试使用1 which means it will order by first column which is guestname in your case and then [Arrival Date]

SELECT UPPER(substring(GLASTNAME,1,1)) + LOWER(SUBSTRING(GLASTNAME,2,len(GLASTNAME)-1)) + ', '+ 
        UPPER(substring(GFIRSTNAME,1,1)) + LOWER(SUBSTRING(GFIRSTNAME,2,len(GFIRSTNAME)-1)) AS Guest,

        FORMAT(ARRIVAL_DATE, 'dddd MM/dd/yy') AS [Arrival Date],
        FORMAT(DateADD(d,NUMDAYS, ARRIVAL_DATE),'dddd MM/dd/yy') AS [Departure Date]
FROM guestWH g INNER JOIN reservationWH r ON g.RNUM = r.RNUM
    INNER JOIN packageWH p ON r.P_ID = p.P_ID 
WHERE NUMDAYS = 5 OR NUMDAYS = 6 OR NUMDAYS = 7
ORDER BY 1, [Arrival Date] ASC

推荐阅读