как избежать дублирования при соединении двух таблиц

ProgramBox

как избежать дублирования при соединении двух таблиц

Post author:admin
Запись опубликована:9 мая, 2023
Post category:Вопросы по программированию

#sql #join #db2 #row-number

#sql #Присоединиться #db2 #номер строки

Вопрос:

 Student Table

 SID    Name
 1      A
 2      B
 3      C

 Marks Table

 id mark    subject 
 1  50  physics
 2  40  biology
 1  50  chemistry
 3  30  mathematics



SELECT distinct(std.id),std.name,m.mark, row_number() over() as rownum FROM 

student std JOIN marks m ON std.id=m.id AND m.mark=50

Этот результат повторяется 2 раза даже после использования disticnt . Мой ожидаемый результат будет иметь только один A. если я удалю row_number () вместо() в качестве rownum, он будет работать нормально. Почему это происходит? как решить. Я использую DB2!!

1. Какой смысл объединять Student и Marks, если вы не хотите получать оценки A как по физике, так и по химии?

2. я просто хочу, чтобы студент, получивший 50 баллов по крайней мере по одному предмету

Ответ №1:

В таблице marks есть две строки с id = 1 и mark = 50.. Таким образом, вы получите две строки в выходных данных для каждой строки в таблице student… Если вам нужна только одна, вам нужно создать группу по

  SELECT std.id, std.name, m.mark, row_number() 
  over() as rownum 
 FROM student std 
    JOIN marks m 
       ON m.id=std.id AND m.mark=50 
 Group By std.id, std.name, m.mark

1. ВСЕГДА помещайте предикаты объединения в соединение. Предикаты предложения Where не вычисляются до тех пор, пока не будет сгенерирован весь результирующий набор, поэтому ненужные строки переносятся на протяжении всей обработки, и в некоторых сценариях внешнего соединения помещение предикатов в предложение where приведет к получению неверных результатов. Наконец, помещение предикатов join в join помещает их рядом с таблицами, о которых они говорят, а не все вместе в конце, что добавляет ясности запросу.

2. КРОМЕ того, иногда требуется присоединиться к одной и той же таблице более одного раза, используя РАЗНЫЕ предикаты для каждого объединения. Как вы собираетесь это сделать в предложении Where?

3. @CharlesBretana Каждая крупная реляционная база данных достаточно умна, чтобы ставить условия фильтрации перед JOIN условиями при фактической оценке запроса; они довольно хороши в сокращении количества операций, которые они должны выполнять (иначе говоря, оптимизация). Вообще говоря, добавляете ли вы что-то подобное m.mark = 50 в WHERE или ON предложение, даже не влияет на план запроса. Таким образом, эстетическое улучшение является единственным реальным соображением (по крайней мере, для внутренних объединений), и включение таких условий в WHERE предложение намного более интуитивно.

4. НЕТ, это не так, потому что результаты помещения предиката в каждое из этих двух мест отличаются (или могут отличаться), и оптимизатор не может быть сконструирован так, чтобы читать мысли автора запроса. В одном случае предикат вычисляется до объединения, а в другом случае он вычисляется после объединения.

5. @CharlesBretana Результаты могут отличаться только для не INNER объединений, к которым запрос в вашем ответе не относится. Попробуйте это в обоих местах вашей любимой базы данных и проверьте, отличаются ли планы запросов. Их не будет. Подобная перестановка шагов является основной частью оптимизации. И фактически, для не- INNER объединений включение фильтра в ON предложение замедляет выполнение запроса, поскольку он должен включать больше строк из нефильтрованной таблицы. Да, это другой результирующий набор, но он также редко требуется. Гораздо чаще возникает желание отфильтровать все, что не соответствует фильтру.

Ответ №2:

Теперь, когда вы прояснили свой вопрос как:

Я хочу найти всех студентов с отметкой 50 хотя бы по одному предмету. Я бы использовал запрос:

 SELECT student.id, '50'
FROM student 
WHERE EXISTS (SELECT 1 FROM marks WHERE marks.id = student.id AND marks.mark = 50)

Это также дает вам гибкость при изменении критериев, например, по крайней мере, одной отметки 50 или меньше.

1. 1 СУЩЕСТВУЕТ (полусоединение) — это то, что вы действительно хотите сделать. Вам не нужно полное объединение, потому что вам не нужны все эти дополнительные данные. Этот запрос более эффективен по сравнению с выполнением дополнительной работы по полному объединению, а затем выполнением еще большей дополнительной работы по урезанию набора данных только до того, что вы хотели в первую очередь.

2. 1 за использование WHERE EXISTS, но вы забыли о его столбце rownum.

3. @orbfish Я действительно не понимаю, с чем пытается справиться OP row_number() over () . @zod Возможно, вы можете уточнить?

4. row_number() over () используется для разбивки на страницы, точно так же, как LIMIT в mysql

5. Чтение row_number() over () этого просто предоставляет последовательный номер строки для каждой строки результирующего набора, так что это, безусловно, могло быть включено. Откуда и ГДЕ важные аспекты решения, но спасибо, что подтолкнули меня к изучению чего-то нового.

Ответ №3:

Аналогично ответу Чарльза, но вы всегда хотите поместить предикат (mark = 50) в предложение WHERE, поэтому вы фильтруете перед объединением. Если это просто домашнее задание, это может не иметь значения, но вы захотите запомнить это, если когда-нибудь столкнетесь с какими-либо реальными данными.

 SELECT std.sid,
       std.name,
       m.mark,
       row_number() over() AS rownum 
 FROM student std 
      JOIN marks m 
        ON std.sid=m.id
WHERE m.mark=50
GROUP BY std.sid, std.name, m.mark

1. Я считаю, что большинство оптимизаторов запросов обычно могут определить наилучшее время для применения фильтров независимо от размещения предложения. Это особенность DB2?

2. @M_M — No — DB2 (по крайней мере, в iSeries), похоже, генерирует одинаковые планы объяснения и, похоже, рассматривает два запроса как эквивалентные (используя одни и те же пути доступа); по крайней мере, для таких простых примеров, как этот. @All — Помещение условия в WHERE предложение в отличие от JOIN предложения не всегда будет возвращать одинаковые результаты (это в основном актуально при использовании LEFT и / или EXCEPTION объединений), поэтому условные выражения следует размещать там, где они будут генерировать правильные результаты, а не из соображений производительности (оптимизатор DB2 тоже довольно хорош).

3. @M_M — Я знаком с этим в Oracle. Иногда это выясняет, но иногда выясняется, что вы действительно имеете в виду то, что говорите с SQL, и заканчивается тем, что занимает целую вечность.