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

Question

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

  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 年销售额的“值(value)”。如果 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 类型的输入语法无效:‘无数据’”

Answer 1

关键元素是使用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 中可以有 0-n 年的销售额 - data 可以为 NULL。

• 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