sql - PostgreSQL 用例 when result in where 子句

Question

我使用复杂的 CASE WHEN 来选择值。我想在 WHERE 子句中使用此结果，但 Postgres 说“d”列不存在。

SELECT id, name, case when complex_with_subqueries_and_multiple_when END AS d
FROM table t WHERE d IS NOT NULL
LIMIT 100, OFFSET 100;

然后我想我可以这样使用它:

select * from (
    SELECT id, name, case when complex_with_subqueries_and_multiple_when END AS d
    FROM table t 
    LIMIT 100, OFFSET 100) t
WHERE d IS NOT NULL;

但现在我没有得到 100 行结果。可能(我不确定)我可以在 select case 语句(WHERE 语句所在的位置)之外使用 LIMIT 和 OFFSET，但我认为(我不确定为什么)这会影响性能。

Case 返回数组或 null。如果 case 语句的结果为空，排除某些行的最佳/最快方法是什么？我需要 100 行(如果不存在则更少——当然)。我正在使用 Postgres 9.4。

已编辑:

SELECT count(*) OVER() AS count, t.id, t.size, t.price, t.location, t.user_id, p.city, t.price_type, ht.value as houses_type_value, ST_X(t.coordinates) as x, ST_Y(t.coordinates) AS y, 
CASE WHEN t.classification='public' THEN 
ARRAY[(SELECT i.filename FROM table_images i WHERE i.table_id=t.id ORDER BY i.weight ASC LIMIT 1), t.description] 
WHEN t.classification='protected' THEN 
ARRAY[(SELECT i.filename FROM table_images i WHERE i.table_id=t.id ORDER BY i.weight ASC LIMIT 1), t.description]
WHEN t.id IN (SELECT rl.table_id FROM table_private_list rl WHERE rl.owner_id=t.user_id AND rl.user_id=41026) THEN 
ARRAY[(SELECT i.filename FROM table_images i WHERE i.table_id=t.id ORDER BY i.weight ASC LIMIT 1), t.description]
ELSE null
END AS main_image_description
FROM table t LEFT JOIN table_modes m ON m.id = t.mode_id 
LEFT JOIN table_types y ON y.id = t.type_id 
LEFT JOIN post_codes p ON p.id = t.post_code_id 
LEFT JOIN table_houses_types ht on ht.id = t.houses_type_id 
WHERE datetime_sold IS NULL AND datetime_deleted IS NULL AND t.published=true AND coordinates IS NOT NULL AND coordinates && ST_MakeEnvelope(17.831490030182, 44.404640972306, 12.151558389557, 47.837396630872) AND main_image_description IS NOT NULL
GROUP BY t.id, m.value, y.value, p.city, ht.value ORDER BY t.id LIMIT 100 OFFSET 0

Answer 1

要在 WHERE 子句中使用 CASE WHEN 结果，您需要像以前那样将其包装在子查询或 View 中。

SELECT * FROM (
    SELECT id, name, CASE 
        WHEN name = 'foo' THEN true
        WHEN name = 'bar' THEN false
        ELSE NULL
        END AS c
    FROM case_in_where
) t WHERE c IS NOT NULL

对于包含 1、'foo'、2、'bar'、3、'baz' 的表，这将返回记录 1 和 2。我不知道这个 SQL Fiddle 会持续多久，但这里有一个例子: http://sqlfiddle.com/#!15/1d3b4/3 .另见 https://stackoverflow.com/a/7950920/101151

如果从偏移量 100 开始的那 100 行包含 d 计算结果为 NULL 的记录，则您的限制是返回少于 100 行。我不知道如何在不包括重新编写以在 where 子句内工作的限制逻辑(您的案例语句)的情况下限制子选择。

WHERE ... AND (
    t.classification='public' OR t.classification='protected'
    OR t.id IN (SELECT rl.table_id ... rl.user_id=41026))

您编写它的方式会有所不同，使 CASE 逻辑与 WHERE 限制语句保持同步可能会很烦人，但它会让您的限制仅适用于匹配数据。