Как заставить GROUP BY и подзапрос в Mysql выдавать нужный вариант таблицы?

Question

[feniksdv]

Всем привет. Суть вопроса я хочу получить таблицу следующего вида:
___________________________________________________________________________
| NCValue | NCValue_sale | value_buy | value_sale | id_name_banks_buy |
------------|-----------------|--------------|-------------|--------------------------|
Делаем запрос в БД и получаем value_buy , дальше это значению value_buy нужно найти в другой таблицы и получить его id_name_banks и вставить в колонку id_name_banks_buy .
Структура таблиц.
Что-то не хочет загружать картинки, по этому прикрепляю с Яндекс диска.

mysql> show create table parser_all_exchange_rates \G;
*************************** 1. row ***************************
       Table: parser_all_exchange_rates
Create Table: CREATE TABLE `parser_all_exchange_rates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `id_name_banks` int NOT NULL,
  `id_name_currency` int NOT NULL,
  `value` decimal(6,3) NOT NULL DEFAULT '0.000',
  `value_sale` decimal(6,3) NOT NULL DEFAULT '0.000',
  `date` date NOT NULL,
  `time` time NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_parser_all_exchange_rate_parser_name_banks` (`id_name_banks`) USING BTREE,
  KEY `FK_parser_all_exchange_rate_parser_name_currency` (`id_name_currency`) USING BTREE,
  CONSTRAINT `FK_parser_all_exchange_rate_parser_name_currency` FOREIGN KEY (`id_name_currency`) REFERENCES `parser_name_currencies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=770 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> show create table parser_name_banks \G;
*************************** 1. row ***************************
       Table: parser_name_banks
Create Table: CREATE TABLE `parser_name_banks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name_bank_ru` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name_bank_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_bank_ru` (`name_bank_ru`)
) ENGINE=InnoDB AUTO_INCREMENT=235 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> show create table parser_name_currencies \G;
*************************** 1. row ***************************
       Table: parser_name_currencies
Create Table: CREATE TABLE `parser_name_currencies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name_currency_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name_currency_ru` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `unit` smallint NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

Запросы

SELECT
   e.name_currency_en AS NCValue,
   e.name_currency_en AS NCValue_sale,
   MAX(c.value) value, 
   MIN(c.value_sale) value_sale
FROM 
   parser_all_exchange_rates c
INNER JOIN 
   parser_name_currencies e ON e.id = c.id_name_currency
   WHERE c.date = CURRENT_DATE() 
   AND c.id_name_banks != 233 
   AND value_sale != 0
GROUP BY NCValue, NCValue_sale;

Это первый запрос он выдает то что нужно, сортировка по MAX и MIN работает. Теперь я прикручиваю к запросу id_name_banks_buy.

SELECT
   e.name_currency_en AS NCValue,
   e.name_currency_en AS NCValue_sale,
   MAX(c.value) value, 
   MIN(c.value_sale) value_sale,
   c.id_name_banks
FROM 
   parser_all_exchange_rates c
INNER JOIN 
   parser_name_currencies e ON e.id = c.id_name_currency
   WHERE c.date = CURRENT_DATE() 
   AND c.id_name_banks != 233 
   AND value_sale != 0 
GROUP BY NCValue, NCValue_sale,c.id_name_banks;

Он выдает мне все банки, т.е. сортировка уже не работает.
Логика следующая:
Берем значение value_buy ищем его в таблице parser_all_exchange_rates и получаем id_name_banks и размещаем полученный результат в колонке id_name_banks_buy. Все вот ту вот у меня затык.
Вот последний вариант моих успехов, где что я делаю неверно?

SELECT
   e.name_currency_en AS NCValue,
   e.name_currency_en AS NCValue_sale,
   MAX(c.value) AS value_buy, 
   MIN(c.value_sale) AS value_sale, 
   c.id_name_banks AS id_name_banks_buy
FROM 
   parser_all_exchange_rates c
