sql - 如何使用确切的年数间隔？

Question

我需要以这种方式格式化以获得年数、月数及其组合或“无保修”，具体取决于给定产品是否有保修。我尝试使用 case + extract 但它没有用

    select p.product_name, sum(o.quantity) as total_quantity,

    case when p.warranty_period = interval '0-0' year to month then 'No 
    warranty'

    when p.warranty_period < interval '0-11' year to month then 
    extract(month from p.warranty_period) || ' months'

    when p.warranty_period < interval '21-0' year to month then -- make it 
    for all years, not only 1
    extract(year from p.warranty_period) || ' years'

    when p.warranty_period > interval '0-0' year to month then 
    extract(year from p.warranty_period) || ' years and ' || extract(month 
    from p.warranty_period)
    || ' months'

    end WARRANTY from PRODUCT_INFORMATION p
    join order_items o on p.product_id = o.product_id
    group by p.product_name, p.warranty_period;

我希望得到一个包含 4 个变量的列 WARRANTY：

没有保修

• 年
• 月
• 年月

标签： sqloracle

Answer 1

您可以使用以下内容：

-- Data preparation
create table product (warranty_period INTERVAL YEAR TO MONTH);
insert into product values(INTERVAL '2-0' YEAR TO MONTH)
insert into product values(INTERVAL '1-5' YEAR TO MONTH)
insert into product values(INTERVAL '0-7' YEAR TO MONTH)
insert into product values(INTERVAL '0-0' YEAR TO MONTH)

--

-- Data in Table
select * from product

--

-- Your query
SELECT
    WARRANTY_PERIOD,
    CASE
        WHEN WARRANTY_PERIOD > INTERVAL '1-0' YEAR TO MONTH 
        THEN EXTRACT(YEAR FROM WARRANTY_PERIOD)
             || ' years '
             || CASE
                WHEN EXTRACT(MONTH FROM WARRANTY_PERIOD) <> 0 
                THEN EXTRACT(MONTH FROM WARRANTY_PERIOD)
                     || ' month'
                END
        WHEN WARRANTY_PERIOD > INTERVAL '0-1' YEAR TO MONTH 
        THEN EXTRACT(MONTH FROM WARRANTY_PERIOD)
             || ' month'
        ELSE 'No warranty'
    END AS WARRANTY
FROM
    PRODUCT

输出

--

Db 小提琴演示

干杯！！