Nano Hash - криптовалюты, майнинг, программирование

как получить количество совпадений между столбцами в двух таблицах в mysql

Я пытаюсь понять, как работать с этим сценарием в mySQL -

Есть три таблицы - t_student, t_teacher, t_result структура таблиц (в основном столбцы в каждой из них) следующая:

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) — такие предметы, как математика, физика, химия и т. д.

t_result:
r_id, s_name, t_name, count

где r_id — первичный ключ, s_name — имя ученика (из таблицы учеников), t_name — имя учителя (из таблицы учителей), а count дает количество (подробнее об этом чуть позже)

Теперь я хотел бы сделать следующее:

в таблице учеников и учителей может быть любое количество записей, а в таблице результатов на данный момент их нет. Теперь, используя mySQL, я хотел бы отсканировать содержимое таблицы учеников, и для каждой записи в этой таблице я хотел бы получить:

s1, а затем сравните значение со столбцами (s1,s2,s3) в таблице учителей s2, а затем сравните значение со столбцами (s1,s2,s3) в таблице учителей s3, а затем сравните значение с (s1,s2,s3) столбцы в учительской таблице

и получить количество совпадающих значений и сохранить в счете.

Для большей ясности, если для первой записи в таблице учеников s1, s2 и s3 — это «физика», «химия» и «математика», а если первая запись в таблице учителей — это «математика», «физика», «компьютер». ", тогда в этом случае student.s1 совпадает с учителем.s2 - так что теперь count равен 1. тогда student.s2 сопоставляется с s1, s2, s3 в таблице учителей, но совпадение равно 0, поэтому count остается равным 1; снова student.s3 сопоставляется с s1, s2, s3 в таблице учителей, на этот раз он совпадает с учителем.s1, поэтому счетчик увеличивается до 2. Таким образом, в конце сравнения первой записи в таблице учеников с первой записью таблицы учителей , я получаю количество = 2. Теперь я бы вставил строку в таблицу результатов с именем ученика, именем учителя и полученным таким образом счетом.

В основном я хочу получить количество совпадений s1, s2, s3 между таблицей учеников и таблицей учителей для каждой строки в таблице учеников, а затем поместить это в таблицу результатов.

Я знаю только основные операции в mysql, такие как выбор, вставка, удаление и т. д. Я предполагаю, что для такой операции потребуется нечто большее, что-то вроде plsql и хранимой процедуры?

К вашему сведению, я использую phpmyadmin, и таблицы хранятся там. Я бы использовал php для получения результатов из таблицы и выполнения этих запросов.

Пожалуйста, дайте мне знать подход к этому.

Спасибо!

mysql stored-procedures count plsql phpmyadmin
13.10.2012

Ответы:


1

Над дизайном вашей базы данных нужно поработать. Начните с нормализации данных, переместив повторяющиеся столбцы темы из таблиц учителей и учеников. Вы можете сделать это, создав следующие таблицы:

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;
13.10.2012
  • спасибо, но опять же я не могу убрать предметы с других столов. На самом деле, предметы школьного стола и предметы учительского стола совершенно разные и не связаны между собой. Эти две таблицы независимы. Мое требование - сопоставить предмет, чтобы увидеть, существует ли он в каком-либо из столбцов таблицы учителей, и получить количество совпадающих предметов в Count. Я думаю, что сделал свой первоначальный пост немного запутанным, но тогда таблицы нельзя нормализовать дальше. любой способ получить счет без нормализации? 13.10.2012

    • 2

    Вы должны изменить свои таблицы на

    t_student:
s_id, s_name, s_s

    и

    t_teacher:
