时间戳

首页 > 解决方案 > 选择的排序结果

sql - 选择的排序结果

问题描述

我有以下选择：

SELECT parameter, value
FROM
    (
        SELECT
           parameter,
            value
        FROM
            table r1
        WHERE
            r1.parameter IN (
                'Room meal',
                'Snacks between meals',
                'Reduction',
                'Mornings',
                'Noons',
                'Evenings',
                'Remarks'
            )
            AND r1.id= 1122632
                AND r1.wert IS NOT NULL
            AND r1.date= (
                SELECT
                    MAX(date)
                FROM
                    table r2
                WHERE
                    r2.parameter = r1.parameter
                    AND r2.id= r1.id
            ) 
        UNION ALL
        SELECT
            parameter,
            wert
        FROM
            table r1
        WHERE
            r1.parameter = 'Status'
                AND r1.id= 1122632
                AND r1.wert IS NOT NULL
    ) l
ORDER BY decode(l.parameter, 'Room meal', 1, 'Snacks between meals', 2, 'Reduction', 3, 'Mornings',4, 'Noons',5, 'Evenings',6, 'Remarks',7, 'Status', 8) asc;

我期望选择的结果是这样的：

Parameter               Value
_________|_____________|________
Room meal              |Yes
Snacks between meals   |Yes
Reduction              |1500 kcal
Mornings               |Cookies
Noons                  |Cereals
Evenings               |Soup
Status                 |Adapted whole food
Status                 |Vegetarian food

但取而代之的是这种格式：

Parameter               Value
_________|_____________|________
Room meal              |Yes
Snacks between meals   |Yes
Reduction              |1500 kcal
Noons                  |Cereals
Evenings               |Soup
Mornings               |Cookies
Status                 |Adapted whole food
Status                 |Vegetarian food

你能帮我解决吗？

标签： sqloracleoracle-sqldeveloper

解决方案

检查解码结果：

SELECT parameter,
       value,
       decode(l.parameter, 'Room meal', 1, 'Snacks between meals', 2, 'Reduction', 3, 'Mornings',4, 'Noons',5, 'Evenings',6, 'Remarks',7, 'Status', 8)
FROM ...

因此，您可以看到 decode 表达式的结果以及问题的原因。

推荐阅读