选择要从中选择的表，取决于已经给定表的列值 [英] select table to select from, dependent on column-value of already given table

问题描述

出于我的意图，我必须选择一个表来根据已给定表的列值选择列.

for my intention I have to select a table to select columns from dependent on the column-value of an already given table.

首先我想到了一个 CASE 结构，如果这可以用 sqlite 实现的话.

First I thought about a CASE construct, if this is possible with sqlite.

SELECT * FROM
CASE IF myTable.column1 = "value1" THEN (SELECT * FROM table1 WHERE ...)
ELSE IF myTable.column1 = "value2" THEN (SELECT * FROM table2 WHERE ...)
END;

我是 SQL 新手.什么结构是最简洁(不难看)的解决方案，如果我不能在 sqlite 中使用它，那么哪种 RDBM 最适合?

I am new to SQL. What construct would be the most concise (not ugly) solution and if I cannot have it in sqlite, what RDBM would be the best fit?

谢谢

推荐答案

这里有一个建议，用于为 mytable 中的每个条目关联两个表之一的值.即这是假设 mytable 不仅包含用于选择辅助表的单个条目.有关这意味着什么的详细信息，请参阅本答案末尾的MCVE".

Here is a proposal for associating a value from one of two tables for each entry in mytable. I.e. this is making the assumption that mytable does not only contain a single entry for choosing the secondary table. For details on what this means, see "MCVE" at the end of this answer.

如果您想根据主表中的单个条目在两个辅助表之间切换，请参阅本答案的最后.

If you want to switch between two secondary tables, based on a single entry in main table, see at the very end of this answer.

详情:

• 将硬编码的value1"/value2"作为 column1 动态添加到辅助表的结果中
• 通过伪造的 colummn1 和辅助连接键加入，这里假设 id
• a union all 从两个辅助表(包括假列1)制作一个表

• a hardcoded "value1"/"value2" as column1 added on the fly to the result from secondary tables
• joining by the faked colummn1 and a secondary join-key, assumption here id
• a union all to make a single table from both secondary tables (including the fake column1)

选择*来自 mytable左连接(选择 'value1' 作为 column1，* from table1联合所有选择 'value2' 作为 column1，* from table2)使用(id, column1);

select * from mytable left join (select 'value1' as column1, * from table1 UNION ALL select 'value2' as column1, * from table2) using(id, column1);

输出(对于下面提供的 MCVE，表 1 中的a-f"，表 2 中的A-Z"):

Output (for the MCVE provided below, "a-f" from table1, "A-Z" from table2):

value1|1|a
value2|2|B
value1|3|c
value1|4|d
value2|5|E
value2|6|F

MCVE:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mytable (column1 varchar(10), id int);
INSERT INTO mytable VALUES('value1',1);
INSERT INTO mytable VALUES('value2',2);
INSERT INTO mytable VALUES('value1',3);
INSERT INTO mytable VALUES('value1',4);
INSERT INTO mytable VALUES('value2',5);
INSERT INTO mytable VALUES('value2',6);
CREATE TABLE table2 (value varchar(2), id int);
INSERT INTO table2 VALUES('F',6);
INSERT INTO table2 VALUES('E',5);
INSERT INTO table2 VALUES('D',4);
INSERT INTO table2 VALUES('C',3);
INSERT INTO table2 VALUES('B',2);
INSERT INTO table2 VALUES('A',1);
CREATE TABLE table1 (value varchar(2), id int);
INSERT INTO table1 VALUES('a',1);
INSERT INTO table1 VALUES('b',2);
INSERT INTO table1 VALUES('c',3);
INSERT INTO table1 VALUES('d',4);
INSERT INTO table1 VALUES('e',5);
INSERT INTO table1 VALUES('f',6);
COMMIT;

用于根据主表中的单个条目在两个表之间进行选择(在本例中为mytable2":

For selecting between two tables based on a single entry in main table (in this case "mytable2":

select * from table1 where (select column1 from mytable2) = 'value1'
 union all
select * from table2 where (select column1 from mytable2) = 'value2';

输出(mytable2 只包含value1"):

Output (with mytable2 only containing 'value1'):

a|1
b|2
c|3
d|4
e|5
f|6

这篇关于选择要从中选择的表，取决于已经给定表的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！