sql - 如何使用 IF 条件简化 SQL 代码。 (PostgreSQL)

Question

我想在不改变业务逻辑的情况下简化下面的sql代码。这里我提到了我想修改的postgresql代码。

SELECT IF(
    (SELECT `rate` FROM `vat_rate` WHERE '2017-05-12' BETWEEN from_date AND to_date) is not null ,
    (SELECT `rate` FROM `vat_rate` WHERE '2017-05-12' BETWEEN from_date AND to_date) ,

         IF((SELECT `rate` FROM `vat_rate` WHERE from_date is null and '2017-05-12' < to_date ) is not  null,
            (SELECT `rate` FROM `vat_rate` WHERE from_date is null and '2017-05-12' < to_date ),

                IF(
                    (SELECT `rate` FROM `vat_rate` WHERE to_date is null and '2017-05-12' > from_date) is not   null,
                          (SELECT `rate` FROM `vat_rate` WHERE to_date is null and '2017-05-12' > from_date),
                          'not found'
                         )
            )
    ) as rate 

Answer 1

复杂性是由日期中允许 NULL 引起的。使用 -infinity 和 (+)infinity 作为默认值可以避免 NULL 日期的测试。更改表定义:

ALTER TABLE vat_rate
        ALTER COLUMN from_date SET DEFAULT '-infinity'
        , ALTER COLUMN to_date SET DEFAULT 'infinity'
        ;

UPDATE vat_rate SET from_date = DEFAULT WHERE from_date IS NULL;
UPDATE vat_rate SET to_date = DEFAULT WHERE to_date IS NULL;

ALTER TABLE vat_rate
       ALTER COLUMN from_date SET NOT NULL
       , ALTER COLUMN to_date SET NOT NULL
        ;

---

可以将您的查询减少为:

---

SELECT rate AS the_reet
FROM vat_rate
WHERE '2017-05-12' BETWEEN from_date AND to_date
                ;

---

如果您不能更改表定义，您可以在查询中折叠日期值:

---

SELECT rate AS the_reet
FROM vat_rate 
WHERE '2017-05-12' BETWEEN COALESCE(from_date, '-infinity'::date)
                AND COALESCE(to_date, 'infinity'::date)
                ;

避免 COALESCE()(这可能会更快)

SELECT rate AS the_reet
FROM vat_rate 
WHERE ('2017-05-12' >=  from_date OR from_date IS NULL)
AND ( '2017-05-12' <= to_date OR to_date IS NULL)
                ;

没有匹配范围的极端情况可以通过添加联合来解决。