时间戳

首页 > 解决方案 > SQL 查询 - 匹配 5 中至少 4 个值的条件

sql - SQL 查询 - 匹配 5 中至少 4 个值的条件

问题描述

我是新手SQL，我需要帮助编写查询（附照片图）

概括：

IG School 系统要求学生在特定的学习课程中选择 5 个学习科目，那么查询结果应该是：

1) 已有选定科目的群组；

或者

2) 至少符合4个所选科目的组，并且返回不匹配的科目。

表：

Sessions -----> Like Semesters
Groups   -----> Like classes that has schedule

我已经写了一个查询，但是对于只有 4 个匹配主题的可用组没有条件，只返回匹配所有 5 个主题的组（问题），如下所示：

    SELECT Group_Subjects.Group_ID, Groups.Name as Groups_Name,  Subject_Schedule.ID as Subject_Schedule_ID,Subjects.ID Subjects_ID,
    Subjects.Name AS Subjects_Name,Sessions.Name AS Sessions_Name
    FROM   Groups 
    INNER JOIN Group_Subjects ON Groups.ID = Group_Subjects.Group_ID 
    INNER JOIN Subject_Schedule ON Group_Subjects.Subject_Schedule_ID = Subject_Schedule.ID 
    INNER JOIN Subjects ON Subject_Schedule.Subject_ID = Subjects.ID
    INNER JOIN Sessions ON dbo.Groups.Session_ID = dbo.Sessions.ID   
    where Groups.Session_ID=13 and  Subjects.ID in (16,117,125,126,127)
    order by Group_Subjects.Group_ID,Subjects_Name

照片图

这里

标签： sqlrelational-division

解决方案

If I understand well (though I am not 100% sure), your requirements include: 1. Select "groups" that match at least 4 of 5 given "subjects". 2. Don't include "groups" that match less than 4 of the 5 given "subjects".

For that you can run the following query:

SELECT 
    gr.name, sub.name
FROM
    groups           gr  JOIN
    group_subject    gs  ON gs.group_id = gr.id JOIN
    subject_schedule ss  ON ss.id = gs.subject_schedule_id JOIN
    subject          sub ON sub.id = ss.subject_id  
WHERE
    EXISTS (
        SELECT 
            g.id, COUNT(*) num_subjects
        FROM
            groups           g  JOIN
            group_subject    gs  ON gs.group_id = g.id JOIN
            subject_schedule ss  ON ss.id = gs.subject_schedule_id JOIN
            subject          sub ON sub.id = ss.subject_id  
        WHERE
            sub.id IN ('A', 'B', 'C', 'D', 'E') AND
            g.id = gr.id
        GROUP BY gr.id
        HAVING num_subjects >=4
    )
ORDER BY 
    gr.name, sub.name;

Adding to that, subject_schedule seems to be referring to sessions. At the same time, groups also refers to sessions.
That can lead to problems in case that data is not consistent. For that reason I am not including 'sessions' in my query as the other tables have all the information needed for your use case.

I have created an example model of your schema to test that query (MySQL):

CREATE TABLE groups (
    id                   VARCHAR(5) PRIMARY KEY,
    name                 TEXT,
    session_id           VARCHAR(7) REFERENCES sessions
);
CREATE TABLE group_subject (
    id                   INTEGER PRIMARY KEY,
    group_id             VARCHAR(5) REFERENCES groups,
    subject_schedule_id  INTEGER REFERENCES subject_schedule,
    description          TEXT
);
CREATE TABLE subject_schedule (
    id                   INTEGER PRIMARY KEY,
    name                 TEXT,
    session_id           VARCHAR(7) REFERENCES sessions,
    subject_id           VARCHAR(1) REFERENCES subject
);
CREATE TABLE subject (
    id                   VARCHAR(1) PRIMARY KEY,
    name                 TEXT
);
CREATE TABLE sessions (
    id                   VARCHAR(7) PRIMARY KEY,
    name                 TEXT
);

Hope it helps.

推荐阅读