postgresql - 基于多列对记录进行排序

Question

我有一个带有以下列的经销商表

dealer_name    is_iso    is_gst    is_approved

& 需要按以下顺序显示经销商记录

dealers with all columns set should appear first,
then dealers with iso & gst,
then with iso & verified,
then with gst & verified,
then with iso,
then with gst

经销商表中的数据就像

dealer_name     is_iso  is_gst  Is_approved
A               1       1       1
B               1       0       1
C               1       0       0
D               0       1       0
E               1       1       0
F               0       1       1
G               1       0       0
G               1       1       1

目前，我正在使用 CASE WHEN（下面的答案）来实现这一点并且需要知道是否有更好的方法？

标签： postgresqlsql-order-by

Answer 1

SELECT
    *,
    CASE  
        WHEN (typeA = 1 and typeB = 1 and isISO = 1)  THEN 6
        WHEN (typeA = 1 and typeB = 1 and isISO = 0)  THEN 5
        WHEN (typeA = 1 and typeB = 0 and isISO = 1)  THEN 4
        WHEN (typeA = 0 and typeB = 1 and isISO = 1)  THEN 3
        WHEN (typeA = 1 and typeB = 0 and isISO = 0)  THEN 2
        WHEN (typeA = 0 and typeB = 1 and isISO = 0)  THEN 1
        ELSE 0
    END as dealer_order
FROM
    dealers 
ORDER BY
    dealer_order desc