需要在SQLite中使用视图通过唯一数字ID连接三个表的帮助

0yg35tkg 于 2023-04-21 发布在 SQLite

关注(0)|答案(1)|浏览(107)

我正在处理一个数据模型，我计划将一个表中的列拆分为多个表，因为不同列的预期更新频率可能会有很大差异。我仍然希望有一个单一的视图，输出所有表都是一个的说明。
基本上我有一个名字作为主键，我使用一个单独的ID表(ID, NAME)，其中ID是唯一的主键来标识相应的行。我还有一个创建者表(ID, NAME)，它将冗长的名字Map到ID。主要数据被分成两个表，每个表都有一个ID列用于与ID表相关联。
长话短说，下面是一个愚蠢的示例数据集的表定义：

BEGIN TRANSACTION;
CREATE TABLE KEY_ID (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME VARCHAR(32));
INSERT INTO KEY_ID VALUES(1,'Name');
CREATE TABLE CREATORS (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME VARCHAR(32));
INSERT INTO CREATORS VALUES(1,'Creator');
CREATE TABLE KEY_DATA (ID INTEGER PRIMARY KEY NOT NULL REFERENCES KEY_ID(ID), KEY_DATA VARCHAR(4096), CREATOR_ID INTEGER not NULL REFERENCES CREATORS(ID), CREATED DATETIME, MODIFIED DATETIME, LOCKED DATETIME);
INSERT INTO KEY_DATA VALUES(1,'K',1,0,0,0);
CREATE TABLE KEY_PROT (ID INTEGER PRIMARY KEY NOT NULL REFERENCES KEY_ID(ID), DK_DATA VARCHAR(128), KEK_DATA VARCHAR(128));
INSERT INTO KEY_PROT VALUES(1,'X','Y');
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('KEY_ID',1);
CREATE VIEW V AS SELECT KEY_ID.NAME AS NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED, KEY_PROT.DK_DATA AS DK_DATA, KEY_PROT.KEK_DATA AS KEK_DATA FROM KEY_ID, CREATORS, KEY_DATA, KEY_PROT JOIN ID ON KEY_ID.ID = KEY_DATA.ID JOIN ID ON KEY_PROT.ID = KEY_DATA.ID JOIN ID ON CREATORS.ID = KEY_DATA.ID;
COMMIT;

当查询视图时，我得到了他的错误消息：

sqlite> select * from V;
Parse error: no such table: main.ID

让我困惑的是，我的视图不包含任何名为main的表。也许while问题可以改为“* 如何在SQLite视图中Map外键关系？*”。

尝试解决问题

在阅读了SQLite INNER JOIN with Examples中的“SQLite INNER JOIN examples”之后，我仍然没有成功，所以我试图首先使用SELECT获得所需的结果，但即使这样也失败了：

sqlite> CREATE VIEW V AS SELECT KEY_ID.NAME AS NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED, KEY_PROT.DK_DATA AS DK_DATA, KEY_PROT.KEK_DATA AS KEK_DATA FROM KEY_ID, CREATORS, KEY_DATA, KEY_PROT FROM KEY_ID INNER JOIN ID ON KEY_ID.ID = KEY_DATA.ID INNER JOIN ID ON KEY_PROT.ID = KEY_DATA.ID INNER JOIN ID ON CREATORS.ID = KEY_DATA.ID;
Parse error: near "FROM": syntax error
  EK_DATA FROM KEY_ID, CREATORS, KEY_DATA, KEY_PROT FROM KEY_ID INNER JOIN ID ON
                                      error here ---^
sqlite> SELECT KEY_ID.NAME AS NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED, KEY_PROT.DK_DATA AS DK_DATA, KEY_PROT.KEK_DATA AS KEK_DATA FROM KEY_ID, CREATORS, KEY_DATA, KEY_PROT INNER JOIN KEY_ID ON KEY_ID.ID = KEY_DATA.ID INNER JOIN KEY_ID ON KEY_PROT.ID = KEY_DATA.ID INNER JOIN KEY_DATA ON CREATORS.ID = KEY_DATA.CREATOR_ID;
Parse error: ambiguous column name: KEY_ID.NAME
  SELECT KEY_ID.NAME AS NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIF
         ^--- error here

sqlite

来源：https://stackoverflow.com/questions/76011992/need-help-joining-three-tables-by-unique-numeric-id-using-a-view-in-sqlite

1条答案

按热度按时间

b1payxdu1#

我在answer https://dba.stackexchange.com/a/267839/272354中找到了基本的提示，这意味着有 * 父表 * 和 * 子表 *。基本上SQLite的错误消息不是很有用，解决方案是删除SELECT中的大多数AS短语，并以FROM和 * 父表 *（KEY_ID）开始。
因此，我正在“分解”视图，如下面的编号列表：

CREATE VIEW V_KEY_FLAT AS # the view
SELECT KEY_ID.NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED, KEY_PROT.DK_DATA, KEY_PROT.KEK_DATA # the * 字段 *
FROM KEY_ID #* 父表 *
INNER JOIN KEY_DATA USING (ID) #第一个 * 子表 *
INNER JOIN KEY_PROT USING(ID) # second * 子表 *
INNER JOIN CREATORS ON CREATORS.ID = KEY_DATA.CREATOR_ID; #第三个 * 子表 * 在字段中使用不同的名称
以下是执行计划：

