mysql - CASE 表达式中 INT 列上的 ORDER BY 无法正常工作

Question

我有一张看起来像的 table

some_table (
    item_id varchar(10) null,
    item_title varchar(200) null,
    item_url varchar(200) null,
    item_status char default '0' null,
    access_count int null,
    created_by varchar(100) default 'system' not null,
    created_date timestamp default CURRENT_TIMESTAMP not null
)

当我尝试执行以下 SQL 时，结果顺序不正确，例如，91 在 912 之后。

SET @orderCol = 'accessCount';
SELECT
    t.item_status,
    t.item_id,
    t.item_title,
    t.item_url,
    t.access_count
FROM some_table t
WHERE DATE(t.created_date) = DATE(NOW())
ORDER BY CASE
    WHEN (@orderCol = 'itemStatus') THEN t.item_status
    WHEN (@orderCol = 'itemId') THEN t.item_id
    WHEN (@orderCol = 'itemTitle') THEN t.item_title
    WHEN (@orderCol = 'itemUrl') THEN t.item_url
    WHEN (@orderCol = 'accessCount') THEN t.access_count 
END DESC
LIMIT 0, 30

我应该怎么做才能解决这个问题？

Answer 1

您在 CASE expression 中混合了数据类型它在这里说:

The return type of a CASE expression result is the aggregated type of all result values.

在您的示例中，返回类型将是 VARCHAR(200)；能够存储所有可能值的最大数据类型。这解释了为什么您的数字被排序为字符串。更好的解决方案是分别对列进行排序，但如果它们兼容则合并:

ORDER BY
    -- string columns
    CASE
        WHEN (@orderCol = 'itemStatus') THEN t.item_status
        WHEN (@orderCol = 'itemId') THEN t.item_id 
        WHEN (@orderCol = 'itemTitle') THEN t.item_title 
        WHEN (@orderCol = 'itemUrl') THEN t.item_url 
    END,
    -- numeric columns
    CASE
        WHEN (@orderCol = 'accessCount') THEN t.access_count
    END

包含全空值的伪列不会影响排序。