Multiple Relationships Between two Tables
There are situations beside the self join in which you need to join the same table more than once. One is when you have multiple relationships between two different tables. This is where you join the same table twice but usually to some other table and not necessarily to itself.
| customer_id | firstname | lastname | birthdate | residence_city_id | notice_city_id |
|---|---|---|---|---|---|
| 1 | John | Mayer | 1983-05-12 | 1 | 6 |
| 2 | Mary | Mayer | 1990-07-30 | 1 | 6 |
| 3 | Lisa | Ross | 1989-04-15 | 6 | 7 |
| 4 | Anna | Timothy | 1988-12-26 | 4 | 4 |
| 5 | Tim | Ross | 1957-08-15 | 6 | 7 |
| 6 | Steve | Donell | 1967-07-09 | 4 | 4 |
| 7 | Donna | Trapp | 1978-06-23 | 2 | 2 |
We also have which has the city ID () and the name of the city (), as seen earlier and shown below as a reminder:
| city_id | name |
|---|---|
| 1 | London |
| 2 | Manchester |
| 3 | Liverpool |
| 4 | Leeds |
| 5 | Bristol |
| 6 | Oxford |
| 7 | Reading |
| 8 | Brighton |
| 9 | Sheffield |
| 10 | York |
Now, if you want to display the names of the cities, you will have to join the table twice:
select cust.customer_id,
cust.firstname,
cust.lastname,
cust.birthdate,
cust.residence_city_id,
cust.notice_city_id,
residence_city.name as residence_city_name,
notice_city.name as notice_city_name
from customer cust
join city residence_city
on cust.residence_city_id=residence_city.city_id
join city notice_city
on cust.notice_city_id=notice_city.city_id;
Let’s break down what is happening in this code. First, we join and with as the key. We get by matching it to in the table. A second join is performed between and to get . The key used here is which also matches to in the table.
We use table aliases for , for the first copy of to get the residence city name, and notice_city for the second copy of city to get the notice city name. We use the aliases to define the columns in the resulting table. Aliases are also used during the join to define the key columns. Again, aliases are required in order to distinguish the two copies of .
When you run this code, you get the following result:
| customer_id | firstname | lastname | birthdate | residence_city_id | notice_city_id | residence_city_name | notice_city_name |
|---|---|---|---|---|---|---|---|
| 1 | John | Mayer | 1983‑05‑12 | 1 | 6 | London | Oxford |
| 2 | Mary | Mayer | 1990-07-30 | 1 | 6 | London | Oxford |
| 3 | Lisa | Ross | 1989-04-15 | 6 | 7 | Oxford | Reading |
| 4 | Anna | Timothy | 1988-12-26 | 4 | 4 | Leeds | Leeds |
| 5 | Tim | Ross | 1957-08-15 | 6 | 7 | Oxford | Reading |
| 6 | Steve | Donell | 1967-07-09 | 4 | 4 | Leeds | Leeds |
| 7 | Donna | Trapp | 1978-06-23 | 2 | 2 | Manchester | Manchester |
We now have two additional columns with the corresponding city names.
We use simple (i.e., inner) joins here, but you can use any type of join as needed. If you are new to SQL joins and want to read more about their different types, I recommend the articles “How to learn joins” and “How to practice joins” which cover these topics. If you prefer to learn by watching videos. I highly recommend the episode that discusses joins.
MySQL select the row with maximum value in a column : MAX() function
This section will help us learn how to get the maximum value for a column and get the record details corresponding to it. Let us start by creating a table sales_details followed by inserting some records to it. ( Read MySQL INSERT INTO for more information on inserting rows to tables).
# Create the table sales_details
CREATE TABLE sales_details (
sale_person_id INT,
sale_person_name VARCHAR(255),
commission_percentage INT,
no_products_sold INT,
total_commission INT
);
# Insert the records
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission)
VALUES(1,"Aditi",10,200,2000);
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission)
VALUES(2,"Furan",5,300,1500);
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission)
VALUES(3,"Veronica",10,250,2500);
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission)
VALUES(4,"Atharv",25,150,3750);
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission)
VALUES(5,"Erick",20,350,7000);
INSERT INTO sales_details (sale_person_id,sale_person_name,commission_percentage,no_products_sold,total_commission)
VALUES(6,"Rasmus",25,355,8750);
Run a select query on sales_details to view the records (see figure1.1).
SELECT * FROM sales_details;
Output:-
Advertisements
figure 1.1
Now: Get the name, commission percentage, and the number of products sold for the salesperson who made the maximum sales.
Below is the code to achieve results to the above ask.
SELECT
sale_person_name, commission_percentage, no_products_sold
FROM
sales_details
WHERE
no_products_sold = (SELECT
MAX(no_products_sold)
FROM
sales_details);
Output:-
figure 1.2
Approach : The approach is simple to get the maximum sales in a subquery using the MAX() function and get other details from sales_details table corresponding to the maximum sales.
So, here we got the sales person name – Rasmus who did the maximum sales that is 355.
In case there is more than one salesperson who made the maximum sales, we will get all records in the results retrieved by the same select query. Let’s assume that in the table sales_details there was more than one person who sold the maximum number of products 350 (see figure 1.3).
figure 1.3
Here, the output of the same select query that we ran above will be.
figure 1.4
In case we want only the first record to be displayed in the results, we can use the LIMIT clause to get only one result. See the below query and output(figure 1.5).
SELECT
sale_person_name, commission_percentage, no_products_sold
FROM
sales_details
WHERE
no_products_sold = (SELECT
MAX(no_products_sold)
FROM
sales_details)
LIMIT 1;
Output:-
figure 1.5
As we can see, we got only one result because we limited them to 1.
10 ответов
Лучший ответ
Каждая опция, которая включает манипуляции CAST, TRUNCATE или DATEPART в поле datetime, имеет одну и ту же проблему: запрос должен сканировать весь набор результатов (40k), чтобы найти отдельные даты. Производительность может незначительно отличаться между различными реализациями.
Что вам действительно нужно, так это иметь индекс, который может мгновенно дать ответ. У вас может быть либо постоянный вычисляемый столбец с индексом, который (требует изменения структуры таблицы), либо индексированное представление (требует, чтобы Enterprise Edition для QO учитывал индекс в готовом виде).
Постоянный вычисляемый столбец:
Индексированное представление:
Обновить
Чтобы полностью исключить сканирование, можно использовать индексированное представление GROUP BY, например:
Вместо этого запрос будет использовать это индексированное представление. Технически это все еще сканирование, но по уже «отдельному» индексу, поэтому сканируются только необходимые записи. Я считаю, что это хак, я бы не рекомендовал его для живого производственного кода.
AFAIK SQL Server не имеет возможности сканировать истинный индекс с пропуском повторов, т.е. ищите вершину, затем ищите больше вершины, затем последовательно ищите больше последней найденной.
7
Remus Rusanu
20 Авг 2009 в 18:20
Я использовал следующее:
Это удаляет время из даты, преобразовывая его в и усекая «временную» часть, которая является десятичной дробью .
Выглядит немного неуклюже, но хорошо работает с большим набором данных (~ 100 000 строк), который я использую постоянно в течение дня.
10
fragilewindows
9 Дек 2016 в 16:04
Это работает для меня:
7
bluish
1 Авг 2012 в 14:14
Самый простой способ — добавить вычисляемый столбец только для части даты и выбрать его. Вы можете сделать это в представлении, если не хотите менять таблицу.
3
Joel Coehoorn
20 Авг 2009 в 16:35
Я не уверен, почему ваш существующий запрос занимает более 5 секунд для 40 000 строк.
Я просто попробовал выполнить следующий запрос к таблице со 100 000 строками, и он вернулся менее чем за 0,1 с.
(Обратите внимание, что этот запрос, вероятно, не сможет использовать какие-либо индексы в столбце даты, но он должен быть достаточно быстрым, если вы не выполняете его десятки раз в секунду.)
3
LukeH
20 Авг 2009 в 17:15
Обновление:
Решение, приведенное ниже, проверено на эффективность на таблице и требует только .
Обычный в индексированном вычисляемом столбце занял .
См. Эту запись в моем блоге для получения подробной информации о производительности:
SQL Server: эффективно DISTINCT по срокам
К сожалению, оптимизатор не может выполнять ни Oracle, ни .
Всегда занимает много времени.
Вы можете составить список возможных дат, используя рекурсивный , и соединить его со своей таблицей:
Это будет более эффективно, чем
2
Quassnoi
20 Авг 2009 в 19:11
Я использовал это
1
JendaZ.
23 Фев 2011 в 18:10
Просто преобразуйте дату:
JeffO
20 Авг 2009 в 17:20
Если вы хотите избежать поэтапного извлечения или переформатирования даты, что, по-видимому, является основной причиной задержки (путем принудительного полного сканирования таблицы), у вас нет альтернативы, кроме как сохранить дату только часть даты и времени, что, к сожалению, будет требуют изменения структуры базы данных.
Если вы используете SQL Server 2005 или более позднюю версию, то вам подойдет постоянное вычисляемое поле.
Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change. By marking a computed column as PERSISTED, you can create an index on a computed column that is deterministic but not precise.
Cruachan
23 Авг 2009 в 12:18
Каков ваш предикат для этого другого отфильтрованного столбца? Пробовали ли вы улучшить индекс этого другого отфильтрованного столбца, за которым следует поле datetime?
Я в основном предполагаю здесь, но 5 секунд, чтобы отфильтровать набор, возможно, из 100000 строк до 40000, а затем выполнить сортировку (что, по-видимому, и происходит), мне не кажется неразумным. Почему ты говоришь, что это слишком медленно? Потому что это не соответствует ожиданиям?
Erwin SmoutErwin Smout
20 Авг 2009 в 16:46
Joining the Same Table Twice
In this article, we discussed when you need to join the same table twice in SQL and saw some common business use cases. We explained how to do so and what the SQL syntax looks like. Self joins with hierarchical data and multiple relationships between two tables are just two of the situations for which you need to join the same table twice. There are others; generally, they involve adding one or more columns to a result set from the same table in the same column.
If you want to learn more about joins, including self joins, I recommend our interactive course SQL Joins available on our LearnSQL.com platform. When it comes to SQL, it is important to practice; our course is designed for just that!
Оператор WHERE
Следующий ниже фрагмент кода содержит универсальную синтаксическую конструкцию для запроса с оператором :
Оператор является необязательной частью запроса . Для указания условий можно использовать операторы или . Оператор также может использоваться с запросом и , который мы обсудим в ближайшее время в последующей статье.
Равно и не равно
Оператор равенства () проверяет, равны ли значения двух полей. Если они совпадают, то условие становится истинным, и оператор извлекает значение для дальнейшей обработки. Если они не совпадают, то условие должно содержать оператор неравенства (). Оно будет извлекать данные на основе условия, которое не совпадает.
Например, следующий ниже запрос используется для получения всех записей с городом, соответствующим значению New York:
Больше и меньше
Оператор больше, чем () проверяет, больше ли значение левого поля, чем значение правого поля. Если да, то условие становится истинным. Оператор меньше, чем () проверяет, меньше ли значение левого поля, чем значение правого поля. Мы также можем использовать операторы / и оператор равенства вместе.
Например, следующий ниже запрос используется для получения всех записей с количеством попыток входа в систему больше, чем 2:
LIKE
Оператор предоставляет простой способ поиска записей в столбце с различными шаблонами. В запросе можно использовать подстановочные символы для построения различных шаблонов. В основном используется два вида подстановочных символов. Давайте рассмотрим каждый из них на примере.
(процент): используйте этот подстановочный символ для поиска ноль или более любых символов. Предположим, что мы хотим отыскать пользователей, чье имя начинается с «a». Тогда мы можем применить этот подстановочный символ, как показано в приведенном ниже запросе.
В случае если мы хотим найти пользователей, чье имя начинается с «a» и заканчивается на «s», то запрос с подстановочным символом % будет таким:
(подчеркивание): используйте этот подстановочный символ там, где нужно отыскать записи с шаблоном, где в том месте, где мы указали подчеркивание (), может иметься любой символ. Предположим, мы хотим отыскать пользователей, чье имя заканчивается на dmin, и мы не уверены в первом символе. Следовательно, приведенный ниже запрос будет искать результаты, где первый символ имени пользователя может быть любым, но он должен заканчиваться на dmin
Важно помнить, что он будет учитывать ровно один символ для одного подчеркивания. Следовательно, в этом случае пользователь с именем пользователя как «aadmin» не будет рассматриваться, потому что в запросе указан всего один подстановочный символ с подчеркиванием.
INNOT IN
Оператор используется для сравнения нескольких значений в операторе . Например, следующий ниже запрос используется для поиска всех пользователей, имеющих город new york или chicago:
Оператор работает наоборот, например чтобы найти всех пользователей, у которых нет ни города new york, ни города chicago, используется:
BETWEEN
Оператор может использоваться в том случае, когда мы хотим получить записи, которые входят в определенный диапазон. Этот диапазон может быть любым, таким как текст, даты или цифры. Предположим, мы хотим отыскать пользователей, дата создания записи о которых находится между 1 июля и 16 июля 2017 года. Тогда приведенный ниже запрос с предложением может нам помочь.
Подобным образом мы можем также задавать диапазон в числах для поиска пользователей, которые принадлежат к этому конкретному диапазону. Например, если мы хотим получить студентов, чьи оценки находятся в диапазоне от 70 до 80, то оператор может быть применен для определения диапазона.
4 – Update with Inline View
Works with: Oracle (not MySQL, SQL Server, PostgreSQL)
This method uses an inline view to ensure a single table is being updated.
The Select query inside the Update statement looks up the two tables and constructs the right data set to use. Then the Set clause will set the person account number to the account number value.
If we run this in Oracle, we may get this error:
ORA-01779: cannot modify a column which maps to a non key-preserved table
This will happen depending on the tables and query that you have. Creating primary keys on the table may still result in this error.
So, you could try this method, but it may not work for you.
There are other approaches for Oracle databases though.
Использование модели в обработчиках
Вернемся в файл и обновим обработчик , таким образом, чтобы он вызвал метод который выведет на страницу последние 10 заметок из базы данных .
А пока, просто закомментируем код, относящийся к рендерингу шаблона. Сделаем это следующим образом:
cmd/web/handlers.go
Go
package main
import (
«errors»
«fmt»
// «html/template»
«net/http»
«strconv»
«golangify.com/snippetbox/pkg/models»
)
func (app *application) home(w http.ResponseWriter, r *http.Request) {
if r.URL.Path != «/» {
app.notFound(w)
return
}
s, err := app.snippets.Latest()
if err != nil {
app.serverError(w, err)
return
}
for _, snippet := range s {
fmt.Fprintf(w, «%v\n», snippet)
}
// files := []string{
// «./ui/html/home.page.tmpl»,
// «./ui/html/base.layout.tmpl»,
// «./ui/html/footer.partial.tmpl»,
// }
// ts, err := template.ParseFiles(files…)
// if err != nil {
// app.serverError(w, err)
// return
// }
// err = ts.Execute(w, nil)
// if err != nil {
// app.serverError(w, err)
// }
}
…
|
1 |
packagemain import( «errors» «fmt» // «html/template» «net/http» «strconv» «golangify.com/snippetbox/pkg/models» ) func(app *application)home(whttp.ResponseWriter,r *http.Request){ ifr.URL.Path!=»/»{ app.notFound(w) return } s,err=app.snippets.Latest() iferr!=nil{ app.serverError(w,err) return } for_,snippet=ranges{ fmt.Fprintf(w,»%v\n»,snippet) } // files := []string{ // «./ui/html/home.page.tmpl», // «./ui/html/base.layout.tmpl», // «./ui/html/footer.partial.tmpl», // } // ts, err := template.ParseFiles(files…) // if err != nil { // app.serverError(w, err) // return // } // err = ts.Execute(w, nil) // if err != nil { // app.serverError(w, err) // } } … |
Запускаем наше веб-приложение из терминала:
Shell
go run ./cmd/web
| 1 | go run.cmdweb |
Переходим на главную страницу http://127.0.0.1:4000 вы должны увидеть ответ, похожий на следующий:
У нас появились только 2 записи, хотя в базе данных у нас как минимум 4 заметок. Ниже вы можете увидеть содержимое таблицы :
MySQL
mysql> SELECT id, title, expires FROM snippets;
+—-+———————————————+———————+
| id | title | expires |
+—-+———————————————+———————+
| 1 | Не имей сто рублей | 2022-01-27 13:09:34 |
| 2 | Лучше один раз увидеть | 2022-01-27 13:09:40 |
| 3 | Не откладывай на завтра | 2021-02-03 13:09:44 |
| 4 | История про улитку | 2021-04-28 14:39:12 |
+—-+———————————————+———————+
|
1 |
mysql>SELECTid,title,expiresFROMsnippets; +—-+———————————————+———————+ |id|title|expires| +—-+———————————————+———————+ |1|Неимейсторублей|2022-01-2713:09:34| |2|Лучшеодинразувидеть|2022-01-2713:09:40| |3|Неоткладывайназавтра|2021-02-0313:09:44| |4|Историяпроулитку|2021-04-2814:39:12| +—-+———————————————+———————+ |
Проверяем данные из столбика и понимаем, что из за нашего SQL запроса, а именно который понимается как «все записи срок которых еще не истёк». SQL функция возвращает текущую дату с точности до секунды.
Вот и получается, что записи с ID 3 и 4 имеют старый срок годности и они нам не видны на главной странице сайта на golang!
Оптимизация запросов SELECT
Запросы используются для получения данных на динамических вебстраницах, поэтому настройка этих операторов обеспечивает хорошую производительность, что очень важно. Ниже приведены некоторые рекомендации по оптимизации запросов
- Убедитесь, что в таблицах есть индексы. Индексы всегда помогают ускорить фильтрацию и получение результатов. Индексы можно задавать в операторе запроса .
- Индексы также минимизируют количество полных сканирований в больших таблицах.
- Настройка буферного пула InnoDB, кеша ключей MylSAM и кеша запросов MySQL помогает кешировать результаты, которые приведут к более быстрым извлечениям повторяющихся результатов. Размер кеш-памяти можно настроить так, чтобы она обеспечивала более быстрый доступ, предоставляя результаты только из кеша.
- Отрегулируйте размер и свойства используемых MySQL областей памяти, чтобы кешировать буферный пул InnoDB, кеш ключей MylSAM и кеш запросов MySQL. Это помогает выполнять повторные запросы быстрее.
- Мы должны использовать оператор вместо , если мы не используем предложение или другие агрегатные функции, такие как , , , и т. д.
- Используйте инструкцию для анализа запроса с операторами , и индексами.
Теперь давайте рассмотрим, в чем заключается полезность инструкции для оптимизации производительности запросов:
Приведенный ниже результат является выводом выполненного вопроса:
В приведенных выше результатах показывает, какие индексы применяются к этому запросу, а говорит о том, какой из них был фактически использован. В нашем примере каждое соединение использует индексный ключ; путем создания индекса мы должны попытаться избежать значения ключа. Поле сообщает нам, сколько строк сканируется в запросе, выполняемом для идентификации, и это число должно быть уменьшено для лучшей производительности запроса, поскольку это минимизирует передачу данных.
Полуобъединение («SEMI» JOIN)
В реляционной алгебре существует операция полуобъединения (), которая, к сожалению, не имеет синтаксического представления в SQL. Если бы синтаксис для данной операции существовал, вероятно, он имел бы следующий вид: LEFT SEMI JOIN и RIGHT SEMI JOIN, аналогичный реализованному в Cloudera Impala.
Что же представляет собой операция «SEMI» JOIN? Рассмотрим следующий воображаемый запрос:
SELECT * FROM actor LEFT SEMI JOIN film_actor USING (actor_id)
В результате данного запроса мы хотим получить всех актеров, снимавшихся в фильмах, но при этом нам не нужны сами фильмы. Более того, мы не хотим, чтобы данный актер появлялся в результате несколько раз (по одному разу на каждый фильм, в котором он играл), мы хотим получить каждого актера только один (или ноль) раз.
«Semi» – это латинское слово, обозначающее «половину». То есть данная операция реализует «половину объединения», в данном случае, левую половину.
В SQL мы можем использовать два варианта альтернативного синтаксиса, чтобы реализовать операцию «SEMI» JOIN.
Альтернативный синтаксис: EXISTS
Представленный ниже вариант является более мощным и чуть более многословным:
SELECT *
FROM actor a
WHERE EXISTS (
SELECT * FROM film_actor fa
WHERE a.actor_id = fa.actor_id
Мы извлекаем всех актеров, для которых существует (exists) фильм, то есть актеров, сыгравших хотя бы в одном в фильме. При рассмотрении данного синтаксиса (где код, реализующий «SEMI» JOIN, помещен в предложении WHERE) сразу становится очевидно, что мы можем получить в результате каждого актера максимум один раз.
Следует отметить, что в данном синтаксисе отсутствует ключевое слово JOIN. Несмотря на это, большинство СУБД способны распознать, что данный запрос выполняет именно «SEMI» JOIN, а не просто обычным образом использует предикат EXISTS(). Для примера рассмотрим план выполнения приведенного выше запроса в Oracle:
Обратите внимание, Oracle называет эту операцию «HASH JOIN (SEMI)» («SEMI» присутствует в названии). Аналогично в PostgreSQL:
Аналогично в PostgreSQL:
Аналогично в SQL Server:
Применение «SEMI» JOIN вместо INNER JOIN для решения поставленной задачи не только более корректно, но также обеспечивает преимущество в отношении производительности. Это объясняется тем, что после того, как найдено первое совпадение, СУБД не будет искать другие совпадения!
Альтернативный синтаксис: IN
Варианты синтаксиса на основе IN и EXISTS являются эквивалентными реализациями операции «SEMI» JOIN. Большинство СУБД (за исключением MySQL) сформируют идентичный план выполнения, как для рассмотренного выше запроса на основе EXISTS, так и для представленного ниже запроса на основе IN:
SELECT * FROM actor WHERE actor_id IN ( SELECT actor_id FROM film_actor )
Если ваша СУБД поддерживает оба описанных выше синтаксиса, вы может выбрать любой из них, руководствуясь стилистическими соображениями.