t_id, t_name, t_s

    и разделите ваши s1, s2, s3 на отдельные строки. Затем вы можете выполнить простое соединение между t_student и t_teacher.

    13.10.2012

    3

    На самом деле вам не нужен какой-либо процедурный код, чтобы решить эту проблему. Подход называется «нормализация». Вам нужно более одной таблицы для представления предметов для учителя, чтобы значения s1, s2, s3 попадали в один столбец в отдельной таблице, которая находится в отношениях внешнего ключа с таблицей учителя. То же самое и с другими отношениями. (Вот почему в первую очередь используется термин «реляционная база данных».)

    Соответствующее обсуждение находится здесь: Дизайн БД: 1-я нормальная форма и повторяющиеся группы< /а>.

    Изменить

    Похоже на задание, а не производственную проблему :). В любом случае, вы все равно можете попробовать замысловатый подход на чистом SQL для нормализации в запросе, а не в схеме. Странно, но не невозможно.

    Вот суть трюка. Используйте это как подзапрос, чтобы получить нормализованное представление ваших данных:

    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
    13.10.2012
  • спасибо за изучение этого, но я думаю, вы неправильно поняли мое требование. Записи в обеих таблицах различны и поступают из разных источников (на высоком уровне) и на самом деле не зависят друг от друга. Так что третья таблица - таблица результатов действительно необходима. Возможно, я не полностью объясняю вещи, но поверьте мне, это не о нормализации. Я просто хотел бы знать, как определить COUNT, сопоставив каждого из этих учеников (s1, s2, s3) с учителем (s1, s2, s3). Еще раз спасибо. 13.10.2012
  • Скорее всего, мы все упускаем суть. Но все равно похоже, что все ответы здесь примерно одинаковы: устранение повторяющейся группы s1, s2, s3 и использование вместо этого отношения «один ко многим». Это действительно нормализация. 13.10.2012
  • да .. я думаю, мой вопрос недостаточно ясен! я думаю, мне нужно было бы написать процедуру, а затем получить количество совпадений, а затем войти в таблицу результатов. сейчас изучаю этот подход. в любом случае спасибо за помощь :) 13.10.2012
  • Добавлен еще один ответ с запросом, который нормализует ваши данные внутри. 14.10.2012

    • 4

    Что ж. Спасибо за этот вызов. Хорошее умственное упражнение :).

    Вот запрос; просто преобразуйте его во вставку:

    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
;

    Изменить: Фактический запуск:

    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)
    13.10.2012
  • хм .. хорошая работа, но все же ни один из ответов не то, что я ищу. :) 14.10.2012
  • Для большей ясности, если для первой записи в таблице учеников s1, s2 и s3 — это физика, химия и математика, а если первая запись в таблице учителей — это математика, физкультура, компьютер, то в этом случае student.s1 совпадает с учитель.s2 - так что теперь count равен 1. тогда student.s2 сопоставляется с s1, s2, s3 в таблице учителей, но совпадение равно 0, поэтому count остается равным 1; снова student.s3 сопоставляется с s1, s2, s3 в таблице учителей, на этот раз он совпадает с учителем.s1, поэтому счетчик увеличивается до 2. 14.10.2012
  • Итак, в конце сравнения первой записи в таблице учеников с первой записью в таблице учителей я получаю количество = 2. Теперь я бы вставил строку в таблицу результатов с именем ученика, именем учителя и полученным таким образом счетом. 14.10.2012
  • Ответ full.stack.ex даст описанный вами результат. Поворачивая все столбцы предмета в каждой из таблиц учеников и учителей, вы получаете копию нормализованных данных с возможностью для одного ученика сопоставить учителя несколько раз, что даст результат, который вы ищете. 14.10.2012
  • Я действительно пробовал это с MySQL. У вас есть? Я отредактировал ответ с тестовыми данными и результатом. Чем оно отличается от того, что вы хотите? 14.10.2012
    • Новые материалы
    Кластеризация: более глубокий взгляд
    Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
    Как написать эффективное резюме
    Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
    Частный метод Python: улучшение инкапсуляции и безопасности
    Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
    Как я автоматизирую тестирование с помощью Jest
    Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
    Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
    Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
    Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
    В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
    Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
    В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..