如何在SQL中创建大型查询

Question

我有4张表：

CREATE TABLE workers (
ID_worker    INTEGER PRIMARY KEY AUTOINCREMENT,
name_worker INTEGER UNIQUE);

CREATE TABLE equip (
ID_equip     INTEGER PRIMARY KEY AUTOINCREMENT,
name_equip TEXT);

CREATE TABLE models (
ID_model     INTEGER PRIMARY KEY AUTOINCREMENT,
ID_equip1        INTEGER REFERENCES equip (ID_equip),
name_model TEXT);

CREATE TABLE work(
ID_worker1    INTEGER REFERENCES workers (ID_worker),
ID_model1 INTEGER REFERENCES models (ID_model) );

我想创建查询，例如: ID_worker / name_worker / ID_model / ID_model，在第一个"ID_model“中出现模型，在第二个"ID_model”中出现ID_equip1=1 - ID_equip1=2

样本数据

 Table workers
 /ID_worker/name_worker/
 /1        /Andrew     /
 /2        /Max        /
Table equip
/ID_equip/name_equip/
/1       /equip1    /
/2       /equip2    /
Table models
/ID_model/ID_equip1/name_model/
/1       /1        /BH-1      /
/2       /1        /BH-2      /
/3       /2        /CH-1      /
/4       /2        /CH-2      /
Table work
/ID_worker1/ID_model1/
/1         /1        /
/1         /4        /
/2         /2        /
/2         /3        /

我想要那个结果

/ID_worker/name_worker/ID_model/ID_model/
/1        /Andrew     /1       /4       /
/2        /Max        /2       /3       /

Answer 1

试试这个-

SELECT WS.ID_worker, WS.name_worker, MIN(W.ID_model1), MAX(W.ID_model1)
FROM workers WS
INNER JOIN work W ON WS.ID_worker = W.ID_worker1
GROUP BY WS.ID_worker, WS.name_worker