MySQL-ning tartiblashi noaniq

Foydalanuvchilar tanlagan ustunga ko'ra, saralangan (kamayib boruvchi yoki kamayib borayotgan) ma'lumotlarni qaytaradigan o'zida saqlab turadigan bir amalni yozdim. Bunga erishish uchun quyidagi Case iboralar buyrug'ida quyidagi kod snippet

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END,
    CASE    WHEN p_filter_type = 'DESC' THEN
                CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END DESC

Ushbu koddagi muammolar, agar mavjud bo'lsa, ma'lumotlarni to'g'ri tartibga solmasligidir   tamsaytli ma'lumotlar turi bilan ustun tanlangan.   U tamsayı qiymatini varchar deb hisoblaydi va ma'lumotlarni mos ravishda tartiblaydi

Misol uchun, Qaytadi

10
1
2
3

Agar requestId buyrug'i ASCENDING tartibida tanlangan bo'lsa.

VARCHAR qiymatini butun songa aylantirish uchun ABS() ni ishlatish kabi muammoni hal qilish uchun ikkita echimdan foydalanishga harakat qildim, lekin ular muammoni hal qilishga yordam bermaydi.

Agar siz menga bu masalani hal qilishda yordam beradigan bo'lsangiz, men sizga juda minnatdorman

2
Bajarilgan barcha varchar maydonlar faqat raqamlardan iboratmi?
qo'shib qo'ydi muallif trincot, manba
@RafalZiolkowski, o'shanda OP yozgan va qilgan.
qo'shib qo'ydi muallif trincot, manba
Buning bir usuli tayyorlangan .
qo'shib qo'ydi muallif Paul Spiegel, manba
Har bir narsani belgilar majmuasi sifatida aylantirganga o'xshaydi, siz int ga aylantirishni majburlashingiz mumkin
qo'shib qo'ydi muallif Rafal Ziolkowski, manba
@RafalZiolkowski Men ham buni qildim, lekin ishlamayapman
qo'shib qo'ydi muallif Dhananjay Sakhare, manba
@PaulSpiegel Men tayyorlangan so'zlar bilan uni sinab ko'raman ..
qo'shib qo'ydi muallif Dhananjay Sakhare, manba

6 javoblar

Haqiqatan ham, siz turli xil ma'lumotlar turlarini birlashtirganda, p_filter_type va p_filter_column bo'lsa ham, faqat bitta son ustun ekanligini bildirgan bo'lsa, ibora varchar bu ifodaning natijasini aniqlash kerak.

Buni hal qilishning bir usuli, null ni ishlab chiqarish uchun har bir imkoniyat uchun tartibini ifodasi bilan yaratish. Shunday ifodalarning har biri o'z ma'lumot turiga yopishishi mumkin:

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'projectCode' 
        THEN projectcode END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'visaType' 
        THEN visaType END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'approveRejectStatus' 
        THEN status END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'createdDate'
        THEN createdDate END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'employeeID'
        THEN employeeID END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'requestId'
        THEN requestId END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'country'
        THEN country END ASC,
    CASE WHEN p_filter_type = 'ASC' AND p_filter_column = 'serviceDesk'
        THEN serviceDesk END ASC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'projectCode' 
        THEN projectcode END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'visaType' 
        THEN visaType END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'approveRejectStatus' 
        THEN status END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'createdDate'
        THEN createdDate END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'employeeID'
        THEN employeeID END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'requestId'
        THEN requestId END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'country'
        THEN country END DESC,
    CASE WHEN p_filter_type = 'DESC' AND p_filter_column = 'serviceDesk'
        THEN serviceDesk END DESC

Masalan, p_filter_type = 'DESC' va p_filter_column = "employeeID" bo'lsa, unda yuqoridagi ORDER BY

ORDER BY 
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL ASC,
    NULL DESC,
    NULL DESC,
    NULL DESC,
    NULL DESC,
    employeeID DESC,
    NULL DESC,
    NULL DESC,
    NULL DESC

bu siz yozganidek, xuddi shunday buyurtma berish kerak:

ORDER BY 
    employeeID DESC

Gibrid eritma

