내 MariaDB SQL 쿼리가 INT를 VARCHAR로 정렬하는 이유는 무엇입니까?

내 MariaDB SQL 쿼리가 INT를 VARCHAR로 정렬하는 이유는 무엇입니까?

INT 열로 결과를 주문할 때 VARCHAR인 것처럼 주문한다는 점을 제외하고 잘 작동하는 다소 복잡한 쿼리가 있습니다.

열 자체가 INT임에도 불구하고 INT에 열(id)을 순서대로 캐스팅해보기도 했지만 여전히 잘못된 주문입니다.

SELECT 
`employees`.`id` AS id,
`employees`.`FirstName` AS `FirstName`,
`employees`.`LastName` AS `LastName`,
`employees`.`Email` AS `Email`,
`employees`.`UserName` AS `UserName`,
`employees`.`BadgeNumber` AS `BadgeNumber`,
`employees`.`EmpEmergencyNum` AS `EmpEmergencyNum`,
`departments`.`DeptName` AS `DeptName`,
`emptypes`.`EmpType` AS `EmpType`,
`jobtitles`.`JobTitle` AS `JobTitle`,
`listcarriers`.`CarrierName` AS `CarrierName`,
`paprofiles`.`PAProfile` AS `PAProfile`,
`empstatuses`.`Status` AS `Status`,
concat(`employees`.`LastName`,', ',`employees`.`FirstName`) AS `EmpName`
FROM `employees`

LEFT JOIN `departments` ON
    (`employees`.`DeptID` = `departments`.`id`)
LEFT JOIN `emptypes` ON
    (`employees`.`EmpTypeID` = `emptypes`.`id`)
LEFT JOIN `jobtitles` ON
    (`employees`.`JobTitleID` = `jobtitles`.`id`)
LEFT JOIN `listcarriers` ON
    (`employees`.`EmpCarrierID` = `listcarriers`.`id`)
LEFT JOIN `empstatuses` ON
    (`employees`.`EmpStatusID` = `empstatuses`.`id`)
LEFT JOIN `paprofiles` ON
    (`employees`.`PAProfileID` = `paprofiles`.`id`)

WHERE ((`employees`.`FirstName` LIKE CONCAT('%', _empname, '%') OR `employees`.`LastName` LIKE CONCAT('%', _empname, '%'))
    AND `email` LIKE CONCAT('%', _email, '%')
    AND `username` LIKE CONCAT('%', _username, '%')
    AND `badgenumber` LIKE CONCAT('%', _badgenumber, '%')
    AND `employees`.`DeptID` = IF(_DeptID = 0, `employees`.`DeptID`, _DeptID ) 
    AND `employees`.`EmpTypeID` = IF(_EmpTypeID = 0, `employees`.`EmpTypeID`, _EmpTypeID)
    AND `employees`.`JobTitleID` = IF(_JobTitleID = 0, `employees`.`JobTitleID`, _JobTitleID)
    AND `employees`.`EmpStatusID` = IF(_EmpStatusID = 0, `employees`.`EmpStatusID`, _EmpStatusID)
    AND `employees`.`PAProfileID` = IF(_PAProfileID = 0, `employees`.`PAProfileID`, _PAProfileID))

ORDER BY 
        CASE
        WHEN _order='DESC' THEN CASE
        WHEN _sort='id' THEN CAST(`employees`.id AS int)
            WHEN _sort='LastName' THEN LastName
            WHEN _sort='FirstName' THEN FirstName
            WHEN _sort='Email' THEN Email
            WHEN _sort='UserName' THEN UserName
            WHEN _sort='BadgeNumber' THEN BadgeNumber
            WHEN _sort='EmpEmergencyNum' THEN EmpEmergencyNum
            WHEN _sort='DeptName' THEN DeptName
            WHEN _sort='EmpType' THEN EmpType
            WHEN _sort='JobTitle' THEN JobTitle
            WHEN _sort='CarrierName' THEN CarrierName
            WHEN _sort='PAProfile' THEN PAProfile
            WHEN _sort='Status' THEN `Status`
            END
        END DESC,
        CASE
        WHEN _order='ASC' THEN CASE
        WHEN _sort='id' THEN CAST(`employees`.id AS int)
            WHEN _sort='LastName' THEN LastName
            WHEN _sort='FirstName' THEN FirstName
            WHEN _sort='Email' THEN Email
            WHEN _sort='UserName' THEN UserName
            WHEN _sort='BadgeNumber' THEN BadgeNumber
            WHEN _sort='EmpEmergencyNum' THEN EmpEmergencyNum
            WHEN _sort='DeptName' THEN DeptName
            WHEN _sort='EmpType' THEN EmpType
            WHEN _sort='JobTitle' THEN JobTitle
            WHEN _sort='CarrierName' THEN CarrierName
            WHEN _sort='PAProfile' THEN PAProfile
            WHEN _sort='Status' THEN `Status`
            END
        END ASC
LIMIT _start, _count;
END

예상 11, 12, 13, 20, 21, 101, 102, 201 실제 101, 102, 11, 12, 13, 20, 201, 21

나는 이것 때문에 정말 어리둥절합니다.

단 하나도 사용 안 함case이 경우의 표현. Acaseexpression은 단일 유형의 단일 값을 반환합니다.대신 별도로 사용case식:

ORDER BY (CASE WHEN _order = 'DESC' AND _sort = 'id' THEN employees.id END) DESC,
         (CASE WHEN _order = 'DESC' AND _sort = 'LastName' THEN LastName END) DESC,
         . . .,
         (CASE WHEN _order = 'ASC' AND _sort = 'id' THEN employees.id END) ASC,
         (CASE WHEN _order = 'ASC' AND _sort = 'LastName' THEN LastName END) ASC,
         . . .,

언급URL : https://stackoverflow.com/questions/58768235/why-is-my-mariadb-sql-query-sorting-int-as-varchar