sqlite> explain select * from V_KEY_FLAT;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     29    0                    0   Start at 29
1     OpenRead       1     4     0     2              0   root=4 iDb=0; KEY_ID
2     OpenRead       2     9     0     6              0   root=9 iDb=0; KEY_DATA
3     OpenRead       3     13    0     3              0   root=13 iDb=0; KEY_PROT
4     OpenRead       4     7     0     2              0   root=7 iDb=0; CREATORS
5     Rewind         1     28    0                    0
6       Rowid          1     1     0                    0   r[1]=KEY_ID.rowid
7       SeekRowid      2     27    1                    0   intkey=r[1]
8       Rowid          1     2     0                    0   r[2]=KEY_ID.rowid
9       Rowid          2     3     0                    0   r[3]=KEY_DATA.rowid
10      Ne             3     27    2                    83  if r[2]!=r[3] goto 27
11      Rowid          1     4     0                    0   r[4]=KEY_ID.rowid
12      SeekRowid      3     27    4                    0   intkey=r[4]
13      Rowid          1     3     0                    0   r[3]=KEY_ID.rowid
14      Rowid          3     2     0                    0   r[2]=KEY_PROT.rowid
15      Ne             2     27    3                    83  if r[3]!=r[2] goto 27
16      Column         2     2     5                    0   r[5]=KEY_DATA.CREATOR_ID
17      SeekRowid      4     27    5                    0   intkey=r[5]
18      Column         1     1     6                    0   r[6]=KEY_ID.NAME
19      Column         2     1     7                    0   r[7]=KEY_DATA.KEY_DATA
20      Column         4     1     8                    0   r[8]=CREATORS.NAME
21      Column         2     3     9                    0   r[9]=KEY_DATA.CREATED
22      Column         2     4     10                   0   r[10]=KEY_DATA.MODIFIED
23      Column         2     5     11                   0   r[11]=KEY_DATA.LOCKED
24      Column         3     1     12                   0   r[12]=KEY_PROT.DK_DATA
25      Column         3     2     13                   0   r[13]=KEY_PROT.KEK_DATA
26      ResultRow      6     8     0                    0   output=r[6..13]
27    Next           1     6     0                    1
28    Halt           0     0     0                    0
29    Transaction    0     0     9     0              1   usesStmtJournal=0
30    Goto           0     1     0                    0

（我不禁要问：* 谁能解释explain的输出？* 我似乎使用了错误的JOIN类型）
在一个新的例子中：

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE KEY_ID (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME VARCHAR(32));
INSERT INTO KEY_ID VALUES(1,'Name1');
INSERT INTO KEY_ID VALUES(2,'Name2');
CREATE TABLE CREATORS (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, NAME VARCHAR(32));
INSERT INTO CREATORS VALUES(1,'Creator1');
INSERT INTO CREATORS VALUES(3,'Creator3');
CREATE TABLE KEY_DATA (ID INTEGER PRIMARY KEY NOT NULL REFERENCES KEY_ID(ID), KEY_DATA VARCHAR(4096), CREATOR_ID INTEGER not NULL REFERENCES CREATORS(ID), CREATED DATETIME, MODIFIED DATETIME, LOCKED DATETIME);
INSERT INTO KEY_DATA VALUES(1,'K1',1,0,0,0);
INSERT INTO KEY_DATA VALUES(2,'K2',3,0,0,0);
CREATE TABLE KEY_PROT (ID INTEGER PRIMARY KEY NOT NULL REFERENCES KEY_ID(ID), DK_DATA VARCHAR(128), KEK_DATA VARCHAR(128));
INSERT INTO KEY_PROT VALUES(1,'A','B');
INSERT INTO KEY_PROT VALUES(2,'C','D');
DELETE FROM sqlite_sequence;
INSERT INTO sqlite_sequence VALUES('KEY_ID',2);
INSERT INTO sqlite_sequence VALUES('CREATORS',3);
CREATE VIEW V_KEY_FLAT AS SELECT KEY_ID.NAME, KEY_DATA, CREATORS.NAME AS CREATOR, CREATED, MODIFIED, LOCKED, KEY_PROT.DK_DATA, KEY_PROT.KEK_DATA FROM KEY_ID INNER JOIN KEY_DATA USING (ID) INNER JOIN KEY_PROT USING(ID) INNER JOIN CREATORS ON CREATORS.ID = KEY_DATA.CREATOR_ID;
COMMIT;
sqlite> .headers ON
sqlite> select * from V_KEY_FLAT;
NAME|KEY_DATA|CREATOR|CREATED|MODIFIED|LOCKED|DK_DATA|KEK_DATA
Name1|K1|Creator1|0|0|0|A|B
Name2|K2|Creator3|0|0|0|C|D

赞(0）回复(0）举报 2023-04-21

我来回答

需要在SQLite中使用视图通过唯一数字ID连接三个表的帮助

尝试解决问题

1条答案

相关问题

热门标签

最新问答