Siz, albatta, bir xil ma'lumotlar turiga ega bo'lgan maydonlarni bir ifodada birlashtirgan joylarda ba'zi ma'lumotlarning o'zaro almashinuvi uchun borishingiz mumkin. Buning uchun varchar maydonlari uchun int maydonlarining biri uchun tartibidagi maydonlari va keyin esa, har bir kishi uchun, takroriy vaziyat uchun takrorlangan:

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column -- all varchar fields
            WHEN 'projectCode' THEN projectCode
            WHEN 'visaType' THEN visaType
            WHEN 'approveRejectStatus' THEN status
            WHEN 'country' THEN country
            WHEN 'serviceDesk' THEN serviceDesk
        END
    END ASC,
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column -- all int fields 
            WHEN 'employeeID' THEN employeeId
            WHEN 'requestId' THEN requestId
        END
    END ASC,
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column -- all date fields
            WHEN 'createdDate' THEN createdDate
        END
    END ASC,
    CASE WHEN p_filter_type = 'DESC' THEN
        CASE p_filter_column -- all varchar fields
            WHEN 'projectCode' THEN projectCode
            WHEN 'visaType' THEN visaType
            WHEN 'approveRejectStatus' THEN status
            WHEN 'country' THEN country
            WHEN 'serviceDesk' THEN serviceDesk
        END
    END DESC,
    CASE WHEN p_filter_type = 'DESC' THEN
        CASE p_filter_column -- all int fields 
            WHEN 'employeeID' THEN employeeId
            WHEN 'requestId' THEN requestId
        END
    END DESC,
    CASE WHEN p_filter_type = 'DESC' THEN
        CASE p_filter_column -- all date fields
            WHEN 'createdDate' THEN createdDate
        END
    END DESC
2
qo'shib qo'ydi
date ma'lumot turi uchun alohida ifodani saqlab qolmoqchi bo'lgan, ammo int bilan birlashtirilishi mumkin bo'lgan javobni faqat shunga o'xshash echimni qo'shgan edim. CASE iborasidan THEN/ELSE so'zlari int bo'lsa, +0 .
qo'shib qo'ydi muallif trincot, manba
Menimcha, faqat ikki guruhga ehtiyoj bor. Birinchidan, soni turlar uchun (kodni qayta ishlaydigan iborani +0 bilan o'zgartiring) va keyin barcha boshqa turdagi.
qo'shib qo'ydi muallif Paul Spiegel, manba
@trincot Bu juda yaxshi ishlaydi :) Bir tonna uchun rahmat!
qo'shib qo'ydi muallif Dhananjay Sakhare, manba

Qanday qilib ASC buyurtmasini birinchi ish uchun qo'shish kerak?

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END ASC,
    CASE    WHEN p_filter_type = 'DESC' THEN
                CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END DESC

u ishlaydi? Bundan tashqari, noto'g'ri tartiblash misolida ishlatilgan sana Varchar ustunida emasligiga ishonchingiz komilmi? Bu xuddi shunga o'xshash.

0
qo'shib qo'ydi
Men unga ASC qo'shishni sinab ko'rdim. Biroq, ASC buyrug'i bilan buyurtmalar bo'yicha bir xil chiqdi va sql-ni beradi
qo'shib qo'ydi muallif Dhananjay Sakhare, manba

Case atom qiymatini qaytaradi, qaytarilgan ma'lumotlar turi faqat bitta bo'lishi mumkin. Turli xil dasturlarda boshqacha ma'lumot topilmadi. Bu holatda, hamma narsa to'liq ravishda varcharga aylanadi. Kerakli buyurtmani olish uchun tamsaytlar buyurtma qoidalariga ko'ra buyurtma berishiga ishonch hosil qilishingiz kerak. Shu kabi:

CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN RIGHT(CONCAT('00', employeeId), 2) -- int field
            WHEN 'requestId' THEN RIGHT(CONCAT('00', requestId), 2) -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field
        END
END

Buyurtma uchun bu qiymatlarni 01, 02, 03, 10 ga o'zgartiradi.

Xurmatli buyurtma berishga to'g'ri kelmasligiga amin emasman, bu mahalliy sozlamalaringizga bog'liq.

0
qo'shib qo'ydi
ORDER BY 
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column 
            WHEN 'projectCode' THEN CAST(projectCode AS INT) -- varchar field
            WHEN 'visaType' THEN CAST(visaType AS INT) -- varchar field
            WHEN 'approveRejectStatus' THEN CAST(status AS INT) -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN CAST(country AS INT) -- varchar field
            WHEN 'serviceDesk' THEN CAST(serviceDesk AS INT) -- varchar field

    END
    END,
    CASE    WHEN p_filter_type = 'DESC' THEN
                CASE p_filter_column 
           WHEN 'projectCode' THEN CAST(projectCode AS INT) -- varchar field
            WHEN 'visaType' THEN CAST(visaType AS INT) -- varchar field
            WHEN 'approveRejectStatus' THEN CAST(status AS INT) -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId -- int field
            WHEN 'country' THEN CAST(country AS INT) -- varchar field
            WHEN 'serviceDesk' THEN CAST(serviceDesk AS INT) -- varchar field

    END
    END DESC

