order by子句desc，混淆了年份和月份

Question

我需要按降序排序保修，但它首先给我月份，因为数字较大，然后是底部的年份，因为年份的数字是1。

  SELECT product_name_us product_name_us,
         product_desc_us product_desc_us,
         product_name_ru product_name_ru,
         product_desc_ru product_desc_ru,
         "Warranty",
         in_stock in_stock
    FROM (  SELECT DISTINCT
                   CASE
                      WHEN pd.language_id = 'US'
                      THEN
                         COALESCE (TO_CHAR (pd.translated_name), ' ')
                   END
                      AS product_name_us,
                   CASE
                      WHEN pd.language_id = 'US'
                      THEN
                         CONCAT (SUBSTR (pd.translated_description, 1, 30),
                                 '...')
                   END
                      AS product_desc_us,
                   CASE
                      WHEN pd.language_id = 'RU'
                      THEN
                         COALESCE (TO_CHAR (pd.translated_name), ' ')
                   END
                      AS product_name_ru,
                   CASE
                      WHEN pd.language_id = 'RU'
                      THEN
                         CONCAT (SUBSTR (pd.translated_description, 1, 30),
                                 '...')
                   END
                      AS product_desc_ru,
                   CASE
                      WHEN pin.warranty_period = '00-00'
                      THEN
                         'No Warranty'
                      WHEN EXTRACT (MONTH FROM pin.warranty_period) = '00'
                      THEN
                         EXTRACT (YEAR FROM pin.warranty_period) || ' years'
                      WHEN EXTRACT (YEAR FROM pin.warranty_period) = '00'
                      THEN
                         EXTRACT (MONTH FROM pin.warranty_period) || ' months'
                      WHEN     EXTRACT (MONTH FROM pin.warranty_period) <> '00'
                           AND EXTRACT (YEAR FROM pin.warranty_period) <> '00'
                      THEN
                            EXTRACT (YEAR FROM pin.warranty_period)
                         || ' years and '
                         || EXTRACT (MONTH FROM pin.warranty_period)
                         || ' months'
                      ELSE
                         'No Warranty'
                   END
                      AS "Warranty",
                   SUM (inv.quantity_on_hand) AS in_stock
              FROM product_descriptions pd
                   INNER JOIN product_information pin
                      ON pd.product_id = pin.product_id
                   INNER JOIN inventories inv ON inv.product_id = pin.product_id
          GROUP BY pd.translated_name,
                   CASE
                      WHEN pd.language_id = 'US'
                      THEN
                         COALESCE (TO_CHAR (pd.translated_name), ' ')
                   END,
                   CASE
                      WHEN pd.language_id = 'US'
                      THEN
                         CONCAT (SUBSTR (pd.translated_description, 1, 30),
                                 '...')
                   END,
                   CASE
                      WHEN pd.language_id = 'RU'
                      THEN
                         COALESCE (TO_CHAR (pd.translated_name), ' ')
                   END,
                   CASE
                      WHEN pd.language_id = 'RU'
                      THEN
                         CONCAT (SUBSTR (pd.translated_description, 1, 30),
                                 '...')
                   END,
                   CASE
                      WHEN pin.warranty_period = '00-00'
                      THEN
                         'No Warranty'
                      WHEN EXTRACT (MONTH FROM pin.warranty_period) = '00'
                      THEN
                         EXTRACT (YEAR FROM pin.warranty_period) || ' years'
                      WHEN EXTRACT (YEAR FROM pin.warranty_period) = '00'
                      THEN
                         EXTRACT (MONTH FROM pin.warranty_period) || ' months'
                      WHEN     EXTRACT (MONTH FROM pin.warranty_period) <> '00'
                           AND EXTRACT (YEAR FROM pin.warranty_period) <> '00'
                      THEN
                            EXTRACT (YEAR FROM pin.warranty_period)
                         || ' years and '
                         || EXTRACT (MONTH FROM pin.warranty_period)
                         || ' months'
                      ELSE
                         'No Warranty'
                   END)
   WHERE     (   product_name_us LIKE '%Monitor%'
              OR product_name_ru LIKE '%Монитор%')
         AND in_stock < 1000
ORDER BY "Warranty" DESC, product_name_us ASC;

Answer 1

您必须对ORDER BY子句进行一些编码。这里有一个你可以尝试做到这一点的例子：

SQL> with test (warranty) as
  2    (select 'No Warranty' from dual union all
  3     select '13 years'    from dual union all
  4     select '8 months'    from dual union all
  5     select '2 years and 4 months' from dual
  6    )
  7  select warranty
  8  from test
  9  order by case when warranty = 'No Warranty' then 1 end,
 10           case when instr(warranty, 'years') = 0 then 1 else 2 end,
 11           to_number(regexp_substr(warranty, '\d+', 1, 1)),
 12           to_number(regexp_substr(warranty, '\d+', 1, 2))
 13  /

WARRANTY
--------------------
No Warranty
8 months
2 years and 4 months
13 years

SQL>

• 第9行：“无保修”排在第一位
• 第10行:如果保修不包含“年”，则应该放在第一位(即那些只是几个月)
• 第11行:取" warranty“(即年)中的第一个数字
• 第12行:取"warranty”(即月)

<>F210中的第二个数字