首页 文章

如何获取mysql中两个表中列之间的匹配计数

提问于
浏览
1

我试图弄清楚如何在mySQL中使用这个场景 -

有三个表 - 表的结构(基本上每列中的列)如下:

t_student:
s_id, s_name, s1, s2, s3

其中s_id是主键,s_name是学生名,(s1,s2,s3)是数学,物理,化学等科目 .

t_teacher:
t_id, t_name, s1, s2, s3

其中t_id是主键,t_name是教师名称,(s1,s2,s3)是数学,phy,chem等主题 .

t_result:
r_id, s_name, t_name, count

其中r_id是主键,s_name是学生姓名(来自学生表),t_name是教师姓名(来自教师表),计数给出计数(稍后更多)

现在,我想做的是如下:

学生和教师表中可以有任意数量的记录,结果表中没有任何记录 . 现在,使用mySQL我想扫描student表的内容,对于这个表中的每条记录,我想要拾取:

s1然后将该值与教师表s2中的(s1,s2,s3)列进行比较,然后将该值与教师表s3中的(s1,s2,s3)列进行比较,然后将该值与(s1,s2,s3)进行比较老师表中的列

并获取匹配值的数量并存储在count中 .

为了更清楚,如果对于student表中的第一条记录,s1,s2和s3是“phy”,“chem”和“maths”,如果教师表中的第一条记录是“maths”,“phy”,“computer” “,然后在这种情况下,student.s1与teacher.s2匹配 - 所以现在count为1.然后student.s2与教师表中的s1,s2,s3匹配,但匹配为0,因此count保持为1;再次student.s3与教师表中的s1,s2,s3匹配,这次它与teacher.s1匹配,因此计数增加到2.因此在学生表中的第一条记录与教师表的第一条记录的比较结束时,我得到计数= 2 . 现在我将在结果表中插入一行,其中包含学生姓名,教师姓名和如此获得的计数 .

基本上我想得到学生表和教师表之间的s1,s2,s3匹配数,对于学生表中的每一行,然后将其放入结果表中 .

我只知道在mysql中的基本操作 - 比如选择插入删除等 . 我假设这样的操作需要更多,比如plsql和存储过程?

仅供参考,我使用的是phpmyadmin,表存放在那里 . 我将使用PHP从表中获取结果,并执行这些查询 .

请让我知道这方面的方法 .

谢谢!

4 回答

  • 0

    您的数据库设计需要一些工作 . 首先规范化数据,将重复的主题列移出教师和学生表 . 您可以通过创建以下表来执行此操作:

    CREATE TABLE t_subjects ( subject_id INT, name VARCHAR(30) );
    CREATE TABLE t_teacher_subjects ( teach_id INT, subject_id INT);
    CREATE TABLE t_student_subjects ( student_id INT, subject_id INT);
    

    从教师和学生中删除s1,s2,s3列,然后您就可以在单个查询中填充结果表(前提是它有一个auto_increment主键) - 如下所示:

    insert into t_result (s_name, t_name, count) 
    select t_teacher.t_name, t_student.s_name, count(*) as c from
    t_teacher_subjects 
        inner join t_student_subjects on t_teacher_subjects.subject_id = t_student_subjects.subject_id
        inner join t_teacher on t_teacher_subjects.teach_id = t_teacher.t_id
        inner join t_student on t_student_subjects.student_id = t_student.s_id
    group by t_teacher.t_name, t_student.s_name;
    
  • 0

    您应该将表修改为

    t_student:
    s_id, s_name, s_s
    

    t_teacher:
    t_id, t_name, t_s
    

    并将s1,s2,s3拆分为不同的行 . 然后你可以在t_student和t_teacher之间进行简单的连接 .

  • 1

    实际上,您不必需要任何过程代码来解决此问题 . 该方法称为“标准化” . 您需要多个表来表示教师的主题,以便s1,s2,s3值转到单独表中的单个列,该表与教师表具有外键关系 . 其他关系也一样 . (这就是“关系数据库”一词的原因 . )

    相关的讨论在这里:DB Design: 1st Normal Form and Repeating Groups .

    Edit

    看起来像一个任务而不是 生产环境 问题:) . 无论如何,您仍然可以尝试使用复杂的纯SQL方法在 query 中而不是在模式中进行规范化 . 奇怪但不是不可能 .

    这是诀窍的本质 . 将其用作子查询以获取数据的规范化表示:

    select s_id, s_name, s1 s from t_student
    union
    select s_id, s_name, s2 s from t_student
    union
    select s_id, s_name, s3 s from t_student
    
  • 1

    好 . 谢谢你的挑战 . 一个很好的心理锻炼:) .

    这是查询;只需将其转换为插入内容:

    select s_name, t_name, count(*) cnt from
    (
    select s.s_name, s.s s_s, t.t_name, t.s t_s from
    (
    select s_id, s_name, s1 s from t_student
    union
    select s_id, s_name, s2 s from t_student
    union
    select s_id, s_name, s3 s from t_student
    ) s
    inner join
    (
    select t_id, t_name, s1 s from t_teacher
    union
    select t_id, t_name, s2 s from t_teacher
    union
    select t_id, t_name, s3 s from t_teacher
    ) t
    on t.s = s.s
    ) m
    group by s_name, t_name
    ;
    

    Edit: 实际运行:

    mysql> select * from t_student;
    +------+--------+------+------+------+
    | s_id | s_name | s1   | s2   | s3   |
    +------+--------+------+------+------+
    |    1 | st1    | qqq  | www  | eee  |
    |    2 | st2    | 111  | 222  | 333  |
    |    3 | st3    | zzz  | xxx  | ccc  |
    +------+--------+------+------+------+
    3 rows in set (0.00 sec)
    

    mysql> select * from t_teacher;
    +------+--------+------+------+------+
    | t_id | t_name | s1   | s2   | s3   |
    +------+--------+------+------+------+
    |    1 | te1    | qqq  | www  | eee  |
    |    2 | te2    | 111  | 222  | nnn  |
    |    3 | te3    | zzz  | nnn  | nnn  |
    +------+--------+------+------+------+
    3 rows in set (0.00 sec)
    

    mysql> select s_name, t_name, count(*) cnt from
        -> (
        -> select s.s_name, s.s s_s, t.t_name, t.s t_s from
        -> (
        -> select s_id, s_name, s1 s from t_student
        -> union
        -> select s_id, s_name, s2 s from t_student
        -> union
        -> select s_id, s_name, s3 s from t_student
        -> ) s
        -> inner join
        -> (
        -> select t_id, t_name, s1 s from t_teacher
        -> union
        -> select t_id, t_name, s2 s from t_teacher
        -> union
        -> select t_id, t_name, s3 s from t_teacher
        -> ) t
        -> on t.s = s.s
        -> ) m
        -> group by s_name, t_name
        -> ;
    

    +--------+--------+-----+
    | s_name | t_name | cnt |
    +--------+--------+-----+
    | st1    | te1    |   3 |
    | st2    | te2    |   2 |
    | st3    | te3    |   1 |
    +--------+--------+-----+
    3 rows in set (0.00 sec)
    

相关问题