Varchar turi bilan ustuningiz raqamlarni o'z ichiga olgan va siz ushbu maydonchani raqamga muvofiq tartiblashtirishni istasangiz, yuqoridagi kodni sinab ko'rganimdagidek buyurtma berish paytida VARCHAR maydonini INT ga qo'shishingiz kerak. Umid qilamanki, bu sizga yordam beradi.

0
qo'shib qo'ydi
@SagarGangwal Men bilaman, lekin bu yordam bermaydi, u int kodini int ga o'zgartiradi, keyin esa varchar . Javob uchun javobni qarang.
qo'shib qo'ydi muallif HoneyBadger, manba
@SagarGangwal, lekin u allaqachon int ustuni
qo'shib qo'ydi muallif HoneyBadger, manba
ORDERING kodini CASE WHEN yordamida foydalanib, VARCHAR maydonini avtomatik ravishda o'zgartiradi.
qo'shib qo'ydi muallif Sagar Gangwal, manba
Shunday qilib, ushbu sütunla faqat WHEN 'requestId' CAST (requestId AS INT) - int olgan bilan harakat qilib ko'rishingiz mumkin. Muammoingizni hal qiladigan bo'lishi mumkin.
qo'shib qo'ydi muallif Sagar Gangwal, manba
Yo'q, varchar joylari alfanumerik qiymatlarni o'z ichiga oladi, masalan. projectCode "E,00031234" kabi bir narsani o'z ichiga oladi, uni tasdiqlash bekor qilingan, tasdiqlangan, rad etilgan va boshqalarni o'z ichiga oladi.
qo'shib qo'ydi muallif Dhananjay Sakhare, manba

Agar faqat varchar qiymatini butun songa aylantirishni istasangiz, bu qiymatlar raqamlar deb faraz qilsangiz, MySQL buni oddiygina bajarishi mumkin, bu ustunni 0 ga o'xshash qilib qo'ying:

ORDER BY 
    CASE WHEN p_filter_type = 'ASC' THEN
        CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId + 0 -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END,
    CASE    WHEN p_filter_type = 'DESC' THEN
                CASE p_filter_column 
            WHEN 'projectCode' THEN projectCode -- varchar field
            WHEN 'visaType' THEN visaType -- varchar field
            WHEN 'approveRejectStatus' THEN `status` -- varchar field
            WHEN 'createdDate' THEN createdDate
            WHEN 'employeeID' THEN employeeId -- int field
            WHEN 'requestId' THEN requestId + 0 -- int field
            WHEN 'country' THEN country -- varchar field
            WHEN 'serviceDesk' THEN serviceDesk -- varchar field

    END
    END DESC

sqlfiddle da oddiy demo ni ko'ring.

0
qo'shib qo'ydi
@DananjaySakhare Demo-ni ko'ring va sizga ba'zi bir misollarni oching.
qo'shib qo'ydi muallif Blank, manba
Agar requestId bir INT requestId + 0 bo'lsa, hech narsa o'zgarmaydi.
qo'shib qo'ydi muallif Paul Spiegel, manba
@Forward buni sinab ko'rdim, lekin afsuski, u ishlamayapti!
qo'shib qo'ydi muallif Dhananjay Sakhare, manba

CONCAT funktsiyasidan foydalangan holda tayyorlangan ariza yaratishingiz mumkin:

SET @sql = CONCAT('select * from some_table order by ', p_filter_column, ' ', p_filter_type);
PREPARE stmt FROM @sql;
EXECUTE stmt;

Bonus sifatida, tayyorlangan bayonot indekslardan (belgilangan bo'lsa) foydalanishlari mumkin.

Biroq, siz avval kirishni tasdiqlashingiz kerak bo'lishi mumkin.

Yana bir vaqtinchalik echim LPADni soni joylar uchun ishlatish va raqamni nol bilan to'ldirish mumkin:

WHEN 'employeeID' THEN LPAD(employeeId, 20, '0') -- int field

Biroq, faqat UNSIGNED turlari uchun ishlaydi.

0
qo'shib qo'ydi