sql - 如何包含不符合条件的行？

我知道我可能只是想错了。我有以下结构：

  CREATE TABLE mytable (
  id       serial PRIMARY KEY
, employee text UNIQUE NOT NULL
, data     jsonb
);

以及以下数据：

INSERT INTO mytable (employee, data)
VALUES
 ('Jim', '{"sales": [{"value": 10, "yr": "2010"}, {"value": 5, "yr": "2011"}, {"value": 40, "yr": "2012"}]}'),
 ('Rob', '{"sales": [{"value": 10, "yr": "2009"}, {"value": 5, "yr": "2010"}, {"value": 41, "yr": "2011"}]}')

我想把所有员工和他们2012年销售额的“价值”都还给他们。如果2012年没有销售，则返回“无数据”。我有：

SELECT id, employee,
coalesce((SELECT s.value AS value FROM mytable, jsonb_to_recordset(mytable.data->'sales') AS s(yr text, value float)
WHERE s.yr='2012'), 0) AS b FROM mytable

我得到：

id |employee |b
53 |Jim      |40
54 |Rob      |40

“Rob”的值不正确。应该是“没有数据”。（我使用0作为coalesce的第二个参数，因为我得到一个错误“类型double precision的无效输入语法：'No Data'”

最佳答案

关键是使用LEFT JOIN LATERAL而不是隐式CROSS JOIN LATERAL来解释只有逗号的短符号。
What is the difference between LATERAL and a subquery in PostgreSQL?
Call a set-returning function with an array argument multiple times
查询可以是：

SELECT t.id, t.employee, s.*
FROM   mytable t
LEFT   JOIN LATERAL jsonb_to_recordset(t.data->'sales')
                 AS s(yr int, value int) ON s.yr = 2012;

我们可以很方便地在不损失员工的情况下，立即选择yr = 2012的销售。
若要用“无数据”进行美化，value列必须是匹配的字符串类型：

SELECT t.id, t.employee
     , COALESCE(s.yr, 2012) AS yr
     , COALESCE(s.value, 'No Data') AS value
FROM   mytable t
LEFT   JOIN LATERAL jsonb_to_recordset(t.data->'sales')
                 AS s(yr int, value text) ON s.yr = 2012;

基于这些缺失（可能）的细节：
表中每个员工只有一行：empoyee是UNIQUE NOT NULL。
每个员工在data-data中可以有0-n年的销售。
yr和value存储有效整数。否则就改头换面。
正确的表定义：

CREATE TABLE mytable (
  id       serial PRIMARY KEY
, employee text UNIQUE NOT NULL
, data     jsonb
);

什么是LATERAL JOIN？
根据评论中的要求，这些链接应该会有所帮助，尤其是对于初学者来说：
http://www.postgresql.org/docs/current/interactive/queries-table-expressions.html#QUERIES-LATERAL
http://rhaas.blogspot.co.at/2010/04/finding-our-way-to-lateral.html
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3#LATERAL_JOIN

关于sql - 如何包含不符合条件的行？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31328611/