SQL：从2个表创建完整记录 [英] SQL : Create a full record from 2 tables
问题描述
我有一个数据库结构（简化为理解关注的最大值）：
id整数主键）
表fields（namevarchar主键等）
表entry_fields（entryid整数主键，名称varchar主键， value）
我想获得一个给定的entry.id这个条目，即。
一个例子可能会更好：
fields：
result
output
code $ bcommand
entry包含：
$ b b
id：842
id：850
entry_fields包含：
entryid：842，name：result，value：ok
entryid：842，name：output，value：this is a output
entryid：842，name：code，value：42
entryid：850，name： ，value：ko
entryid：850，name：command，value：print ko
想要的输出是：
| id |命令|输出|代码|结果|
| 842 | NULL | 这是一个输出| 42 |确定|
| 850 | print ko| NULL | NULL | ko |
目的是能够添加一个字段而不改变任何东西到entry
我尝试过类似：
SELECT e。*， SELECT name FROM fields）FROM entry AS e
但Postgres抱怨：
错误：由用作表达式的子查询返回的多行
> 虽然坚持这种不幸的设计，最快的查询将是由附加模块
tablefunc提供的crosstab（）。相关答案中的大量详细信息：
对于问题：
SELECT * FROM crosstab（
$$ SELECT e.id，ef.name，ef.value
FROM entry e
LEFT JOIN entry_fields ef
ON ef.entryid = e.id
AND ef.name = ANY（'{result，output，code，command}':: text []）
ORDER BY 1，2 $$
，$$ SELECT unnest（'{result，output，code，command}':: text []）$$
）AS ct文本，输出文本，代码文本，命令文本）;
数据库设计
t有大量不同的字段，将更简单更有效将所有三个表合并为一个简单的表：
CREATE TABLE条目（
entry_id serial PRIMARY KEY
，field1 text
，field2 text
，... fields
）;
没有值的字段可以是 NULL 。 NULL 存储是非常便宜（在NULL位图中基本上每列1位）：
即使您有数百个不同的列，并且每个条目只填充少量列，这将仍然使用少得多的磁盘空间。
您的查询变得不重要：
SELECT entry_id，result，output，code，command
FROM enty ;
如果您的列 1 太多，设计（通常，这可以折叠成更少的列），请考虑数据类型 hstore 或 json / jsonb （在Postgres 9.4中） -attribute-value / info> EAV 。
根据列类型，每个表的最大列数250 - 1600
：
此问题与典型用例/在dba.SE上的EAV结构的问题：
I've got a DB structure as is (simplified to maximum for understanding concern):
Table "entry" ("id" integer primary key)
Table "fields" ("name" varchar primary key, and others)
Table "entry_fields" ("entryid" integer primary key, "name" varchar primary key, "value")
I would like to get, for a given "entry.id", the detail of this entry, ie. all the "entry_fields" linked to this entry, in a single SQL query.
An example would be better perhaps:
"fields":
"result"
"output"
"code"
"command"
"entry" contains:
id : 842
id : 850
"entry_fields" contains:
entryid : 842, name : "result", value : "ok"
entryid : 842, name : "output", value : "this is an output"
entryid : 842, name : "code", value : "42"
entryid : 850, name : "result", value : "ko"
entryid : 850, name : "command", value : "print ko"
The wanted output would be:
| id | command | output | code | result |
| 842 | NULL | "this is an output" | 42 | ok |
| 850 | "print ko" | NULL | NULL | ko |
The aim is to be able to add a "field" without changing anything to "entry" table structure
I tried something like:
SELECT e.*, (SELECT name FROM fields) FROM entry AS e
but Postgres complains:
ERROR: more than one row returned by a subquery used as an expression
Hope someone can help me!
Solution as requested
While stuck with this unfortunate design, the fastest query would be with crosstab(), provided by the additional module tablefunc. Ample details in this related answer:
For the question asked:
SELECT * FROM crosstab(
$$SELECT e.id, ef.name, ef.value
FROM entry e
LEFT JOIN entry_fields ef
ON ef.entryid = e.id
AND ef.name = ANY ('{result,output,code,command}'::text[])
ORDER BY 1, 2$$
,$$SELECT unnest('{result,output,code,command}'::text[])$$
) AS ct (id int, result text, output text, code text, command text);
Database design
If you don't have a huge number of different fields, it will be much simpler and more efficient to merge all three tables into one simple table:
CREATE TABLE entry (
entry_id serial PRIMARY KEY
,field1 text
,field2 text
, ... more fields
);
Fields without values can be NULL. NULL storage is very cheap (basically 1 bit per column in the NULL bitmap):
- How much disk-space is needed to store a NULL value using postgresql DB?
- Do nullable columns occupy additional space in PostgreSQL?
Even if you have hundreds of different columns, and only few are filled per entry, this will still use much less disk space.
You query becomes trivial:
SELECT entry_id, result, output, code, command
FROM enty;
If you have too many columns1, and that's not just a misguided design (often, this can be folded into much fewer columns), consider the data types hstore or json / jsonb (in Postgres 9.4) for EAV storage.
Maximum Columns per Table 250 - 1600 depending on column types
Consider this related answer with alternatives:
And this question about typical use cases / problems of EAV structures on dba.SE:
这篇关于SQL：从2个表创建完整记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