INNER JOIN 
   parser_name_currencies e ON e.id = c.id_name_currency
WHERE 
   c.date = CURRENT_DATE() 
   AND c.id_name_banks != 233 
   AND value_sale != 0 
   AND c.id_name_banks = 
   (
      SELECT 
         c.id_name_banks
      FROM
         parser_all_exchange_rates c
      WHERE 
         c.value_sale ORDER BY value_sale ASC LIMIT 1
   )
GROUP BY NCValue, NCValue_sale, c.id_name_banks;

Comments

[Akina]

Не надо картинок, лечить по фотографиям - это шарлатанство. Создайте fiddle? например, на https://dbfiddle.uk/?rdbms=mysql_8.0

PS. Что, по-Вашему, делает/проверяет WHERE c.value_sale в подзапросе?

[feniksdv]

Akina,

WHERE 
c.value_sale ORDER BY value_sale ASC LIMIT 1

Берем колонку c.value_sale и сортируем её по возрастанию. Вы намекаете на Limit 1 ?

[Akina]

feniksdv, нет, я спросил, что делает именно WHERE. По сортировке и лимитированию вопросов нет.

[feniksdv]

Akina, ничего оно не делает я хотел там сделать так:

но выдает ошибку, т.к. MAX нельзя использовать в where. Я хоть направильном пути?

[Akina]

В изначальном варианте Ваше условие проверяло, что c.value_sale содержит значение, отличное от NULL и нуля.

В показанном же на скриншоте варианте - логика есть. Но Вы не учитываете, что значение MAX() появляется после выполнения GROUP BY, тогда как WHERE выполняется до группировки. Возможное решение - перенос условия из WHERE в HAVING.

Но последний запрос отличается от того, что показано в вопросе, причём в худшую сторону. Например, Вы пишете c.date = CURRENT_DATE() - 1. Если Вы думаете, что это минус день - огорчу. В таком варианте контекст выполнения - числовой, и если CURRENT_DATE() - первое число месяца, запрос выдаст ошибку. Следует использовать c.date = CURRENT_DATE() - INTERVAL 1 DAY.

В общем, следите за синтаксисом. Сервер не станет выполнять то, что Вы хотите, он дурак и делает строго то, что Вы скажете. А если скажете неправильно - то о правильном результате даже не мечтайте.

Answer 1

[feniksdv]

SELECT 
  val.*,
  val_sale.sale,
  ANY_VALUE(bi.id_name_banks) buy_bank,
  ANY_VALUE(si.id_name_banks) sale_bank,
  val_cbrf.value
FROM

(
   SELECT MAX(value) 
      buy, 
      id_name_currency 
   FROM 
      parser_all_exchange_rates
   WHERE 
      date = CURRENT_DATE() AND
      id_name_banks != 233
   GROUP BY 
      id_name_currency
) val,

(
   SELECT 
      MIN(value_sale) sale, 
      id_name_currency
   FROM 
      parser_all_exchange_rates
   WHERE 
      date = CURRENT_DATE() AND
      value_sale > 0 AND
      id_name_banks != 233
   GROUP BY 
      id_name_currency
) val_sale,

(
   SELECT 
      r.value,
      r.id_name_currency
   FROM 
      parser_all_exchange_rates r
   WHERE
      r.id_name_banks = 233 AND 
      r.date = CURRENT_DATE()
) val_cbrf,
   
   parser_all_exchange_rates bi,
   parser_all_exchange_rates si
WHERE 
  bi.id_name_currency = val.id_name_currency AND 
  val.buy = bi.value AND 
  val_sale.id_name_currency = val.id_name_currency AND
  bi.date = CURRENT_DATE() AND
  si.id_name_currency = val.id_name_currency AND
  val_sale.sale = si.value_sale AND
  si.date = CURRENT_DATE() AND 
  val_cbrf.id_name_currency = val.id_name_currency
GROUP BY 
   val.id_name_currency, 
   val.buy, 
   val_sale.sale,
   val_cbrf.value