Запрос MySQL. Примеры SELECT: 31-40

Печать

Примеры могут быть использованы для практикума по запросам MySQL.

Examples can be used for a workshop on MySQL queries.

Запрос Q031. Пример группирования записей по двум полям. По каждому поставщику вычисляются сумма и количество поступивщих от него товаров:

Query Q031. An example of grouping records by two fields. For each supplier, the amount and quantity of goods received from him are calculated:

SELECT supplier_id, product_id, SUM(amount) AS amount_sum, 
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id;

Запрос Q032. Пример группирования записей по двум полям.Для каждого поставщика вычисляются сумма и количество его продуктов, проданных нами:

Query Q032. Example of grouping records into two fields. For each vendor, the amount and quantity of its products sold by us are calculated:

SELECT supplier_id, product_id, SUM(amount) AS amount_sum, 
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id;

Запрос Q033. В этом примере два вышеприведенных запроса (Q031 и Q032) использованы как подзапросы. Результаты этих запросов методом LEFT JOIN объединены в один отчет. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже поступил, но еще не реализован, то клетка outcome_sum для этой записи будет пустой. Также необходимо отметить, что данный запрос служит только примером использования относительно сложных запросов в качестве подзапроса. Производительность данного запроса SQL при большом объеме данных сомнительна:

Query Q033. In this example, the two above queries (Q031 and Q032) are used as subqueries. The results of these queries by the LEFT JOIN method are combined into one report. The following query displays a report on the number and amount of incoming and sold products for each vendor. You should pay attention to the fact that if some product has already arrived, but has not yet been implemented, then the field outcome_sum for this record will be empty. It should also be noted that this query serves only as an example of using relatively complex queries as a subquery. The performance of this SQL query for large amounts of data is questionable:

SELECT *
FROM
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS a
LEFT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b
ON a.product_id=b.id GROUP BY supplier_id, product_id) AS b
ON (a.product_id=b.product_id) AND (a.supplier_id=b.supplier_id);

Запрос Q034. В этом примере два вышеприведенных запроса (Q031 и Q032) использованы как подзапросы. Результаты этих запросов методом RIGTH JOIN объединены в один отчет. Следующий запрос выводит отчет о сумме платежей каждого клиента по использованным им платежным системам и сумме сделанных им инвестиций. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже реализован, но еще не поступил, то клетка income_sum для этой записи будет пустой. Наличие таких пустых клеток является показателем ошибки в учете продаж, так как до продажы сначала необходимо, чтобы соответствующий товар поступил:

Query Q034. In this example, the two above queries (Q031 and Q032) are used as subqueries. The results of these queries by the RIGTH JOIN method are combined into one report. The following query displays a report on the amount of payments each client made on the payment systems used by him and the amount of his investment. The following query displays a report on the number and amount of incoming and sold products for each vendor. You should pay attention to the fact that if some product is already implemented, but has not yet arrived, the income_sum cell for this record will be empty. The presence of such empty cells is an indicator of a mistake in the accounting of sales, since before the sale, it is first necessary that the corresponding goods arrive:

SELECT *
FROM
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS income_sum
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS a
RIGHT JOIN
(SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
SUM(amount*price) AS outcome_sum
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
GROUP BY supplier_id, product_id) AS b
ON (a.supplier_id=b.supplier_id) AND (a.product_id=b.product_id);

Запрос Q035. В этом примере выводится отчет о сумме доходов и расходов по продуктам. Для этого создается список продуктов по таблицам m_income и m_outcome, затем для каждого продукта из этого списка вычисляется сумма его приходов по таблице m_income и сумма его расходов по таблице m_outcome:

Query Q035. This example displays a report on the amount of revenue and expenses for products. To do this, create a list of products on the m_income and m_outcome tables, then for each product from this list, calculate the amount of its revenues in the m_income table and the amount of its expenditures in the m_outcome table:

SELECT product_id, SUM(in_amount) AS income_amount, 
SUM(out_amount) AS outcome_amount
FROM
(SELECT product_id, amount AS in_amount, 0 AS out_amount
FROM m_income
UNION ALL
SELECT product_id, 0 AS in_amount, amount AS out_amount
FROM m_outcome) AS t
GROUP BY product_id;

Запрос Q036. Функция EXISTS возвращает значение TRUE, если переданное ей множество содержит элементы. Функция EXISTS возвращает значение FALSE, если переданное ей множество пустое, то есть не содержит элементов. Следующий запрос выводит коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:

Query Q036. The EXISTS function returns TRUE if the set it contains contains elements. The EXISTS function returns FALSE if the set passed to it is empty, that is, contains no elements. The following query displays the product codes that are contained in both the m_income table and the m_outcome table:

SELECT DISTINCT product_id
FROM m_income AS a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);

Запрос Q037. Выводятся коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:

Query Q037. The product codes are displayed that are contained in both the m_income table and the m_outcome table:

SELECT DISTINCT product_id
FROM m_income AS a
WHERE product_id IN (SELECT product_id FROM m_outcome)

Запрос Q038. Выводятся коды товаров, которые содержатся как в таблице m_income, но не содержатся в таблице m_outcome:

Query Q038. The product codes that are contained in the m_income table are displayed, but not contained in the m_outcome table:

SELECT DISTINCT product_id
FROM m_income AS a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);

Запрос Q039. Следующий пример выводит список товаров, сумма продаж которых максимальная. Алгоритм таков. Для каждого товара вычисляется сумма его продаж. Затем, определяется максимум этих сумм. Затем, для каждого товара снова вычисляется сумма его продаж, и выводятся код и сумма продаж товаров, сумма продаж которых равна максимальной:

Query Q039. The following example displays a list of products with a maximum sales amount. The algorithm is as follows. For each product, the amount of its sales is calculated. Then, the maximum of these sums is determined. Then, for each product, the amount of its sales is calculated again, and the code and the amount of sales of the goods whose sales amount is equal to the maximum are displayed:

SELECT product_id, SUM(amount*price) AS amount_sum
FROM m_outcome
GROUP BY product_id
HAVING SUM(amount*price) = (SELECT MAX(s_amount)
FROM (SELECT SUM(amount*price) AS s_amount FROM m_outcome GROUP BY product_id)t);

Запрос Q040. Зарезервированное слово IF (условный оператор) используется в MySQL для оценки логического выражения и выполнения того или иного действия в зависимости от результата (TRUE или FALSE). В следующем примере поставка товара считается «малой», если количество меньше 500. В противном случае, то есть количество поступления больше или равно 500, поставка считается «большой»:

Query Q040. The reserved IF word (conditional statement) is used in MySQL to evaluate a logical expression and perform an action depending on the result (TRUE or FALSE). In the following example, the delivery of the goods is considered to be "small" («малой») if the quantity is less than 500. Otherwise, that is, the amount of receipt is greater than or equal to 500, the delivery is considered "large" («большой»):

SELECT dt, product_id, amount, 
IF(amount<500,"малая","большая") AS mark
FROM m_income;

Комментарии   

 
+1 #2 Программист 24.12.2013 09:03
Цитирую Алексей:
В Запросе Q039 перед последней закрывающейся скобкой указан "t". Что означает и зачем используется? Без него не работает.

t - это алиас. Подзапросу, который в круглых скобках, присвоено имя t.
Цитировать
 
 
0 #1 Алексей 23.12.2013 13:32
В Запросе Q039 перед последней закрывающейся скобкой указан "t". Что означает и зачем используется? Без него не работает.
Цитировать
 

Добавить комментарий


Защитный код
Обновить

Произведение «OFTOB.COM» публикуется на условиях лицензии Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Непортированная.