从两个表中选择查询获取数据: sqlite

Question

我有两个表"Student_Info表“和"Training_Status表”

Student_Info包含有关学生姓名、groupId、学生组卷数、年龄等信息。Training_Status表包含不同学生培训状况的条目。

表格的结构如下：

Student_Info表

groupId    GroupRollNo     Name      Age     Level
- - - - - - - - - - - - - - - -  - - -  - -  -  - -  
G1             1           Sam       24        4 
G1             2           David     25        2 
G1             3           Robin     26        3 
G1             4           John      28        4 
G2             1           Harry     26        3 
G2             2           Jerry     28        4 
G3             1           Michel    28        3
G3             2           Jerry     28        4 
G4             1           Michel    28        3

将学生分为不同的组( G1、G2、G3 )，同一组的学生不能有相同的GroupRollNo。但是来自不同群体的学生可以有相同的GroupRollNo。

Training_Status表

groupId     GroupRollNo    TrainingStatus
- - - - - - - - - - - - - - - - - - - - - 
 G1             2            InProgress
 G1             3            OnHold
 G3             1            InProgress
 G3             2            Completed

随着对学生的培训开始，进入进入"Training_Status表“与groupId和GroupRollNo和培训状态。并根据培训状态更新该表(OnHold，已完成)

考虑场景

有100名学生在Student_Info表中被分成不同的小组。(比如G1，G2，G3 )。Training_Status表中只有10个条目(仅包括那些已经开始培训的学生)，它们来自具有当前状态的不同组。

我需要从两个表中获取来自特定组的所有学生的培训状态，并提供学生的详细信息。

和我一样，需要从Sqlite查询中获取G1 (第1组)所有学生的培训状态，并提供学生的详细信息。。

期望查询结果

groupId    GroupRollNo     Name        Age     Level    TrainingStatus
- - - - - - - - - - - - - - - -  - - -  - -  -  - - - - - - - - - - - - -
 G1             1          Sam         24        4      NULL(means training not started)
 G1             2          David       25        2      InProgress
 G1             3          Robin       26        3      OnHold
 G1             4          John        28        4      NULL(means training not started)

我应该编写什么样的sqlite查询，以便从两个表中获得所需格式的结果.

Answer 1

我刚刚创建了一个示例来说明如何根据您的数据进行操作：

-- Set up sqlite
.mode column
.headers ON
--
DROP TABLE IF EXISTS Student_Id;
CREATE TABLE Student_Id (
   groupId TEXT,
   GroupRollNo INTEGER,
   Name TEXT,
   Age INTEGER,
   Level INTEGER,
   UNIQUE(groupId, GroupRollNo));
--
DROP TABLE IF EXISTS Training_Status;
CREATE TABLE Training_Status (
   groupId TEXT,
   GroupRollNo INTEGER,
   TrainingStatus TEXT,
   UNIQUE(groupId, GroupRollNo));
--
-- Create Data
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G1", 1, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G1", 2, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G1", 3, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G1", 4, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G2", 1, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G2", 2, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G3", 1, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G3", 2, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G4", 1, "Sam", 24, 4);
INSERT INTO Student_Id (groupId, GroupRollNo, Name, Age, Level) VALUES("G4", 2, "Sam", 24, 4);
-- 2nd Table
INSERT INTO Training_Status (groupId, GroupRollNo, TrainingStatus) VALUES("G1", 2, "InProgress");
INSERT INTO Training_Status (groupId, GroupRollNo, TrainingStatus) VALUES("G1", 3, "OnHold");
INSERT INTO Training_Status (groupId, GroupRollNo, TrainingStatus) VALUES("G3", 1, "InProgress");
INSERT INTO Training_Status (groupId, GroupRollNo, TrainingStatus) VALUES("G3", 2, "Completed");

查询科：

-- Check data
SELECT * FROM Student_Id;
SELECT * FROM Training_Status;

-- 1.- Query (inner JOIN: only records that are on both tables)
SELECT t1.groupId AS groupId, t1.GroupRollNo AS GroupRollNo, t1.Name AS Name, 
   t1.Age AS Age, t1.Level AS Level, t2.TrainingStatus AS TrainingStatus
FROM Student_Id AS t1, Training_Status AS t2
WHERE t1.groupID = t2.groupId AND t1.GroupRollNo = t2.GroupRollNo AND t1.groupId = "G1"
ORDER BY t1.groupId, t1.GroupRollNo;

-- 2.- Query (outer join: Both tables are combined together showing all records)
SELECT t1.groupId AS groupId, t1.GroupRollNo AS GroupRollNo, t1.Name AS Name, 
   t1.Age AS Age, t1.Level AS Level, t2.TrainingStatus AS TrainingStatus
FROM Student_Id AS t1 LEFT JOIN Training_Status AS t2
ON t1.groupID = t2.groupId AND t1.GroupRollNo = t2.GroupRollNo
WHERE t1.groupId = "G1"
ORDER BY t1.groupId, t1.GroupRollNo;

-- 3.- Query for NOT NULL (outer join: Both tables are combined together showing all records)
SELECT t1.groupId AS groupId, t1.GroupRollNo AS GroupRollNo, t1.Name AS Name, 
   t1.Age AS Age, t1.Level AS Level, t2.TrainingStatus AS TrainingStatus
FROM Student_Id AS t1 LEFT JOIN Training_Status AS t2
ON t1.groupID = t2.groupId AND t1.GroupRollNo = t2.GroupRollNo
WHERE t1.groupId = "G1" AND t2.TrainingStatus IS NOT NULL
ORDER BY t1.groupId, t1.GroupRollNo;

-- 4.- Query for NULL (outer join: Both tables are combined together showing all records)
SELECT t1.groupId AS groupId, t1.GroupRollNo AS GroupRollNo, t1.Name AS Name, 
   t1.Age AS Age, t1.Level AS Level, typeof(t2.TrainingStatus) AS TrainingStatus
FROM Student_Id AS t1 LEFT JOIN Training_Status AS t2
ON t1.groupID = t2.groupId AND t1.GroupRollNo = t2.GroupRollNo
WHERE t1.groupId = "G1" AND t2.TrainingStatus IS NULL
ORDER BY t1.groupId, t1.GroupRollNo;

请注意以下事项：

独特(.)约束将阻止具有相同groupId和GroupRollNo的两个条目。

查询使用公共列groupId和GroupRollNo显式连接两个表。

需要使用IS而不是= NULL查询NULL值。